Re: [GENERAL] PG periodic Error on W2K

[Magnus Hagander]

On Thu, Mar 01, 2007 at 09:44:19AM +0900, Paul Lambert wrote:
> I'm running PG 8.2.3 on We doze 2000 Server. (Should I apologise for 
> that up front to appease the masses?)
> 
> I am periodically getting errors pop up on the server console of the 
> following nature:
> 
> The File or directory D:\PostgresQL\Data\global\pgstat.stat is corrupt 
> and unreadable. Please run the Chkdsk utility.
> 
> and
> 
> The file or directory D: is corrupt and unreadable. Please run the 
> Chkdsk utility.
> 
> Now, per the errors suggestion I have run the chkdsk utility with a /X 
> /F switch to do a complete check on reboot before mounting the volume.
> 
> This showed no errors.
> 
> I can also open the mentioned file - pgstat.stat - using notepad or any 
> other program without mention of corruption and the data within the file 
> looks to be uniform suggesting it is fine.
> 
> Strangely enough, this error was being presented on the last server I 
> had it running on, and was in fact one of the reasons I moved it - I 
> assumed the error was due to dodgy disks but this seems a bit much of a 
> coincidence.
> 
> I know these errors are not coming directly from Postgres, but does 
> anyone else have problems (or has had previously) of a similar nature or 
> any suggestions on where it may be?

They are, as you say, generated by Windows, and not PostgreSQL. They're
a clear indication of either hardware problem, driver problem or windows
bug (which we all know don't exist, so it must be one of the first).

They can *not* be caused by a bug in PostgreSQL - no more than a kernel
oops in linux is the fault of PostgreSQL. Now, we do push the filesystem
and disk layer in an unusual way with the pgstats writes, gievn that we
rewrite the same file over and over and over and over again at very
short intervals. But nothing says we're not allowed to do that :-)

The reason you acn open it with notepad is most likely that it's a
different file - the file is deleted and recreated at a rate of at least
twice per second, when there is activity happening in the database. The
error is more a "filesystem is broken" message than "this file is
broken".


> As a side-note, this server is RAID controlled, the D drive has 
> 
>3 disks in the array - I would therefore have assumed that if there 
> was a problem with one of the disks then the server would carry on using 
> the other disks.

You would hope so. But the problem could be in the actual RAID
controller. There are a lot of el-cheapo RAID-boards out there that
really do more harm than good. Then there are of course a lot of very
nice controllers as well :-) Which one do you have?

Also, it could very well be a driver problem - have you verified that
you're on the latest version?


> I can find no performance degradation in Postgres, the service and 
> connections et al. keep on operating as though there was nothing wrong, 
> but the errors continue to pop up sporadically on the console.
> 
> Thoughts? Ideas? Suggestions? Should I bugger off?

Thoughts: scary (for you).
Ideas: see above.
Suggestions: get it fixed. Next time it might be your datafile or WAL.
Bugger off: Nah, find out what it was and let us know instead :-)


On Wed, Feb 28, 2007 at 04:52:15PM -0800, Joshua D. Drake wrote:
> Try turning off stats. However you will need to run vacuum using some
> other method.

While this will get rid of the message (most likely) you're only curing
the symptoms and not the problem. As I said above, next time it might be
a file that contains important data.

//Magnus

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] usage for 'with recursive'?

[hubert depesz lubaczewski]

there have been a discussions about how posdtgresql needs 'with
recursive' queries.

not that i would like to object the idea (new feature is always  a
good thing), but is anybody able to show me real usage of this kind of
queries?
as i see it the only usage for 'with recursive' is when one have a
tree-structure stored as:
create table objects (id serial primary key, parent_id int references
objects (id), ...)
and one want to do some "deep queries" without client-side recursion/loops.

is it the only thing 'with recursive' is useful for? i mean it sounds
unrealistic given that better data-structures for tree hierarchies
have been proposed and implemented.

best regards,

depesz

--
http://www.depesz.com/ - nowy, lepszy depesz

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

[GENERAL] Please Help ! Error with Access 2003

[hengky liwandouw]

Dear all,
   
  I have another problem with Access 2003 as front end with Postgre 8.1, ODBC 
version 8.02.02 Unicode.
   
  I just upgrade the Point of Sales Program to Access 2003 MDE, all station 
running XP Home wth Access 2003 on P4 3.0, 512MB Memory, 40GB HDD. Server run 
Windows Server 2003 on P4 3.0, 1GB Memory, 72GB UWSCSI HDD.
   
  Sometimes In POS Application, while user input sales transaction, this error 
message window appear :
   
  "The information you were working might be lost. microsoft office Access can 
by you compact and repair."
   
  with option to send or don't send this error report to Microsoft.
   
  What caused this error ? Please help !!! :(
   

 
-
We won't tell. Get more on shows you hate to love
(and love to hate): Yahoo! TV's Guilty Pleasures list.

Re: [GENERAL] Please Help ! Error with Access 2003

[Magnus Hagander]

On Thu, Mar 01, 2007 at 02:31:12AM -0800, hengky liwandouw wrote:
> Dear all,
>
>   I have another problem with Access 2003 as front end with Postgre 8.1, ODBC 
> version 8.02.02 Unicode.
>
>   I just upgrade the Point of Sales Program to Access 2003 MDE, all station 
> running XP Home wth Access 2003 on P4 3.0, 512MB Memory, 40GB HDD. Server run 
> Windows Server 2003 on P4 3.0, 1GB Memory, 72GB UWSCSI HDD.
>
>   Sometimes In POS Application, while user input sales transaction, this 
> error message window appear :
>
>   "The information you were working might be lost. microsoft office Access 
> can by you compact and repair."
>
>   with option to send or don't send this error report to Microsoft.
>
>   What caused this error ? Please help !!! :(

This isa problem in MS Access, and has nothing to do with PostgreSQL.
You will have to post your question in a MS Access forum.

(BTW, it's PostgreSQL or Postgres, not Postgre)

//Magnus

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Assistance with Query Optimisation?

[Isak Hansen]

On 3/1/07, Shaun Johnston <[EMAIL PROTECTED]> wrote:

Hi

Apologies in advance for the verbosity of my explanation for this
problem, but I think it's all pertinent.

I have a fairly simple query which postgresql's query planner seems to
be interpreting / optimising in interesting ways:

Query:

SELECT *
FROM account_transaction
WHERE account_id = 10783
ORDER BY account_transaction_timestamp asc
OFFSET 0
LIMIT 100

Table "public.account_transaction"

(I have stripped the default values etc for the sake of formatting)

Column |   Type   |
---+--+
 account_transaction_id| bigint   |
 account_transaction_timestamp | timestamp with time zone |
 account_id| integer  |
 other_account_transaction_id  | bigint   |
 transaction_reason| text |
 transaction_amount| numeric(15,2)|
 transaction_exchange_rate | numeric(20,10)   |
 transaction_base_amount   | numeric(15,2)|
 transaction_locked_until  | timestamp with time zone |
 transaction_approved  | boolean  |
Indexes:
"account_transaction_pkey" PRIMARY KEY, btree (account_transaction_id),
 tablespace "indexes"
"account_transaction_ai" btree (account_id), tablespace "indexes"
"account_transaction_timestamp" btree (account_transaction_timestamp),
 tablespace "indexes"
"account_transaction_tlu" btree (transaction_locked_until),
 tablespace "indexes"
Foreign-key constraints:
"$1" FOREIGN KEY (account_id) REFERENCES
  account(account_id)
"$2" FOREIGN KEY (other_account_transaction_id) REFERENCES
  account_transaction(account_transaction_id) ON UPDATE CASCADE

Query Plans:

With Sort and Limit
-
QUERY
PLAN
-
 Limit  (cost=0.00..12297.59 rows=100 width=120)
 (actual time=23.537..275476.496 rows=100 loops=1)
   ->  Index Scan Backward using account_transaction_timestamp on
   account_transaction
   (cost=0.00..640704.23 rows=5210 width=120)
   (actual time=23.529..275475.781 rows=100 loops=1)
 Filter: (account_id = 10783)
 Total runtime: 275476.944 ms


With Sort but no Limit

QUERY
PLAN

 Sort  (cost=18777.41..18790.43 rows=5210 width=120)
 (actual time=1081.226..1082.170 rows=308 loops=1)
   Sort Key: account_transaction_timestamp
   ->  Index Scan using account_transaction_ai on account_transaction
   (cost=0.00..18455.77 rows=5210 width=120)
   (actual time=47.731..1070.788 rows=308 loops=1)
 Index Cond: (account_id = 10783)
 Total runtime: 1083.182 ms

With Limit but no Sort
-
QUERY
PLAN
-
 Limit  (cost=0.00..354.24 rows=100 width=120)
 (actual time=0.029..1.070 rows=100 loops=1)
   ->  Index Scan using account_transaction_ai on account_transaction
   (cost=0.00..18455.77 rows=5210 width=120)
   (actual time=0.022..0.467 rows=100 loops=1)
 Index Cond: (account_id = 10783)
 Total runtime: 1.422 ms

With Limit and Sort, but sorted by transaction_base_amount
-
QUERY
PLAN
-
 Limit  (cost=18777.41..18777.66 rows=100 width=120)
 (actual time=55.294..56.221 rows=100 loops=1)
   ->  Sort  (cost=18777.41..18790.43 rows=5210 width=120)
   (actual time=55.285..55.600 rows=100 loops=1)
 Sort Key: transaction_base_amount
 ->  Index Scan using account_transaction_ai on
account_transaction
 (cost=0.00..18455.77 rows=5210 width=120)
 (actual time=0.057..53.187 rows=308 loops=1)
   Index Cond: (account_id = 10783)
 Total runtime: 56.597 ms

The table currently contains about 10 million records.

If I sort by account_transaction_timestamp then limit, the planner
performs a backward index scan based on account_transaction_timestamp
then limits.

If I sort but don't limit, it performs a forward scan on the table, then
a sort on the results.

If I limit but don't sort, it performs the forward scan then limits.

If I limit and sort, but sort by transaction_base_amount instead, it
performs a forward index scan, then sorts the results, then limits - in
stark contrast to sorting and limiting using acc

Re: [GENERAL] usage for 'with recursive'?

[Kenneth Downs]

hubert depesz lubaczewski wrote:

there have been a discussions about how posdtgresql needs 'with
recursive' queries.

not that i would like to object the idea (new feature is always  a
good thing), but is anybody able to show me real usage of this kind of
queries?
as i see it the only usage for 'with recursive' is when one have a
tree-structure stored as:
create table objects (id serial primary key, parent_id int references
objects (id), ...)
and one want to do some "deep queries" without client-side 
recursion/loops.


is it the only thing 'with recursive' is useful for? i mean it sounds
unrealistic given that better data-structures for tree hierarchies
have been proposed and implemented.


Better?  I think perhaps different.  There is materialized path, which 
requires a very problematic unlimited-length column to hold the path, 
and there is upper/lower bounds, which again requires client-side 
row-by-row processing.  Both have the unpleasant problem that changes to 
one row may affect many others.


AFAIK, the "WITH RECURSE" allows the simplest data structure, being 
key/parent_key.  The best benefit of this method is that it is a simple 
foreign key and no action on a row ever affects another row, unlike the 
other two.  If we could query out a list using WITH RECURSE it would 
become very powerful.




best regards,

depesz




--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.com / www.andromeda-project.org
Office: 631-689-7200   Cell: 631-379-0010

::Think you may have a problem with programming? Ask yourself this 
::question: do you worry about how to throw away a garbage can?



---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] cast bytea to text

[Willy-Bas Loos]

Dear List,

How can i cast bytea to text?
I´ve read about the DECODE function, but my 8.1 backend doesn´t recognize
it.
I´m trying to create an implicit cast using the function:

create or replace function bytea2text(bytea) returns text as
$$
select DECODE($1, 'escape');
$$
language sql strict;

Answer:
ERROR: function decode(bytea, "unknown") does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You may need to
add explicit type casts.
Context: SQL function "bytea2text"

Re: [GENERAL] Please Help ! Error with Access 2003

[Bill Moran]

In response to hengky liwandouw <[EMAIL PROTECTED]>:

> Dear all,
>
>   I have another problem with Access 2003 as front end with Postgre 8.1, ODBC 
> version 8.02.02 Unicode.
>
>   I just upgrade the Point of Sales Program to Access 2003 MDE, all station 
> running XP Home wth Access 2003 on P4 3.0, 512MB Memory, 40GB HDD. Server run 
> Windows Server 2003 on P4 3.0, 1GB Memory, 72GB UWSCSI HDD.
>
>   Sometimes In POS Application, while user input sales transaction, this 
> error message window appear :
>
>   "The information you were working might be lost. microsoft office Access 
> can by you compact and repair."
>
>   with option to send or don't send this error report to Microsoft.
>
>   What caused this error ? Please help !!! :(

If you actually believe that this problem _is_ with Postgres, grab the error
message in the Postgres server logs that corresponds with the MS Access error.

Based on that error, MS Access really has no idea what's going on.

-- 
Bill Moran
Collaborative Fusion Inc.

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] cast bytea to text

[Raymond O'Donnell]

Willy-Bas Loos wrote:


ERROR: function decode(bytea, "unknown") does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You may 
need to

add explicit type casts.
Context: SQL function "bytea2text"


That's because the first parameter of decode() should be of type TEXT, 
not bytea:


http://www.postgresql.org/docs/8.1/static/functions-binarystring.html#FUNCTIONS-BINARYSTRING-OTHER

Ray.

---
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
[EMAIL PROTECTED]
---

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] cast bytea to text

[Peter Eisentraut]

Willy-Bas Loos wrote:
> How can i cast bytea to text?
> I´ve read about the DECODE function, but my 8.1 backend doesn´t
> recognize it.

You want to use encode() in that direction.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] [HACKERS] urgent: upgraded to 8.2, getting kernel panics

[Merlin Moncure]

On 2/26/07, Merlin Moncure <[EMAIL PROTECTED]> wrote:

On 2/23/07, Tom Lane <[EMAIL PROTECTED]> wrote:
> "Merlin Moncure" <[EMAIL PROTECTED]> writes:
> > On friday we upgraded a critical backend server to postgresql 8.2
> > running on fedora core 4.
>
> Umm ... why that particular choice of OS?  Red Hat dropped update
> support for FC4 some time ago, and AFAIK the Fedora Legacy project
> is not getting things done.  How old is the kernel you're using?

Linux mojo 2.6.17-1.2142_FC4smp #1 SMP Tue Jul 11 22:57:02 EDT 2006
i686 i686 i386 GNU/Linux


Unfortunately, the decision about which kernel to run is more or less
out of my hands.  I would personally really dislike fedora and would
much prefer to be running centos/redhat as.  That said, your comments
and those of others are very helpul in regards to fixing that.

we tried update to the latest via yum update with no help.

as promised, here is the  best photo of the panic we could get:
http://img144.imageshack.us/my.php?image=dumpic6.jpg

We did an emergency downgrade to 8.1 and will monitor the
situation...the decision to get a new server has already been made and
hopefully it will be on a more stable platform.

big thanks to all who took a few minutes out of their day to lend a hand.


Following an emergency downgrade back to 8.1, the kernel panics went
away.  Note that I don't believe for a second that the database was
the root cause here...research suggest that the problem is due to some
type of bug in the scsi driver.  Exactly why 8.2 brings this out is a
mystery...working on getting an enterprise kernel on the server.

merlin

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] [HACKERS] urgent: upgraded to 8.2, getting kernel panics

[Magnus Hagander]

On Thu, Mar 01, 2007 at 08:32:57AM -0500, Merlin Moncure wrote:
> 
> Following an emergency downgrade back to 8.1, the kernel panics went
> away.  Note that I don't believe for a second that the database was
> the root cause here...research suggest that the problem is due to some
> type of bug in the scsi driver.  Exactly why 8.2 brings this out is a
> mystery...working on getting an enterprise kernel on the server.

Probably it's pushing some part of the I/O system harder than 8.1, thus
exposing the bug faster.

//Magnus

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[GENERAL] TSearch2: Can't build gendict output

[Hannes Dorbath]

PG 8.2.3 + tsearch_snowball_82.gz.

gendict line:

./config.sh -n de -s -p german_UTF_8 -v -C 'Snowball stemmer for German 
(UTF8)'


make in contrib/dict_de/:

gcc -march=prescott -O2 -pipe -Wall -Wmissing-prototypes -Wpointer-arith 
-Winline -Wdeclaration-after-statement -Wendif-labels 
-fno-strict-aliasing -fpic -I../tsearch2/snowball -I../tsearch2 -I. 
-I../../src/include -D_GNU_SOURCE   -c -o stem.o stem.c

stem.c: In function ‘r_prelude’:
stem.c:163: error: too many arguments to function ‘in_grouping_U’
stem.c:168: error: too many arguments to function ‘in_grouping_U’
stem.c:177: error: too many arguments to function ‘in_grouping_U’
stem.c: In function ‘r_mark_regions’:
stem.c:212: error: too many arguments to function ‘out_grouping_U’
stem.c:217: error: too many arguments to function ‘in_grouping_U’
stem.c:227: error: too many arguments to function ‘out_grouping_U’
stem.c:232: error: too many arguments to function ‘in_grouping_U’
stem.c: In function ‘r_standard_suffix’:
stem.c:320: error: too many arguments to function ‘in_grouping_b_U’
stem.c:347: error: too many arguments to function ‘in_grouping_b_U’
make: *** [stem.o] Error 1

What is the problem?

Thanks.

--
Regards,
Hannes Dorbath

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] TSearch2: Can't build gendict output

[Hannes Dorbath]

Seems I downloaded wrong libstemmer_c.tgz. Works with the files from my 
old install.


On 01.03.2007 15:59, Hannes Dorbath wrote:

What is the problem?



--
Regards,
Hannes Dorbath

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] Recursive/Wildcard Object Ownership Change

[Brian A. Seklecki]

Corrections (was in a hurry to get to class):


- There is no way to specify recursion in "ALTER _DATABASE_ OWNER TO 
rolename" (changes all objects)



---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] why can't I increase shared buffers to higher value?

[Brian A. Seklecki]

http://lists.freebsd.org/pipermail/freebsd-hackers/2006-December/018854.html

~BAS

On Wed, 28 Feb 2007, Dino Vliet wrote:


I have the following config but increasing the shared buffers to a value 
greater then 32 doesn't let the database server start (I want a value of 256MB 
there because I will have a giant table of 12 million rows which will be 
qeuried extremely).


I have a 3GB RAM amd64 system running freebsd 6.1 with:

Maintenance_work_mem is 32MB
Max_stack_depth is 3MB
Shared_buffers is 32MB
Temp_buffers is 8MB
Work_mem is 32MB
 Max_fsm_pages is 204800
 Max_connections is 3

And I am doing this:

sysctl -w kern.ipc.shmmax=1954311424
sysctl -w kern.ipc.shmall=16384

Whay is my shared buffer value not increasing?
Hope you can help me out or give me a few tips.
O yeah, I'm using version 8.2.3.

Thanks









Have a burning question? 
Go to www.Answers.yahoo.com and get answers from real people who know.


l8*
-lava (Brian A. Seklecki - Pittsburgh, PA, USA)
   http://www.spiritual-machines.org/

"...from back in the heady days when "helpdesk" meant nothing, "diskquota"
meant everything, and lives could be bought and sold for a couple of pages
of laser printout - and frequently were."

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] PG periodic Error on W2K

[Tom Lane]

Magnus Hagander <[EMAIL PROTECTED]> writes:
> On Thu, Mar 01, 2007 at 09:44:19AM +0900, Paul Lambert wrote:
>> I am periodically getting errors pop up on the server console of the 
>> following nature:
>> The File or directory D:\PostgresQL\Data\global\pgstat.stat is corrupt 
>> and unreadable. Please run the Chkdsk utility.

> They can *not* be caused by a bug in PostgreSQL - no more than a kernel
> oops in linux is the fault of PostgreSQL. Now, we do push the filesystem
> and disk layer in an unusual way with the pgstats writes, gievn that we
> rewrite the same file over and over and over and over again at very
> short intervals. But nothing says we're not allowed to do that :-)

I'm wondering whether the message is coming from the kernel, or some
sort of file-scanning utility that gets confused when a file is deleted
while it's looking at it.

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Bad performace of a query

[Kaloyan Iliev]

Hi,

The index doesn't cost you so much, seq SEQ Scan actully does:
 Seq Scan on isbns_a_descubrir 
(cost=0.00..8067.91 rows=1 width=21) (actual time=30.044..30.044 rows=1
loops=2025)

This seq scan is called once for every row of librosdisponibilidadtemp
which passes the WHERE condition.
So Here "Index Scan using
librosdisponibilidadtemp_idx_proceso on librosdisponibilidadtemp 
(cost=
1.01..133557993.56 rows=2068 width=21) (actual
time=5722.607..790552.588 rows=9 loops=1)" it
says how much it will cost you to calculate the upper seq scan and the
seq scan on (Seq Scan on
raizpaises) and the index scan on libros.

I suggest you to create index on table isbns_a_descubrir over
column isbn. This will hurry the query.
And use join instead of IN for table raizpaises. This should also save
some time.

Regards,
   Kaloyan Iliev

ave this query:
  
SELECT DISTINCT isbn, CURRENT_TIMESTAMP, 1
  FROM librosdisponibilidadtemp
 WHERE proceso = ai_proceso
   AND gen_isbn_pais(isbn) IN (SELECT pais FROM raizpaises)
  
   AND NOT EXISTS
   ( SELECT isbn
   FROM libros
  WHERE isbn = librosdisponibilidadtemp.isbn)
   AND NOT EXISTS
   ( SELECT isbn
  
   FROM isbns_a_descubrir
  WHERE isbn = librosdisponibilidadtemp.isbn);
  
and the plan execution is
Unique  (cost=133558107.45..133558128.13 rows=414 width=21) (actual
time=
790552.899..790553.098 rows=9 loops=1)
  ->  Sort  (cost=133558107.45..133558112.62 rows=2068 width=21)
(actual time=790552.882..790552.944 rows=9 loops=1)
    Sort Key: isbn, now(), 1
    ->  Index Scan using librosdisponibilidadtemp_idx_proceso on
librosdisponibilidadtemp  (cost=
1.01..133557993.56 rows=2068 width=21) (actual
time=5722.607..790552.588 rows=9 loops=1)
  Index Cond: (proceso = 28465)
  Filter: ((hashed subplan) AND (NOT (subplan)) AND (NOT
(subplan)))
  SubPlan
    ->  Seq Scan on isbns_a_descubrir 
(cost=0.00..8067.91 rows=1 width=21) (actual time=30.044..30.044 rows=1
loops=2025)
  Filter: ((isbn)::bpchar = $1)
    ->  Index Scan using "libros_idx_ISBN" on libros 
(cost=0.00..5.95 rows=1 width=21) (actual time=12.938..12.938 rows=1
loops=50512)
  Index Cond: (isbn = $1)
    ->  Seq Scan on raizpaises  (cost=
0.00..1.01 rows=1 width=10) (actual time=0.764..0.871 rows=1 loops=1)
Total runtime: 790553.561 ms
  
The libros table has 120 regs.
The isbns_a_descubrir table has 30 regs.
The librosdisponibilidadtemp table has 5 regs. 
  
does anybody can explain me, why using index
ibrosdisponibilidadtemp_idx_proceso is so slow and the others
conditions are good enough
Thanks everybody
  
  

[GENERAL] strange behaviour with sub-select and pl/pgSQL

[Robert Partyka]

Hi,

I found PostgreSQL have strange behaviour with sub-select and pl/pgSQL, 
see the case study at:

http://www.bobson.pl/pgsql/pgsql_sb1.html

I know my version is quite old. Its most new in gentoo (hope 8.2 will be 
soon in gentoo portage).

test=# select version();
version
-
PostgreSQL 8.1.5 on i686-pc-linux-gnu, compiled by GCC 
i686-pc-linux-gnu-gcc (GCC) 4.1.1 (Gentoo 4.1.1)


regards,
Robert
[EMAIL PROTECTED]

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

[GENERAL] 2d array issues

[John Gant]

Hello,
Can anyone tell me why the function below is breaking at the second
assignment? I am trying to set values for columns 1 and 2 for row 1. The
compilation and error are below the function definition. Thanks in advance.


  1. CREATE OR REPLACE FUNCTION test() returns void AS $$
  2. declare
  3. matrix numeric [][];
  4. cnt numeric := 1;
  5. begin
  6. matrix[cnt][1] := 3;
  7. raise notice '%', matrix[cnt][1];
  8. matrix[cnt][2] := 4;
  9. raise notice '%', matrix[cnt][2];
  10. end;
  11. $$ LANGUAGE plpgsql;
  12.
  13. error...
  14. stocks=# \i test.sql
  15. CREATE FUNCTION
  16. stocks=# select test();
  17. NOTICE:  3
  18. ERROR:  invalid array subscripts
  19. CONTEXT:  PL/pgSQL FUNCTION "test" line 7 at assignment

[GENERAL] 8.1.8 Installer Fails on Win2k Server

[Saqib Awan]

I am installing on a Cisco Media Server 7800 running Win2k Server and am 
noticing that the installer fails every time with the error dialog saying 
"Failed to create process for initdb: Access is denied". It looks like that 
I need to change some permission in the registry allowing other users spawn 
processes. Any pointer in this respect is highly appreciated.



---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] PostgreSQL 8.2.3, Jboss 4.0.3 and postgresql-8.2-504.jdbc4

[Andy Dale]

Hi,

Have tried putting the JDBC driver in $JBOSS_HOME/server//lib, that is where the I always put jdbc drivers (The default
Hypersonic driver is also in this directory).

Cheers,

Andy

On 28/02/07, Andrew Madu <[EMAIL PROTECTED]> wrote:


Hi,
i've just upgraded from posgreSQL 8.1 to 8.2.3. I placed
postgresql-8.2-504.jdbc4 in /deploy/ejb3.deployer, restarted jboss and the
proces breaks when it gets to detecting what postgresql driver is being
used! I remove the jdbc4 driver and replace it with, my original driver,
postgresql-8.1-405.jdbc3 and all works fine again!

What is the issue here?

I am using Java 1.5.0_06 on Window XP SP2

--
Regards

Andrew

[GENERAL] Constructors for dates, times, and timestamps

[Andrew T. Robinson]

Migrating from DB/2 6.1 to PostgreSQL 8.1.4,

The following work under DB/2, but I can find no analog in the 
PostgreSQL documentation:


   time('00:00:00')  [there is to_date() and to_timestamp(), but no 
to_time()?]


   timestamp(u.date, u.time) [where u.date is of type DATE and u.time 
is of type TIME]


As with any paradigm shift, I'm sure this will be embarrassingly simple, 
but I've spent hours RTFMing to no avail.


Andy
begin:vcard
fn:Andrew T. Robinson, CISM, CISSP
n:Robinson;Andrew
org:NMI InfoSecurity Solutions
adr:;;145 Newbury Street Second Floor;Portland;ME;04101;USA
email;internet:[EMAIL PROTECTED]
title:President
tel;work:207-780-6381 x226
tel;fax:207-780-6301
x-mozilla-html:TRUE
url:http://www.nmi.net
version:2.1
end:vcard


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] performance of partitioning?

[Brent Wood]

George Nychis wrote:

Hey all,

So I have a master table called "flows" and 400 partitions in the 
format "flow_*" where * is equal to some epoch.


Each partition contains ~700,000 rows and has a check such that 1 
field is equal to a value:
   "flows_1107246900_interval_check" CHECK ("interval" = '2005-02-01 
03:35:00'::timestamp without time zone)


Each partition has a different and unique non-overlapping check.

This query takes about 5 seconds to execute:
dp=> select count(*) from flows_1107246900;
 count

 696836
(1 row)

This query has been running for 10 minutes now and hasn't stopped:
dp=> select count(*) from flows where interval='2005-02-01 03:35:00';

Isn't partitioning supposed to make the second query almost as fast?  
My WHERE is exactly the partitioning constraint, therefore it only 
needs to go to 1 partition and execute the query.


Why would it take magnitudes longer to run?  Am i misunderstanding 
something?


We have a db with only 200,000,000 records, partitioned by year with 
about 15 partitions. There is a clustered index on the timestamp field 
and queries like a 25 wide self join for 3 months data are around 20 
seconds. On a desktop box with a single SATA drive.


If you are querying by timestamp, I suggest a clustered index will help.

Brent Wood

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Bad performace of a query

[Kaloyan Iliev]

Hi,

The index doesn't cost you so much, seq SEQ Scan actully does:
 Seq Scan on isbns_a_descubrir 
(cost=0.00..8067.91 rows=1 width=21) (actual time=30.044..30.044 rows=1
loops=2025)

This seq scan is called once for every row of librosdisponibilidadtemp
which passes the WHERE condition.
So Here "Index Scan using
librosdisponibilidadtemp_idx_proceso on librosdisponibilidadtemp 
(cost=
1.01..133557993.56 rows=2068 width=21) (actual
time=5722.607..790552.588 rows=9 loops=1)" it
says how much it will cost you to calculate the upper seq scan and the
seq scan on (Seq Scan on
raizpaises) and the index scan on libros.

I suggest you to create index on table isbns_a_descubrir over
column isbn. This will hurry the query.
And use join instead of IN for table raizpaises. This should also save
some time.

Regards,
   Kaloyan Iliev

ave this query:
  
SELECT DISTINCT isbn, CURRENT_TIMESTAMP, 1
  FROM librosdisponibilidadtemp
 WHERE proceso = ai_proceso
   AND gen_isbn_pais(isbn) IN (SELECT pais FROM raizpaises)
  
   AND NOT EXISTS
   ( SELECT isbn
   FROM libros
  WHERE isbn = librosdisponibilidadtemp.isbn)
   AND NOT EXISTS
   ( SELECT isbn 
   FROM isbns_a_descubrir
  WHERE isbn = librosdisponibilidadtemp.isbn);
  
and the plan execution is
Unique  (cost=133558107.45..133558128.13 rows=414 width=21) (actual
time=
790552.899..790553.098 rows=9 loops=1)
  ->  Sort  (cost=133558107.45..133558112.62 rows=2068 width=21)
(actual time=790552.882..790552.944 rows=9 loops=1)
    Sort Key: isbn, now(), 1
    ->  Index Scan using librosdisponibilidadtemp_idx_proceso on
librosdisponibilidadtemp  (cost=
1.01..133557993.56 rows=2068 width=21) (actual
time=5722.607..790552.588 rows=9 loops=1)
  Index Cond: (proceso = 28465)
  Filter: ((hashed subplan) AND (NOT (subplan)) AND (NOT
(subplan)))
  SubPlan
    ->  Seq Scan on isbns_a_descubrir 
(cost=0.00..8067.91 rows=1 width=21) (actual time=30.044..30.044 rows=1
loops=2025)
  Filter: ((isbn)::bpchar = $1)
    ->  Index Scan using "libros_idx_ISBN" on libros 
(cost=0.00..5.95 rows=1 width=21) (actual time=12.938..12.938 rows=1
loops=50512)
  Index Cond: (isbn = $1)
    ->  Seq Scan on raizpaises  (cost=
0.00..1.01 rows=1 width=10) (actual time=0.764..0.871 rows=1 loops=1)
Total runtime: 790553.561 ms
  
The libros table has 120 regs.
The isbns_a_descubrir table has 30 regs.
The librosdisponibilidadtemp table has 5 regs. 
  
does anybody can explain me, why using index
ibrosdisponibilidadtemp_idx_proceso is so slow and the others
conditions are good enough
Thanks everybody
  
  

[GENERAL] postgres init script reports failure, but postmaster started

[IN Conny]

Hi all,
I'm not sure if the following is a bug or not. The symptoms I have is pretty 
much like this bug
https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=166117
which have been documented in various archives. However, I run postgres 8.1.

What I do is i try to restore from a backup. I have a base backup and some 
archived WALs which I restore from. 

If I, as a final step in the restoration, start up postgres with 
'/etc/init.d/postgresql-8.1 start' it will print [fail] to the shell and exit. 
This happens about halfway through the restoration of WAL archives.
However, if I call '/etc/init.d/postgresql-8.1 status' it says that the status 
is online; the database is running. If I examine the logs it seems that the 
startup has continued running beyond the point where it stopped printing to 
shell. All WAL archives are restored. And the content of the database seems to 
be ok.

I also tried to use '/usr/lib/postgresql/8.1/bin/pg_ctl start' to start up the 
database instead of the /etc/init.d script. This works without any error 
messages.

Could I safely assume that the restoration was successful, although 
/etc/init.d/postgresql-8.1 says it failed?

Best,
I.N.


-

Stava rätt! Stava lätt! Yahoo! Mails stavkontroll tar hand om tryckfelen och 
mycket mer! Få den på http://se.mail.yahoo.com

[GENERAL] Can I getting a unique ID from a select

[Timasmith]

I am using hibernate, using a view like a read only table and I need a
primary key each time a select is issued.

So in Oracle terms this might work, though I am skeptical that
Hibernate is going to return a cached result.

create view myview as
select rownum, t1.field, t2.field
from tableOne t1, tableTwo t2
where t1.key = t2.fkey

select * from myview

But what I really need is

select makemeauniquekey, t1.field, t2.field
...

Maybe there is no way I think...  incrementing a sequence per select
is untenable.


---(end of broadcast)---
TIP 6: explain analyze is your friend

[GENERAL] US Highschool database in postgres

[mobil]

Is there a  downlaodable high school database in postgresql

thanks a lot guys

Mobil


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

[GENERAL] postgres powered search engine

[John]

http://teenwag.com/search


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] General Ledger db design

[Brent Wood]

Filipe Fernandes wrote:

[snip]
  

Martin Winsler wrote:


Does anybody have any experience or knowledge of building
financial accounting databases?
  

[snip]

I too was thinking about building a double entry accounting system and
I've been following this thread closely, learning a few tricks on the way :)

I've been gathering up ideas on how a db schema might look by reading the
gnuCash tutorials on double-entry accounting, but does anybody know if
there are open source applications that have already solved this on the db
layer?
  


Look at SQL-Ledger and LedgerSMB

Brent Wood

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] Bug in row locking?

[Ing. Pavel Hanák]

Hi,

I noticed a problem by trying to lock rows of the view for update,
if the definition of the view consists the reference to another view.

Example:

create table test (a int)

insert into test values (1)
insert into test values (2)
insert into test values (3)
insert into test values (4)

create view vtest1 as
select a from test
where a > 1

create view vtest2 as
select a from vtest1
where a > 2

If you try to lock rows in the view vtest1, everything is OK.

But if you try to lock rows in the view vtest2, this error message
occurs:

ERROR:  no relation entry for relid 5

Any ideas, whats wrong?

Best regards

Pavel Hanak


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

[GENERAL] supporting 2000 simultaneous connections.

[Shiva Sarna]

Hi,

I am working on a web application where the front end is struts framework and 
back end is PgSQL 7.4.

The client want us to support 2000 simultaneous users. My question is will 
there be any performance degradation if I increase the max_connections of pgsql 
to 2000. 

Is there a limit to maximum number of simultaneous user supoorted by pgsql.

thanks for your time and help.

regards

Shiva


-
 Here’s a new way to find what you're looking for - Yahoo! Answers 

Re: [GENERAL] PG periodic Error on W2K

[Magnus Hagander]

On Thu, Mar 01, 2007 at 10:45:16AM -0500, Tom Lane wrote:
> Magnus Hagander <[EMAIL PROTECTED]> writes:
> > On Thu, Mar 01, 2007 at 09:44:19AM +0900, Paul Lambert wrote:
> >> I am periodically getting errors pop up on the server console of the 
> >> following nature:
> >> The File or directory D:\PostgresQL\Data\global\pgstat.stat is corrupt 
> >> and unreadable. Please run the Chkdsk utility.
> 
> > They can *not* be caused by a bug in PostgreSQL - no more than a kernel
> > oops in linux is the fault of PostgreSQL. Now, we do push the filesystem
> > and disk layer in an unusual way with the pgstats writes, gievn that we
> > rewrite the same file over and over and over and over again at very
> > short intervals. But nothing says we're not allowed to do that :-)
> 
> I'm wondering whether the message is coming from the kernel, or some
> sort of file-scanning utility that gets confused when a file is deleted
> while it's looking at it.

That specific message comes from the kernel.

//Magnus

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] General Ledger db design

[Merlin Moncure]

On 2/26/07, Filipe Fernandes <[EMAIL PROTECTED]> wrote:

[snip]
>>> Martin Winsler wrote:
 Does anybody have any experience or knowledge of building
 financial accounting databases?
[snip]

I too was thinking about building a double entry accounting system and
I've been following this thread closely, learning a few tricks on the way :)

I've been gathering up ideas on how a db schema might look by reading the
gnuCash tutorials on double-entry accounting, but does anybody know if
there are open source applications that have already solved this on the db
layer?


start here:
http://www.ledgersmb.org/

merlin

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] creating a function with a variable table name

[George Nychis]

Hey all,

I'm trying to create a function in which the table a query is run on is variable, but I 
guess this is not as easy as I thought.


BEGIN
dp=> CREATE FUNCTION stats_addr_dst(date,text)
dp-> RETURNS setof addr_count
dp-> AS 'SELECT ip,sum(dst_packets)
dp'> FROM(
dp'>   (SELECT dst_ip AS ip,sum(src_packets) AS dst_packets
dp'>   FROM $2
dp'>   WHERE interval=$1
dp'>   GROUP BY dst_ip)
dp'> UNION ALL
dp'> (SELECT src_ip AS ip,sum(dst_packets) AS dst_packets
dp'>   FROM $2
dp'>   WHERE interval=$1
dp'>   GROUP BY src_ip) )
dp'> AS topk
dp'> GROUP BY topk.ip
dp'> HAVING sum(dst_packets)>0
dp'> ORDER BY sum(dst_packets) DESC;'
dp-> LANGUAGE SQL;
ERROR:  syntax error at or near "$2" at character 179
LINE 6:   FROM $2
   ^
How can I pass the table name?

Thanks!
George

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] supporting 2000 simultaneous connections.

[Hannes Dorbath]

On 28.02.2007 07:15, Shiva Sarna wrote:
The client want us to support 2000 simultaneous users. My question is will there be any performance degradation if I increase the max_connections of pgsql to 2000. 


Consider using a connection pool..

--
Regards,
Hannes Dorbath

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] supporting 2000 simultaneous connections.

[Bill Moran]

In response to Shiva Sarna <[EMAIL PROTECTED]>:

> Hi,
> 
> I am working on a web application where the front end is struts framework
> and back end is PgSQL 7.4.
> 
> The client want us to support 2000 simultaneous users. My question is will
> there be any performance degradation if I increase the max_connections of
> pgsql to 2000. 
> 
> Is there a limit to maximum number of simultaneous user supoorted by pgsql.
> 
> thanks for your time and help.

You can support as many simultaneous users as your hardware can allow.  Each
connection requires a certain amount of RAM and CPU to maintain.  As long
as you have enough memory and CPU power, you'll be able to support that
many connections.

Performance _will_ degrade if all of those connections are busy at once, but
that's going to happen with any shared system.  The disk can only read from
one area at a time, and other system resources will be contended for as well.

You might want to take some time to investigate what "simultaneous
users" really means for your application.  For example, in a web
application, 2000 simultaneous users usually equates to less than 100
actual database connections, as web users spend most of their time
reading pages and very little time actually talking to the database.

You might also gain efficiencies by using something such as pgpool, but
that depends on the nature of your application.

In any event, I'm not aware of anything that would prevent you from having
2000 simultaneous connections, as long as you had beefy enough hardware to
handle it.

-- 
Bill Moran
Collaborative Fusion Inc.

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Can I getting a unique ID from a select

[Bill Moran]

In response to "Timasmith" <[EMAIL PROTECTED]>:

> I am using hibernate, using a view like a read only table and I need a
> primary key each time a select is issued.
> 
> So in Oracle terms this might work, though I am skeptical that
> Hibernate is going to return a cached result.
> 
> create view myview as
> select rownum, t1.field, t2.field
> from tableOne t1, tableTwo t2
> where t1.key = t2.fkey
> 
> select * from myview
> 
> But what I really need is
> 
> select makemeauniquekey, t1.field, t2.field
> ...
> 
> Maybe there is no way I think...  incrementing a sequence per select
> is untenable.

Create a sequence and use nextval().

-- 
Bill Moran
Collaborative Fusion Inc.

[EMAIL PROTECTED]
Phone: 412-422-3463x4023


IMPORTANT: This message contains confidential information and is
intended only for the individual named. If the reader of this
message is not an intended recipient (or the individual
responsible for the delivery of this message to an intended
recipient), please be advised that any re-use, dissemination,
distribution or copying of this message is prohibited. Please
notify the sender immediately by e-mail if you have received
this e-mail by mistake and delete this e-mail from your system.
E-mail transmission cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The
sender therefore does not accept liability for any errors or
omissions in the contents of this message, which arise as a
result of e-mail transmission.


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] supporting 2000 simultaneous connections.

[Joshua D. Drake]

Hannes Dorbath wrote:
> On 28.02.2007 07:15, Shiva Sarna wrote:
>> The client want us to support 2000 simultaneous users. My question is
>> will there be any performance degradation if I increase the
>> max_connections of pgsql to 2000. 
> 
> Consider using a connection pool..

But to answer the question, it entirely depends on your hardware. I
wouldn't try this with less than 16Gig of ram and 4-8 cores.

Joshua D. Drake

> 


-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Why does "group by" need to match select fields?

[Erik Jones]

On Feb 28, 2007, at 6:57 PM, Omar Eljumaily wrote:

OK, I see what's going on.  I can have more than one max(amount)  
with the same amount and payee.  Thanks so much.  Like I said, it's  
sort of dogged me off and on many times.


Thanks.


Bill Moran wrote:

Omar Eljumaily <[EMAIL PROTECTED]> wrote:

Sorry if this isn't exactly postgresql specific.  I periodically  
run into this problem, and I'm running into it now.  I'm  
wondering if there's something about "group by" that I don't  
understand.  As an example what I'd want to do is return the "id"  
value for the check to each payee that has the highest amount.   
It seems like there's no problem with ambiguity in logic, but  
postgresql + other sql servers balk at it.  The group by fields  
need to explicitly match the select fields with the exception of  
the aggregate function(s?).


create table checks
{
id serial,
payee text,
amount double
};

select max(amount), payee, id from checks group by payee;

Why won't the above work?  Is there another way to get the id for  
the record with the highest amount for each payee?




Because it's ambiguous.  If you're grabbing max() for amount, which
id tuple do you want?

Perhaps the way you're storing your data, those answers aren't  
ambiguous,

but the database doesn't know that.  Take this query as an example:

select max(amount), max(checknumber), payee from checks group by  
payee;


In that case, the highest checknumber and the highest check amount
probably won't come from the same tuple.  If you were to throw in
there:

select max(amount), max(checknumber), payee, id from checks group  
by payee;


Which id does it give you?  The one that matches max(amount) or  
the one

that matches max(checknumber)?


Omar, note that in many cases you can do:

select c.amount, c.payee, c.id
from checks c
where c.amount = (select max(amount)
   from checks
   where payee=c.payee);


erik jones <[EMAIL PROTECTED]>
sofware developer
615-296-0838
emma(r)

Re: [GENERAL] creating a function with a variable table name

[Joshua D. Drake]

> dp'> HAVING sum(dst_packets)>0
> dp'> ORDER BY sum(dst_packets) DESC;'
> dp-> LANGUAGE SQL;
> ERROR:  syntax error at or near "$2" at character 179
> LINE 6:   FROM $2
>^
> How can I pass the table name?

Look at the EXECUTE option in plpgsql.

Joshua D. Drake



> 
> Thanks!
> George
> 
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend
> 


-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] postgres init script reports failure, but postmaster started

[Tom Lane]

IN Conny <[EMAIL PROTECTED]> writes:
> If I, as a final step in the restoration, start up postgres with 
> '/etc/init.d/postgresql-8.1 start' it will print [fail] to the shell and 
> exit. This happens about halfway through the restoration of WAL archives.

You're probably using an init script that is coded to report failure if the
postmaster isn't allowing connections within 60 seconds or so of being
started.  This is mostly cosmetic, but you might want to adjust the script.

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] supporting 2000 simultaneous connections.

[Joshua D. Drake]

Bill Moran wrote:
> In response to Shiva Sarna <[EMAIL PROTECTED]>:
> 
>> Hi,
>>
>> I am working on a web application where the front end is struts framework
>> and back end is PgSQL 7.4.

*cough*, you are going to greatly decrease your ability to scale if you
are running anything less than 8.1.
> 
> Performance _will_ degrade if all of those connections are busy at once, but
> that's going to happen with any shared system.  The disk can only read from
> one area at a time, and other system resources will be contended for as well.

7.4 doesn't scale to what he wants, even on big hardware.

Sincerely,

Joshua D. Drake


-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Constructors for dates, times, and timestamps

[Tom Lane]

"Andrew T. Robinson" <[EMAIL PROTECTED]> writes:
> The following work under DB/2, but I can find no analog in the 
> PostgreSQL documentation:

> time('00:00:00')  [there is to_date() and to_timestamp(), but no 
> to_time()?]

Write it as a cast, either SQL-spec CAST() or PG :: notation.

regression=# select '00:00:00'::time;
   time
--
 00:00:00
(1 row)

In many situations PG also accepts the same function-like notation for
specifying casts that DB/2 seems to be using, but in this particular
case it doesn't work because TIME(n) is a datatype specification
according to the SQL spec, and the special syntax needed for that
conflicts with this usage.

> timestamp(u.date, u.time) [where u.date is of type DATE and u.time 
> is of type TIME]

You can add a date and a time to get a timestamp:

regression=# select '3-1-2007'::date + '12:34'::time;
  ?column?
-
 2007-03-01 12:34:00
(1 row)

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] ftell mismatch with expected position

[Jan Muszynski]

I found this thread (I'm posting here because I'm not subscribed to 
Hackers)
http://www.mail-archive.com/pgsql-hackers@postgresql.org/msg85241.html

which seems to be talking about this issue. I'm just wondering what the 
current status on this is. Someone I know is getting this error -(I'm not 
100% sure what version he's running other than it's an 8.2 series, 
although I am finding out. And yes it's definitely on Windows).

Does he have to wait until 8.3? Will it be fixed in 8.3? Or is it fixed in 
the latest release?

Any suggestions for a workaround? (I told him he can generate a Plain 
backup and compress the result using zip, rar, etc - will this work?)

TIA
-jcm

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] creating a function with a variable table name

[A. Kretschmer]

am  Thu, dem 01.03.2007, um 11:17:46 -0500 mailte George Nychis folgendes:
> Hey all,
> 
> I'm trying to create a function in which the table a query is run on is 
> variable, but I guess this is not as easy as I thought.
> 
> BEGIN
> dp=> CREATE FUNCTION stats_addr_dst(date,text)
> ...
> dp'>   FROM $2
>^
> How can I pass the table name?

Build a string with your SQL and EXECUTE this string.


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Constructors for dates, times, and timestamps

[Martijn van Oosterhout]

On Tue, Feb 27, 2007 at 07:47:32AM -0500, Andrew T. Robinson wrote:
> Migrating from DB/2 6.1 to PostgreSQL 8.1.4,
> 
> The following work under DB/2, but I can find no analog in the 
> PostgreSQL documentation:
> 
>time('00:00:00')  [there is to_date() and to_timestamp(), but no 
> to_time()?]

Well, you can always use to_timestamp and then cast to time, but this
also works:

# select "time"('00:00:00');
   time
--
 00:00:00
(1 row)

I can't explain the need for the quotes, some kind of grammer issue.

>timestamp(u.date, u.time) [where u.date is of type DATE and u.time 
> is of type TIME]

There is the function datetime_pl(date,time) whic does that, but most
people just use +'

# select '2007-05-02'::date + '22:33:44'::time;
  ?column?
-
 2007-05-02 22:33:44
(1 row)

You can wrap it into a simple function if that makes it easier to
understand.

Oddly, the documentation indeed doesn't list all the functions, but the
operators will do what you want also.

http://www.postgresql.org/docs/current/static/functions-datetime.html

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

[GENERAL] get username of user calling function?

[George Nychis]

Hi,

Is it possible to get the username of the user calling a function?

Just as a test, a function which would return the user their username.

Thanks!
George

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] cast bytea to text

[Albe Laurenz]

> How can i cast bytea to text?
> I´ve read about the DECODE function, but my 8.1 backend 
> doesn´t recognize it.
> I´m trying to create an implicit cast using the function:
> 
> create or replace function bytea2text(bytea) returns text as 
> $$
> select DECODE($1, 'escape');
> $$
> language sql strict;
> 
> Answer:
> ERROR: function decode(bytea, "unknown") does not exist

Try ENCODE instead of DECODE ...

Yours,
Laurenz Albe

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] creating a function with a variable table name

[George Nychis]

do I need to use PREPARE with it also?

A. Kretschmer wrote:

am  Thu, dem 01.03.2007, um 11:17:46 -0500 mailte George Nychis folgendes:

Hey all,

I'm trying to create a function in which the table a query is run on is 
variable, but I guess this is not as easy as I thought.


BEGIN
dp=> CREATE FUNCTION stats_addr_dst(date,text)
...
dp'>   FROM $2
   ^
How can I pass the table name?


Build a string with your SQL and EXECUTE this string.


Andreas


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Why does "group by" need to match select fields?

[Martijn van Oosterhout]

On Wed, Feb 28, 2007 at 04:57:08PM -0800, Omar Eljumaily wrote:
> OK, I see what's going on.  I can have more than one max(amount) with 
> the same amount and payee.  Thanks so much.  Like I said, it's sort of 
> dogged me off and on many times.

Note that in the special case of max/min, you can use things like ORDER
BY/LIMIT to acheive the effect you want. And DISTINCT ON () is also
useful in such situations.
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

Re: [GENERAL] Why does "group by" need to match select fields?

[Martijn van Oosterhout]

On Wed, Feb 28, 2007 at 04:57:08PM -0800, Omar Eljumaily wrote:
> OK, I see what's going on.  I can have more than one max(amount) with 
> the same amount and payee.  Thanks so much.  Like I said, it's sort of 
> dogged me off and on many times.

Note that in the case of min/max you can use ORDER BY/LIMIT or DISTINCT
ON () to acheive the effect you want.

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

Re: [GENERAL] creating a function with a variable table name

[A. Kretschmer]

am  Thu, dem 01.03.2007, um 11:47:02 -0500 mailte George Nychis folgendes:
> do I need to use PREPARE with it also?

No.


> 
> A. Kretschmer wrote:
> >am  Thu, dem 01.03.2007, um 11:17:46 -0500 mailte George Nychis folgendes:
> >>Hey all,

Please no top-posting with fullquote below your text.


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] get username of user calling function?

[A. Kretschmer]

am  Thu, dem 01.03.2007, um 11:40:11 -0500 mailte George Nychis folgendes:
> Hi,
> 
> Is it possible to get the username of the user calling a function?

You can use the current_user - variable. Select current_user;


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Bug in row locking?

[Tom Lane]

[EMAIL PROTECTED] (Ing. Pavel =?iso-8859-2?Q?Han=E1k?=) writes:
> But if you try to lock rows in the view vtest2, this error message
> occurs:
> ERROR:  no relation entry for relid 5

Wow, amazing no one reported this before, because it seems to be broken
all the way back to 7.3 ... will look into it.

regards, tom lane

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] How often do I need to reindex tables?

[Vivek Khera]

On Feb 28, 2007, at 5:35 PM, Bill Moran wrote:


Just an FYI ... I remembered what prompted the cron job.

We were seeing significant performance degradation.  I never did  
actual
measurements, but it was on the order of "Bill, why is restoring  
taking
such a long time?" from other systems people.  At the time, I poked  
around

and tried some stuff here and there and found that reindex restored
performance.  I didn't look at actual size at that time.


I have two huge tables (one tracks messages sent, one tracks URL  
click-throughs from said messages) from which I purge old data every  
few weeks.  The primary key indexes on these get bloated after a few  
months and performance goes way down like you observe.  A reindex  
fixes up the performance issues pretty well on those tables, and  
often shaves off a few gigs of disk space too.


We have to manually run the reindex because it has to be timed such  
that the service is not impacted (ie, run on major holiday weekends)  
and we have to take down part of the service and point other parts to  
backup servers, etc.  Not an easy chore...


This is on Pg 8.1.  Don't even ask me how it was in the 7.4 days when  
we have maybe 10% of the data! :-)





smime.p7s
Description: S/MIME cryptographic signature

Re: [GENERAL] get username of user calling function?

[George Nychis]

A. Kretschmer wrote:

You can use the current_user - variable. Select current_user;


I'm trying to create a function in which users can only kill their own processes, it works 
perfectly if i hardcode a username in such as this:

CREATE FUNCTION kill_process(integer) RETURNS boolean
AS 'select pg_cancel_backend(procpid)
FROM (SELECT procpid FROM pg_stat_activity WHERE procpid=$1 and usename=''gnychis'') 
AS kill;'

LANGUAGE SQL SECURITY DEFINER;

But if i try to replace usename=''gnychis'' with usename=current_user it no 
longer works.

Any ideas?

- George

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] US Highschool database in postgres

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/27/07 01:04, mobil wrote:
> Is there a  downlaodable high school database in postgresql

What exactly do you mean by "high school database"?


-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF5wuFS9HxQb37XmcRAk8OAKCEKRtzaXppkdVedA4ZB798wrG1XgCeOqpi
cnqACyTWIvWW0mTMXSQEqZY=
=q/4U
-END PGP SIGNATURE-

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] PG periodic Error on W2K

[MargaretGillon]

> Date: Thu, 01 Mar 2007 10:06:44 +0900
> From: Paul Lambert <[EMAIL PROTECTED]>
> To: "Joshua D. Drake" <[EMAIL PROTECTED]>
> Cc: pgsql-general@postgresql.org
> Subject: Re: PG periodic Error on W2K
> Message-ID: <[EMAIL PROTECTED]>
> 
> 
> I propound to all my sincerest of apologies for installing what I 
> believe to be a marvel of human creation in Postgres on what most 
> believe to be the ultimate travesty of what some claim to be an 
> operating system in Macrohard Webloze.
>
>> [text excluded here]
> 
> I would be delighted to offer my assurance that such a farce would never 

> again take place, but I have about forty such installations to do when 
> my development is complete :(
> 
> Paul Lambert

Hi Paul,

Have you thought of running the Postgresql on a Linux box and then 
developing your application to use the Postgresql server via ODBC?

I am in a Windows environment on a Windows / Novell network. We still use 
Novell for log-on and security management. I develop in Visual FoxPro 
because the IT head wants to keep us on Windows clients but he agreed to 
let me try developing with a Linux server. I have been working with 
Postgresql on a Redhat 9 server for a couple years now and it is much more 
stable (and more secure) than our SQLServer databases on a Windows 2000 
server. 

*** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** 
*** ***
Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297

Re: [GENERAL] get username of user calling function?

[David Legault]

On 3/1/07, George Nychis <[EMAIL PROTECTED]> wrote:




A. Kretschmer wrote:
> You can use the current_user - variable. Select current_user;

I'm trying to create a function in which users can only kill their own
processes, it works
perfectly if i hardcode a username in such as this:
CREATE FUNCTION kill_process(integer) RETURNS boolean
AS 'select pg_cancel_backend(procpid)
 FROM (SELECT procpid FROM pg_stat_activity WHERE procpid=$1 and
usename=''gnychis'')
AS kill;'
LANGUAGE SQL SECURITY DEFINER;




See the EXECUTE function in the pl/pgSQL language in the docs for dynamic
queries.


But if i try to replace usename=''gnychis'' with usename=current_user it no

longer works.

Any ideas?

- George

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] supporting 2000 simultaneous connections.

[Bill Moran]

In response to "Joshua D. Drake" <[EMAIL PROTECTED]>:

> Bill Moran wrote:
> > In response to Shiva Sarna <[EMAIL PROTECTED]>:
> > 
> >> Hi,
> >>
> >> I am working on a web application where the front end is struts framework
> >> and back end is PgSQL 7.4.
> 
> *cough*, you are going to greatly decrease your ability to scale if you
> are running anything less than 8.1.
> > 
> > Performance _will_ degrade if all of those connections are busy at once, but
> > that's going to happen with any shared system.  The disk can only read from
> > one area at a time, and other system resources will be contended for as 
> > well.
> 
> 7.4 doesn't scale to what he wants, even on big hardware.

Oops ... didn't notice that.

-- 
Bill Moran
Collaborative Fusion Inc.

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] get username of user calling function?

[George Nychis]

David Legault wrote:



See the EXECUTE function in the pl/pgSQL language in the docs for dynamic
queries.

So it turns out that in a SECURITY DEFINER the current_user is the owner of the function. 
 I had to use session_user and it works now :)


- George

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] /libexec/ld-elf.so.1: Shared object "libpq.so.4" not found

[Palle Girgensohn]

postgresql-8-2 installs libpq.so.5. You need to upgrade pgadmin to  
link with libpq.so.5, or you need to downgrade postgresql to 8.1.x.  
Once you downgraded postgres, you can upgrade it again using  
portupgrade; it will save a copy of libpq.so.4 in /usr/local/lib/ 
compat/pkg/, so as long as you have that path in you ldconfig path  
(you should, unless you've modified som settings), it will work  
without upgrading pgadmin.


Good luck!

/Palle


28 feb 2007 kl. 19.04 skrev Dino Vliet:


Hi folks,

when I start pgadmin3 on my system I get the following error:

/libexec/ld-elf.so.1: Shared object "libpq.so.4" not found,  
required by "pgadmin3"


The only thing I have done lately is upgrade the database to 8.2 in  
the freebsd ports system. Pgadmin did work in the past, so what   
could be the problem? I have a amd64 system running freebsd version  
6.1.


I haven't had the time to check out the postgresql database I had  
backed up.

Will I experience problems because of this?

Hope somebody can help me out,

brgds


Need a quick answer? Get one in minutes from people who know. Ask  
your question on Yahoo! Answers.



---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] cast bytea to text

[Willy-Bas Loos]

yep, sry it took me a while to answer. It works now.
here´s my code:
-- start code --
create or replace function bytea2text(bytea) returns text as
$$
select ENCODE($1, 'escape');
$$
language sql strict;

create cast (bytea as text)
   with function bytea2text(bytea)
   as implicit;
-- end code --

thanks!

Willy-Bas Loos

On 3/1/07, Albe Laurenz <[EMAIL PROTECTED]> wrote:


> How can i cast bytea to text?
> I´ve read about the DECODE function, but my 8.1 backend
> doesn´t recognize it.
> I´m trying to create an implicit cast using the function:
>
> create or replace function bytea2text(bytea) returns text as
> $$
> select DECODE($1, 'escape');
> $$
> language sql strict;
>
> Answer:
> ERROR: function decode(bytea, "unknown") does not exist

Try ENCODE instead of DECODE ...

Yours,
Laurenz Albe

Re: [GENERAL] get username of user calling function?

[David Legault]

On 3/1/07, George Nychis <[EMAIL PROTECTED]> wrote:




David Legault wrote:
>
>
> See the EXECUTE function in the pl/pgSQL language in the docs for
dynamic
> queries.
>
So it turns out that in a SECURITY DEFINER the current_user is the owner
of the function.
  I had to use session_user and it works now :)



yes because you are running it in the context of the owner of the function

there is also another SECURITY setting that will be in the context of the
caller where current_user() should return the callee.



- George

Re: [GENERAL]

[Josh Berkus]

Radovan,

> I am interesting if you are planning or would like to incorporate
> pg_bulkload external function(s) into Postgresql 8.3 code. pg_bulkload
> is function which surpase COPY command in bulk data loading about 3
> times.

The correct place to propose this is on pgsql-hackers.  The authors of 
pg_bulkload have not proposed it for core inclusion.

-- 
--Josh Berkus

Josh Berkus
PostgreSQL Project Core Team
www.postgresql.org

(all opinions expressed are my own; I do not speak
 for the Project unless specifically noted.)

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

[GENERAL] PostgreSQL primary (sequence) key issue (Ruby/Rails)

[Andrew Madu]

Hi Guys,
I'm completely stumped with this one!

I've included ActiveRecord::Base.set_sequence_name in my environment.rb, and
in my user.rb I've placed the following code:

class Usertbl < ActiveRecord::Base
   set_primary_key "user_id"
   set_sequence_name "seq_user_mytable"
   validates_uniqueness_of :userpassword, :username, :scope => :user_id
end


From http://localhost:3000/usertbl/new I then attempt to enter a new a

record and receive the following error message:


RuntimeError: ERROR C23502  Mnull value in column "user_id" violates
not-null constraint

FexecMain.c L1795   RExecConstraints: INSERT INTO usertbl



Oh my head hurts!!

--
Regards

Andrew


On 01/03/07, Andrew Madu < [EMAIL PROTECTED]> wrote:


Hi Dave,
my apologies for contacting you off list but i'm having a spot of bother
with postgreSQL sequence setup in rails. In addition to what is mentioned
below, I have place the following line of code in my environment.rbdocument:

# Include your application configuration below
ActiveRecord::Base.pluralize_table_names = false
ActiveRecord::Base.set_sequence_name

What I am overlooking here?

--
Regards

Andrew

-- Forwarded message --
From: Andrew Madu < [EMAIL PROTECTED]>
Date: 01-Mar-2007 15:28
Subject: Re: PostgreSQL primary (sequence) key issue
To: [EMAIL PROTECTED]

Hi,
I've followed the example given here:

http://ar.rubyonrails.com/classes/ActiveRecord/Base.html#M000367

and implemented set_sequence_name "seq_name" in my class. When I try to
update the user table with a new row from:

http://localhost:3000/usertbl/new

I get the following error message:

RuntimeError: ERROR C23502  Mnull value in column "user_id" violates 
not-null constraint  


FexecMain.c L1795   RExecConstraints: INSERT INTO usertbl


What am I overlooking here?

Also coud possibly give me any clues on how to setup an 'assigned' primary
key in an ActiveRecord class?

--
Regards

Andrew

On 01/03/07, Andrew Madu <[EMAIL PROTECTED]> wrote:
>
> Hi,
> I have a sequence set up on one of my tables called seq_user_mytable. I
> tried the following with no joy:
>
> set_primary_key "user_id", :sequence => "seq_user_mytable" and:
>
> ActiveRecord:: Base.seq_user_mytable
>
> again with no joy. How do I implement a sequence in Rails/ActiveRecord?
>
> --
> Regards
>
> Andrew
>

Re: [GENERAL] PostgreSQL primary (sequence) key issue (Ruby/Rails)

[Joshua D. Drake]

> On 01/03/07, Andrew Madu < [EMAIL PROTECTED]> wrote:
>>
>> Hi Dave,
>> my apologies for contacting you off list but i'm having a spot of bother
>> with postgreSQL sequence setup in rails. In addition to what is mentioned
>> below, I have place the following line of code in my

The definition of primary key explicitly states that it can't be null.
You are trying to pass a null to user_id which won't work.

Joshua D. Drkae


>> environment.rbdocument:
>>
>> # Include your application configuration below
>> ActiveRecord::Base.pluralize_table_names = false
>> ActiveRecord::Base.set_sequence_name
>>
>> What I am overlooking here?
>>
>> -- 
>> Regards
>>
>> Andrew
>>
>> -- Forwarded message --
>> From: Andrew Madu < [EMAIL PROTECTED]>
>> Date: 01-Mar-2007 15:28
>> Subject: Re: PostgreSQL primary (sequence) key issue
>> To: [EMAIL PROTECTED]
>>
>> Hi,
>> I've followed the example given here:
>>
>> http://ar.rubyonrails.com/classes/ActiveRecord/Base.html#M000367
>>
>> and implemented set_sequence_name "seq_name" in my class. When I try to
>> update the user table with a new row from:
>>
>> http://localhost:3000/usertbl/new
>>
>> I get the following error message:
>>
>> RuntimeError: ERRORC23502Mnull value in column "user_id"
>> violates not-null constraint   
>>
>>
>> FexecMain.cL1795RExecConstraints: INSERT INTO usertbl
>>
>>
>> What am I overlooking here?
>>
>> Also coud possibly give me any clues on how to setup an 'assigned'
>> primary
>> key in an ActiveRecord class?
>>
>> -- 
>> Regards
>>
>> Andrew
>>
>> On 01/03/07, Andrew Madu <[EMAIL PROTECTED]> wrote:
>> >
>> > Hi,
>> > I have a sequence set up on one of my tables called seq_user_mytable. I
>> > tried the following with no joy:
>> >
>> > set_primary_key "user_id", :sequence => "seq_user_mytable" and:
>> >
>> > ActiveRecord:: Base.seq_user_mytable
>> >
>> > again with no joy. How do I implement a sequence in Rails/ActiveRecord?
>> >
>> > --
>> > Regards
>> >
>> > Andrew
>> >
>>
>>
> 


-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] PostgreSQL primary (sequence) key issue (Ruby/Rails)

[Russ Brown]

Joshua D. Drake wrote:

On 01/03/07, Andrew Madu < [EMAIL PROTECTED]> wrote:

Hi Dave,
my apologies for contacting you off list but i'm having a spot of bother
with postgreSQL sequence setup in rails. In addition to what is mentioned
below, I have place the following line of code in my


The definition of primary key explicitly states that it can't be null.
You are trying to pass a null to user_id which won't work.

Joshua D. Drkae



In MySQL that is traditionally how you tell the RDBMS to use the 
auto_increment to generate the value. Postgres correctly doesn't allow 
that (since you might actually try to set a field to NULL accidentally 
in which case an error is expected).


The portable (and correct) way to do it is to use the DEFAULT keyword 
like this:


INSERT INTO some_table (id_field) VALUES (DEFAULT);

I just tested on MySQL 5.0.32 and that syntax works fine.

--

Russ.

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] hardware question - LSI MegaRaid 8480e

[Alan Hodgson]

Does anyone have anything specifically good or bad to say about the LSI 
MegaRaid 8480e, in particular RAID-10 performance, and performance and 
stability under Linux or any problems with the battery-backed cache option?

I'm building a new database server and planning to hook one of these up to 
an SAS JBOD enclosure for most of the storage.  It will be running Linux 
x86_84 and any arrays will be RAID-10.

-- 
Eat right. Exercise regularly. Die anyway.


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] who uses tsearch2

[John Smith]

guys,
need to pitch tsearch2+postgresql- need some big names who use ts2?
jzs

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Constructors for dates, times, and timestamps

[Andrew T. Robinson]

Thanks for the pointers.  I did figure out

   '-mm-dd'::date
   'hh:mm:ss'::time

And I also came up with

   (date_column::text || ' ' || time_column::text)::timestamp

Which is too ugly for words.  I appreciate the more elegant solutions 
posted to the list.


I'm growing to like Postgres, but I'm not sure I'll ever get over DB/2 :-)

Andy
begin:vcard
fn:Andrew T. Robinson, CISM, CISSP
n:Robinson;Andrew
org:NMI InfoSecurity Solutions
adr:;;145 Newbury Street Second Floor;Portland;ME;04101;USA
email;internet:[EMAIL PROTECTED]
title:President
tel;work:207-780-6381 x226
tel;fax:207-780-6301
x-mozilla-html:TRUE
url:http://www.nmi.net
version:2.1
end:vcard


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

[GENERAL] Connections over SSH tunnels closing after idle time

[Jerry Sievers]

Curious if others have run into this; 

Client side is Ubuntu Linux, server side is Solaris 10/ SPARC.  Pg
version is 8.1.5.

Connecting to some machines requires tunneling through another host
that sits on 2 network interfaces.

ssh -n -N -N port:remote.host:5432 tunnelhost &

Connections  work as expected but die after an idle period of an hour
or so.  I see the socket on local side in TIME_WAIT state. 

Doesn't seem like Postgres on Solaris will allow setting the
tcp_keepalive parameters.  They are all set to zero presently and
cause error if a change is attempted.

Did try various combinations of the SSH TCP keepalive and ServerAlive
settings.  

Any advice how to solve or better drill down to the exact problem is
much appreciated. 

Thanks

-- 
---
Jerry Sievers   732 365-2844 (work) Production Database Administrator
305 321-1144 (mobil WWW E-Commerce Consultant

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] hardware question - LSI MegaRaid 8480e

[Merlin Moncure]

On 3/1/07, Alan Hodgson <[EMAIL PROTECTED]> wrote:

Does anyone have anything specifically good or bad to say about the LSI
MegaRaid 8480e, in particular RAID-10 performance, and performance and
stability under Linux or any problems with the battery-backed cache option?

I'm building a new database server and planning to hook one of these up to
an SAS JBOD enclosure for most of the storage.  It will be running Linux
x86_84 and any arrays will be RAID-10.


I was planning on doing exactly the same...would be very curious what
you come up with.

merlin

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] who uses tsearch2

[Magnus Hagander]

John Smith wrote:
> guys,
> need to pitch tsearch2+postgresql- need some big names who use ts2?

http://search.postgresql.org, about 600,000 webpages and emails in the
archives.

//Magnus

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] PG periodic Error on W2K

[Paul Lambert]

[EMAIL PROTECTED] wrote:


 > Date: Thu, 01 Mar 2007 10:06:44 +0900
 > From: Paul Lambert <[EMAIL PROTECTED]>
 > To: "Joshua D. Drake" <[EMAIL PROTECTED]>
 > Cc: pgsql-general@postgresql.org
 > Subject: Re: PG periodic Error on W2K
 > Message-ID: <[EMAIL PROTECTED]>
 >
 >
 > I propound to all my sincerest of apologies for installing what I
 > believe to be a marvel of human creation in Postgres on what most
 > believe to be the ultimate travesty of what some claim to be an
 > operating system in Macrohard Webloze.
 >
 >> [text excluded here]
 >
 > I would be delighted to offer my assurance that such a farce would never
 > again take place, but I have about forty such installations to do when
 > my development is complete :(
 >
 > Paul Lambert

Hi Paul,

Have you thought of running the Postgresql on a Linux box and then 
developing your application to use the Postgresql server via ODBC?


I am in a Windows environment on a Windows / Novell network. We still 
use Novell for log-on and security management. I develop in Visual 
FoxPro because the IT head wants to keep us on Windows clients but he 
agreed to let me try developing with a Linux server. I have been working 
with Postgresql on a Redhat 9 server for a couple years now and it is 
much more stable (and more secure) than our SQLServer databases on a 
Windows 2000 server.


*** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** 
*** ***

Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297


I've put thought of running it on Linux... a lot of thought... but I 
haven't for 2 reasons.


1.) I've had very limited exposure to Linux, most of my work is done in 
an OpenVMS environment with some in Weendoze, I wouldn't feel 
comfortable configuring a Linux environment let alone configuring it 
properly.


2.) All of our customers run Weenblows servers and also don't have the 
expertise to maintain a linux server, it's a lot easier for us to sell 
our product to them if we can just install it on their existing server 
and have them not worry about upgrading or adding anything to their 
server room.


Can I put in another plug for an OpenVMS port? Or would I have to do the 
work myself? ;)


--
Paul Lambert
Database Administrator
AutoLedgers


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] strange behaviour with sub-select and pl/pgSQL

[Tom Lane]

Robert Partyka <[EMAIL PROTECTED]> writes:
> http://www.bobson.pl/pgsql/pgsql_sb1.html

8.2 won't flatten sub-selects that contain volatile functions in their
targetlists...

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend

[GENERAL] is pg_standby over nfs expected to work?

[Merlin Moncure]

I have been experimenting with pg_standby with the primay copying
files to a nfs folder mounted from a special server on the standby.
Inevitably, the following happens:

LOG:  restored log file "000100E7" from archive
LOG:  restored log file "000100E8" from archive
LOG:  restored log file "000100E9" from archive
LOG:  could not fsync segment 0 of relation 1663/21119/2673: No such
file or dir  ectory
CONTEXT:  xlog redo checkpoint: redo 0/E9047EC8; undo 0/0; tli 1; xid
0/20369249  ; oid 24584; multi 1; offset 0; online
FATAL:  storage sync failed on magnetic disk: No such file or directory
CONTEXT:  xlog redo checkpoint: redo 0/E9047EC8; undo 0/0; tli 1; xid
0/20369249  ; oid 24584; multi 1; offset 0; online
LOG:  startup process (PID 30031) exited with exit code 1
LOG:  aborting startup due to startup process failure
LOG:  logger shutting down

I'm thinking one of two things is wrong, either I'm not mounting the
nfs folder in a suitable way to do this, or I have to revert to
shipping wal files with nc.  anybody have any ideas?

merlin

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Assistance with Query Optimisation?

[Shaun Johnston]

Isak Hansen wrote:

First thing I notice is that your query plans seem to only use one index.

Postgres should be able to combine the timestamp and account_id
indexes in that first query, if the optimizer thought there was a
point in doing so?

Absolutely brilliant - this solved my issue, brought the query time down 
to about 2 seconds initially, then about 500 milliseconds thereafter.  
Thank you very much for this tip.



---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

[GENERAL] rpm containing pgdump

[Frank Church]

Which of the postgresql rpms contains pgdump. I have downloaded
postgresql-server and postgresql-libs and pgdump is not included.
Which rpm contains it?

/frank

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] supporting 2000 simultaneous connections.

[Mariano Mara]

Shiva Sarna escribió:

Hi,

I am working on a web application where the front end is struts 
framework and back end is PgSQL 7.4.


The client want us to support 2000 simultaneous users. My question is 
will there be any performance degradation if I increase the 
max_connections of pgsql to 2000.


Is there a limit to maximum number of simultaneous user supoorted by 
pgsql.


thanks for your time and help.

regards

Shiva


Here's a new way to find what you're looking for - Yahoo! Answers 
 
You can test it. Use JMeter (http://jakarta.apache.org/jmeter/) and 
stress it to know where the limit is (according to your hardware, of 
course).

Re: [GENERAL] rpm containing pgdump

[Jorge Godoy]

"Frank Church" <[EMAIL PROTECTED]> writes:

> Which of the postgresql rpms contains pgdump. I have downloaded
> postgresql-server and postgresql-libs and pgdump is not included.
> Which rpm contains it?

On my SuSE box it is the 'postgresql' RPM. 

-- 
Jorge Godoy  <[EMAIL PROTECTED]>

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] Fun with Cursors- how to rewind a cursor

[Postgres User]

Hi,
I'm opening a refcursor in Postgres to return a dataset to the client.
However, before returning the cursor, I'd like to iterate thru the
rows.  Here's the code:

 DECLARE
ref_entry  refcursor;
rec  record;
i  integer = 0;
v_list varchar = '';

 BEGIN
OPEN ref_entry FOR
SELECT * FROM big_select_statement;

LOOP
  FETCH ref_entry INTO rec;
  EXIT WHEN NOT FOUND;

  i = i + 1;
  IF v_list != '' THEN
 v_list = v_list || ', ';
  END IF;
  v_list = v_list || rec.entry_id::varchar;

END LOOP;

Return next ref_entry;

 END;

There's one slight and obvious problem- the cursor returns nothing to
the client because I've already fetched all the rows.  (Remove the
LOOP, and the cursor returns all rows as expected).

Is there any way to 'rewind' the cursor to the first row?  I realize
that I can simply execute the full query and open another cursor to
return to the client, but I'll take a performance hit that I'd like to
avoid.

I've tried a few FETCH BACKWARD and other statements but only get
syntax errors returned by the comiler.  Anyone done this before?

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

[GENERAL] Constraint validation

[Demian Lessa]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hi,

After browsing the source, and running some experiments, it seems like
PostgreSQL blindly verifies all CHECK constraints for an update, even if
the update COULDN'T possibly be violated by the specified update (for
instance

  UPDATE table SET field3=value WHERE condition

and there is no CHECK constraint on field3. I compared my observations
with what I saw by running the same experiments in Oracle, and they seem
to verify constraints selectively.

Two questions: is there a reason for this behavior in PostgreSQL? and,
does this also hold for other constraints such as PRIMARY KEYs, UNIQUEs,
and FOREIGN KEYs?

Thanks,

Demian
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.2 (GNU/Linux)
Comment: Using GnuPG with SUSE - http://enigmail.mozdev.org

iD8DBQFF53LXWs7G5iIp9akRAiQHAJ4nxqKt/gH2SVqDYxS+8bcTpHIt6gCeL/HH
ZhpTHmGPQ9qaylyBZiBaBw4=
=pnf6
-END PGP SIGNATURE-

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Fun with Cursors- how to rewind a cursor

[Tom Lane]

"Postgres User" <[EMAIL PROTECTED]> writes:
> Is there any way to 'rewind' the cursor to the first row?

plpgsql doesn't have any command for that (though I think someone is
working on improving its cursor command set).  You should be able to
work around it by EXECUTE'ing a MOVE BACKWARD ALL command, though.
You just need to know the real name of the cursor --- see 'Returning
Cursors' in the plpgsql docs for discussion.

regards, tom lane

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] Constraint validation

[Tom Lane]

Demian Lessa <[EMAIL PROTECTED]> writes:
> After browsing the source, and running some experiments, it seems like
> PostgreSQL blindly verifies all CHECK constraints for an update, even if
> the update COULDN'T possibly be violated by the specified update (for
> instance
>   UPDATE table SET field3=value WHERE condition

We keep hearing people propose that we optimize on the assumption that
an UPDATE "can't change fields it doesn't assign to".  This falls down
on the fact that a BEFORE UPDATE trigger can change the row arbitrarily.

Possibly we could do something in cases where there isn't any trigger or
it doesn't replace the row; but it would have to be a decision taken at
runtime in the guts of the executor, which considerably limits the scope
of what can be done.

In any case I'm not at all excited about trying to detect which fields a
CHECK is based on --- for typical simple check conditions it's probably
cheaper to just do the check.  If you have an expensive condition you
might consider enforcing it in a trigger, which can test for itself
whether the relevant fields have changed.

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[GENERAL] Thanks to all

[Paul Lambert]

Thanks to all who have helped me over the last month or so with 
converting my system from M$ SQL server to Postgres.


Unfortunately I've decided to scrap the project and continue working 
with M$ SQL Server... PG just isn't doing what I want.






No... I jest, I've finally got the entire system up and running exactly 
as I want it and I am ready to do my first customer install. That's 
another 40 or 50 PG installs over the next few months :D


A big thankyou to everyone who has given me advise up to now... and a 
big thanks in advance to those who will continue to advise me in the 
future ;)


Regards,
Paul.

--
Paul Lambert
Database Administrator
AutoLedgers


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] Thanks to all

[Terry Fielder]

You may encounter many things in life which you may regret having done.

Converting from M$ SQL to PostgreSQL is probably *not* one of them.

Terry

Terry Fielder
[EMAIL PROTECTED]
Associate Director Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
Fax: (416) 441-9085



Paul Lambert wrote:
Thanks to all who have helped me over the last month or so with 
converting my system from M$ SQL server to Postgres.


Unfortunately I've decided to scrap the project and continue working 
with M$ SQL Server... PG just isn't doing what I want.






No... I jest, I've finally got the entire system up and running 
exactly as I want it and I am ready to do my first customer install. 
That's another 40 or 50 PG installs over the next few months :D


A big thankyou to everyone who has given me advise up to now... and a 
big thanks in advance to those who will continue to advise me in the 
future ;)


Regards,
Paul.



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] Add items to a record variable

[eddy sanchez]

WEBMAIL Server: UDABOLnet, Universidad de Aquino Bolivia


Can anyone help me???

I work with plpgsql and I need to add items to a record variable, with a for
statement, something like this:

declare
   v_rec record;
begin
for nn in (some_xpresion) loop
 

v_rec = vrec + [new_item]; <--Here I need to add an item to record variable with
   each loop 
end loop lp;

return v_rec;
end;


The result should be like {it1,it2,it3,i4,}

Please can you help me?
Thanks a lot

-
Este email se envio mediante el servidor WEBMAIL de UDABOLnet
-

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

[GENERAL] SOLVED: Re: PostgreSQL primary (sequence) key issue (Ruby/Rails)

[Andrew Madu]

Hi,
this one has been finally put to bed!!

--
Regards

Andrew

On 01/03/07, Andrew Madu <[EMAIL PROTECTED]> wrote:


Hi Guys,
I'm completely stumped with this one!

I've included ActiveRecord::Base.set_sequence_name in my environment.rb,
and in my user.rb I've placed the following code:

class Usertbl < ActiveRecord::Base
set_primary_key "user_id"
set_sequence_name "seq_user_mytable"
validates_uniqueness_of :userpassword, :username, :scope => :user_id
end

From http://localhost:3000/usertbl/new I then attempt to enter a new a
record and receive the following error message:


RuntimeError: ERROR C23502  Mnull value in column "user_id" violates 
not-null constraint  


FexecMain.c L1795   RExecConstraints: INSERT INTO usertbl



Oh my head hurts!!

--
Regards

Andrew


On 01/03/07, Andrew Madu < [EMAIL PROTECTED]> wrote:
>
> Hi Dave,
> my apologies for contacting you off list but i'm having a spot of bother
> with postgreSQL sequence setup in rails. In addition to what is mentioned
> below, I have place the following line of code in my environment.rbdocument:
>
> # Include your application configuration below
> ActiveRecord::Base.pluralize_table_names = false
> ActiveRecord::Base.set_sequence_name
>
> What I am overlooking here?
>
> --
> Regards
>
> Andrew
>
> -- Forwarded message --
> From: Andrew Madu < [EMAIL PROTECTED]>
> Date: 01-Mar-2007 15:28
> Subject: Re: PostgreSQL primary (sequence) key issue
> To: [EMAIL PROTECTED]
>
> Hi,
> I've followed the example given here:
>
> http://ar.rubyonrails.com/classes/ActiveRecord/Base.html#M000367
>
> and implemented set_sequence_name "seq_name" in my class. When I try to
> update the user table with a new row from:
>
> http://localhost:3000/usertbl/new
>
> I get the following error message:
>
> RuntimeError: ERRORC23502  Mnull value in column "user_id" violates 
not-null constraint  
>
>
>
> FexecMain.cL1795   RExecConstraints: INSERT INTO usertbl
>
>
> What am I overlooking here?
>
> Also coud possibly give me any clues on how to setup an 'assigned'
> primary key in an ActiveRecord class?
>
> --
> Regards
>
> Andrew
>
> On 01/03/07, Andrew Madu <[EMAIL PROTECTED]> wrote:
> >
> > Hi,
> > I have a sequence set up on one of my tables called seq_user_mytable.
> > I tried the following with no joy:
> >
> > set_primary_key "user_id", :sequence => "seq_user_mytable" and:
> >
> > ActiveRecord:: Base.seq_user_mytable
> >
> > again with no joy. How do I implement a sequence in
> > Rails/ActiveRecord?
> >
> > --
> > Regards
> >
> > Andrew
> >
>
>

Re: [GENERAL] Fun with Cursors- how to rewind a cursor

[Postgres User]

Thanks for the pointer.  According to the Postgres docs:

"The portal name used for a cursor can be specified by the programmer
or automatically generated. To specify a portal name, simply assign a
string to the refcursor variable before opening it."

So I added the following code:

 ref_entry = 'c_entry';
  MOVE Backward All In c_entry;

Which compiles- progress!  But when I try to execute it, this error is returned:

SPI_execute_plan failed executing query "MOVE Backward All In
c_entry": SPI_ERROR_CURSOR

No luck finding any info on this error, except for a few bug reports.
Any ideas?  I've pasted my complete code below:


DECLARE
   ref_entry refcursor;

   rec record;
   v_list varchar = '';
   i integer = 0;

BEGIN

-- assign name to cursor
ref_entry = 'c_entry';

OPEN ref_entry FOR
SELECT * FROM big_select_statement

LOOP
  FETCH ref_entry INTO rec;
  EXIT WHEN NOT FOUND;

  i = i + 1;
  IF v_list != '' THEN
 v_list = v_list || ', ';
  END IF;
  v_list = v_list || rec.entry_id::varchar;

END LOOP;
MOVE Backward All In c_entry;

END;

On 3/1/07, Tom Lane <[EMAIL PROTECTED]> wrote:

"Postgres User" <[EMAIL PROTECTED]> writes:
> Is there any way to 'rewind' the cursor to the first row?

plpgsql doesn't have any command for that (though I think someone is
working on improving its cursor command set).  You should be able to
work around it by EXECUTE'ing a MOVE BACKWARD ALL command, though.
You just need to know the real name of the cursor --- see 'Returning
Cursors' in the plpgsql docs for discussion.

   regards, tom lane



---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Add items to a record variable

[Postgres User]

If you need to return a record to another function or client program,
you can always use this sytnax:

SELECT var_a::integer, var_b::integer, var_c::integer... etc

where var_a, var_b, etc and local variables that hold your calculated values.

You dont need to create a local record structure- select will do that
for you.  The '::integer' will ensure that the values are typecast as
integers.


On 3/1/07, eddy sanchez <[EMAIL PROTECTED]> wrote:

WEBMAIL Server: UDABOLnet, Universidad de Aquino Bolivia

Thank you for you help

The item is a field, I need to return a record with double precision or integer
fields.

I need a function that can calculate some fields (integer numbers) that should
be added to a record inside a loop, and the result, must seems like this:

{234.00, 56434.78, 5556.89,}

Always, I call those functions with:

select * from func_with_record() as (field1 integer, field2 integer, field3
integer,)

Do you undestand?

Thanks a lot for your interesting.



---

What is the new item?  Is it a field?

On 3/1/07, eddy sanchez <[EMAIL PROTECTED]> wrote:
WEBMAIL Server: UDABOLnet, Universidad de Aquino Bolivia


Can anyone help me???

I work with plpgsql and I need to add items to a record variable, with a for
statement, something like this:

declare
 v_rec record;
begin
for nn in (some_xpresion) loop
 

v_rec = vrec + [new_item]; <--Here I need to add an item to record variable with
 each loop
end loop lp;

return v_rec;
end;


The result should be like {it1,it2,it3,i4,}

Please can you help me?
Thanks a lot

-
Este email se envio mediante el servidor WEBMAIL de UDABOLnet
-

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly



-
Este email se envio mediante el servidor WEBMAIL de UDABOLnet
-



---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] Add items to a record variable

[Eddy D. Sanchez]

Thanks.

But I need to add fields to a defined and filled record, I don't know  
if I can do it, and how can I do it.


If I have:

v_record record;

EXECUTE 'select * from table'
INTO v_record;

supposing (after query) that v_record contains just one row with 5  
fields, I need to add the 6th, 7th, 8th fields, etc, etc.


Do you understand ?



On Mar 2, 2007, at 12:48 AM, Postgres User wrote:


If you need to return a record to another function or client program,
you can always use this sytnax:

SELECT var_a::integer, var_b::integer, var_c::integer... etc

where var_a, var_b, etc and local variables that hold your  
calculated values.


You dont need to create a local record structure- select will do that
for you.  The '::integer' will ensure that the values are typecast as
integers.


On 3/1/07, eddy sanchez <[EMAIL PROTECTED]> wrote:

WEBMAIL Server: UDABOLnet, Universidad de Aquino Bolivia

Thank you for you help

The item is a field, I need to return a record with double  
precision or integer

fields.

I need a function that can calculate some fields (integer numbers)  
that should
be added to a record inside a loop, and the result, must seems  
like this:


{234.00, 56434.78, 5556.89,}

Always, I call those functions with:

select * from func_with_record() as (field1 integer, field2  
integer, field3

integer,)

Do you undestand?

Thanks a lot for your interesting.



---

What is the new item?  Is it a field?

On 3/1/07, eddy sanchez <[EMAIL PROTECTED]> wrote:
WEBMAIL Server: UDABOLnet, Universidad de Aquino Bolivia


Can anyone help me???

I work with plpgsql and I need to add items to a record variable,  
with a for

statement, something like this:

declare
 v_rec record;
begin
for nn in (some_xpresion) loop
 

v_rec = vrec + [new_item]; <--Here I need to add an item to record  
variable with

 each loop
end loop lp;

return v_rec;
end;


The result should be like {it1,it2,it3,i4,}

Please can you help me?
Thanks a lot

-
Este email se envio mediante el servidor WEBMAIL de UDABOLnet
-

---(end of  
broadcast)---

TIP 1: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that  
your

 message can get through to the mailing list cleanly



-
Este email se envio mediante el servidor WEBMAIL de UDABOLnet
-




---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Fun with Cursors- how to rewind a cursor

[Tom Lane]

"Postgres User" <[EMAIL PROTECTED]> writes:
> So I added the following code:

>  ref_entry = 'c_entry';
>   MOVE Backward All In c_entry;

You have to use EXECUTE for the latter.

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Fun with Cursors- how to rewind a cursor

[Postgres User]

I had tried several variations of MOVE Backward inside an Execute
statement earlier.  And now, I'm seeing this error appear again:

ERROR: 0A000: cannot manipulate cursors directly in PL/pgSQL

I updated the Postgres function below with this replacement line:
Execute 'MOVE Backward All In c_entry';

Any other thoughts?


On 3/1/07, Tom Lane <[EMAIL PROTECTED]> wrote:

"Postgres User" <[EMAIL PROTECTED]> writes:
> So I added the following code:

>  ref_entry = 'c_entry';
>   MOVE Backward All In c_entry;

You have to use EXECUTE for the latter.

   regards, tom lane



---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] How often do I need to reindex tables?

[Tom Lane]

Bill Moran <[EMAIL PROTECTED]> writes:
> Just an FYI ... I remembered what prompted the cron job.

> We were seeing significant performance degradation.  I never did actual
> measurements, but it was on the order of "Bill, why is restoring taking
> such a long time?" from other systems people.  At the time, I poked around
> and tried some stuff here and there and found that reindex restored
> performance.  I didn't look at actual size at that time.

A reindex might improve performance for reasons other than bloat --- to
wit, that a freshly-built index is in perfect physical order, which
tends to get degraded over time by page splits.  How important that is
depends on your usage patterns.  If this is what the story is for your
situation, then what might fix it (in 8.2) is to create the index with
FILLFACTOR 50 or so, so that it's already at the steady state density
and won't need many page splits.

> Anyway, I'll report back in a few weeks as to what the numbers look like.

Yeah, please for the moment just watch what happens with the default
behavior.

regards, tom lane

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] Add items to a record variable

[Postgres User]

The first hack is probably a little easier.  Let me try and flesh it
out for you:

declare
   rec1   record;
   rec2   record;
   new_fields  varchar = '';

begin
  select fieldlist from mytable into rec1;
  -- inspect and play with your rec1 here
  -- now start a loop to add new fields

 if new_fields != '' then
new_fields = new_fields || ', '
 end if;

  new_fields = new_fields || new field value here || '::' || new
field type here;

  -- close loop here

  -- now select all rows into a new record variable

  execute 'select mt.fieldlist ' || new_fields || ' from mytable mt into rec2';

end;

This is a hack but it should work.  You'll end up with a single record
var with your old fields and new fields, typed as you have specified.


On 3/1/07, Eddy D. Sanchez <[EMAIL PROTECTED]> wrote:

Good idea, thanks a lot, I try it

On Mar 2, 2007, at 1:32 AM, Postgres User wrote:

> Ok, you can always SELECT into a temp table.  If you want to add
> additional fields in a loop, you can call an Execute 'Alter Table Add
> Column'
> to add all the fields you need.  Then call Update to insert values
> into the fields.
>
> Finally do a select into your record var.
>
> On 3/1/07, Eddy D. Sanchez <[EMAIL PROTECTED]> wrote:
>> Thanks.
>>
>> But I need to add fields to a defined and filled record, I don't know
>> if I can do it, and how can I do it.
>>
>> If I have:
>>
>> v_record record;
>>
>> EXECUTE 'select * from table'
>> INTO v_record;
>>
>> supposing (after query) that v_record contains just one row with 5
>> fields, I need to add the 6th, 7th, 8th fields, etc, etc.
>>
>> Do you understand ?
>>
>>
>>
>> On Mar 2, 2007, at 12:48 AM, Postgres User wrote:
>>
>> > If you need to return a record to another function or client
>> program,
>> > you can always use this sytnax:
>> >
>> > SELECT var_a::integer, var_b::integer, var_c::integer... etc
>> >
>> > where var_a, var_b, etc and local variables that hold your
>> > calculated values.
>> >
>> > You dont need to create a local record structure- select will do
>> that
>> > for you.  The '::integer' will ensure that the values are
>> typecast as
>> > integers.
>> >
>> >
>> > On 3/1/07, eddy sanchez <[EMAIL PROTECTED]> wrote:
>> >> WEBMAIL Server: UDABOLnet, Universidad de Aquino Bolivia
>> >>
>> >> Thank you for you help
>> >>
>> >> The item is a field, I need to return a record with double
>> >> precision or integer
>> >> fields.
>> >>
>> >> I need a function that can calculate some fields (integer numbers)
>> >> that should
>> >> be added to a record inside a loop, and the result, must seems
>> >> like this:
>> >>
>> >> {234.00, 56434.78, 5556.89,}
>> >>
>> >> Always, I call those functions with:
>> >>
>> >> select * from func_with_record() as (field1 integer, field2
>> >> integer, field3
>> >> integer,)
>> >>
>> >> Do you undestand?
>> >>
>> >> Thanks a lot for your interesting.
>> >>
>> >>
>> >>
>> >> ---
>> >>
>> >> What is the new item?  Is it a field?
>> >>
>> >> On 3/1/07, eddy sanchez <[EMAIL PROTECTED]> wrote:
>> >> WEBMAIL Server: UDABOLnet, Universidad de Aquino Bolivia
>> >>
>> >>
>> >> Can anyone help me???
>> >>
>> >> I work with plpgsql and I need to add items to a record variable,
>> >> with a for
>> >> statement, something like this:
>> >>
>> >> declare
>> >>  v_rec record;
>> >> begin
>> >> for nn in (some_xpresion) loop
>> >>  
>> >>
>> >> v_rec = vrec + [new_item]; <--Here I need to add an item to record
>> >> variable with
>> >>  each loop
>> >> end loop lp;
>> >>
>> >> return v_rec;
>> >> end;
>> >>
>> >>
>> >> The result should be like {it1,it2,it3,i4,}
>> >>
>> >> Please can you help me?
>> >> Thanks a lot
>> >>
>> >> -
>> >> Este email se envio mediante el servidor WEBMAIL de UDABOLnet
>> >> -
>> >>
>> >> ---(end of
>> >> broadcast)---
>> >> TIP 1: if posting/reading through Usenet, please send an
>> appropriate
>> >>  subscribe-nomail command to [EMAIL PROTECTED] so that
>> >> your
>> >>  message can get through to the mailing list cleanly
>> >>
>> >>
>> >>
>> >> -
>> >> Este email se envio mediante el servidor WEBMAIL de UDABOLnet
>> >> -
>> >>
>>
>>




---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Differences in identical queries

[Richard Huxton]

Rob Schall wrote:

Question for anyone...

I have to queries. One runs in about 2 seconds. The other takes upwards
of 2 minutes. I have a temp table that is created with 2 columns. This
table is joined with the larger database of call detail records.
However, these 2 queries are handled very differently.


1. They're different queries - the second is expecting 10 times as many 
rows as the first.
2. Can't tell if that is accurate - you need to supply EXPLAIN ANALYSE 
output instead of EXPLAIN, so we can see what actually happened.



calldetail=> EXPLAIN SELECT current.* FROM current JOIN anitmp ON
current.destnum=anitmp.ani AND istf=true;
  QUERY PLAN
--
 Nested Loop  (cost=0.00..2026113.09 rows=500908 width=108)



calldetail=> EXPLAIN SELECT current.* FROM current JOIN anitmp ON
current.orignum=anitmp.ani AND istf=false;
---
 Hash Join  (cost=35.99..3402035.53 rows=5381529 width=108)



--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 6: explain analyze is your friend