Re: [GENERAL] Problem restoring Database
On Thu, 2004-04-01 at 20:08, Joshua D. Drake wrote:
mike wrote:
I have just upgraded froom Fedora test1 to test2 which has upgraded
postgres version from 7.4.2 to 7.4.5 (frrom rpm -q)
Uhh unless I am missing something what you have done is impossible.
There is no 7.4.5
Are you sure it didn't upgrade from 7.3.2 to 7.3.5? Or WORSE:
7.3.2 to 7.4.2?
If you have your dump still, you should be in fine shape. What flags
did you pass to pg_dumpall?
Sincerely,
Joshua D. Drake
even more curious is that I have just done a new dump/restore using
pg_dump (ie: not dumpall) and eveything worked fine
I have used pg_dumpall to dump my database, the restore appeared to go
OK, except there is no data in two of my tables (only these two)
output from \d \d tb_invoice_header
Table
public.tb_invoice_header
Column | Type
| Modifiers
+---+---
inv_id | integer | not null default
nextval('public.tb_invoice_header_inv_id_seq'::text)
client_id | integer |
payable_recievable | character varying(15) |
amount | money |
amount_paid| money |
date_paid | date |
cheque | character varying(20) |
Indexes:
ih_pk primary key, btree (inv_id)
\d tb_invoice_detail
Table public.tb_invoice_detail
Column| Type |
Modifiers
-++---
id | integer| not null default
nextval('public.tb_invoice_detail_id_seq'::text)
invoice_id | integer|
code| character varying(15) |
description | character varying(100) |
amount | money |
vat_code| smallint |
line_total | money |
Indexes:
tb_id_pk primary key, btree (id)
Foreign-key constraints:
$1 FOREIGN KEY (invoice_id) REFERENCES tb_invoice_header(inv_id)
The dump file contains all the data
Anyone have any idea what is happening, and more important how to rescue
thanks
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Optimize query
Hello, I have one main table and 2 many-to-many relation tables and 2 one-to-many relation table. Main table has 150 000 rows and many-to-many tables have about 300 000 rows. I am creating web portal. In my query I must call distinct with many-to-many relation. But this query is too slow for web application. This query costs 8,5 seconds. For example: SELECT distinct(i_part_id), i_is_special, a.v_name, d.v_company, f_price, t_description FROM v_part a JOIN l_model_to_part b USING (i_part_id) JOIN l_part_to_cat c USING (i_part_id) JOIN k_seller d USING (i_seller_id) JOIN c_part_cat e USING (i_part_cat_id) WHERE TRUE AND condition ORDER BY i_is_special DESC, v_name; So, I created another way, how to grow up performace. I created temporary table with result of query, which has only one column (id of product). And finally I do a join of temporary table with main table. This query costs 0,5 seconds with everything. It is 8,5 seconds versus 0,5 seconds... Is this the right way, how to increase the performance of the queries??? I know, that every problem is specific. I mean this philosophical. thanx, miso ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Evening in NYC
SRA America is sponsoring an evening event with me in NYC. If folks want to go, the details are on our web site under Events. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] thread_test.c problems
[EMAIL PROTECTED] wrote: On 4/4/04 11:43 PM, Bruce Momjian [EMAIL PROTECTED] wrote: OK, new patch applied that causes all threads to wait until the parent checks their thread-specific pointers. I ran 1000 tests and all passed. Hopefully it will good for you too. I'll try to give it a test tonight. Please grab CVS. I added sched_yield() too. Dumb question... Why would you not always use the _r functions if they exist? Yes, we do use *_r functions in 7.5 if they exist, but in 7.4.X, I think we use the non-R if we can, though we actually just use getaddrinfo() in 7.4.X if it exists. Basically, the threading tests are still in flux, as you can see, in 7.4.X. It works, but it isn't 100% configure perfect yet. I'm still not clear on this... The thread_test program checks to see if the non-r functions are thread safe. If so, it directs you to set =yes in the template file - I assume that causes the non-r function to be used. If they are not thread safe, it directs you to use =no - which I assume causes the *_r functions to be used. Why would you not *always* use the _r functions if they exist, and only check for thread safety if the _r functions do not exist? Or, am I misunderstanding how the xxx=yes is used? In 7.4.X, the thought was that if the native function are already thread-safe, why bother calling the *_r functions, but in 7.5, we decided it was better to use the *_r functions --- again, 7.4.X has threads working, but the configure issues were in flux as we learned how each platform handled threading. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Mirroring tables from Oracle to Postgresql
Hi there, First of all, I suppose my problem should be at least farly common, but haven't had luck trying to find what I need by searching on the docs, google, etc. So please if this issue has already been covered please point me to some URL, or the list archives, or what you think is fine. My issue is that I need to do some replication from Oracle tables to PSQL. One-way only, incremental, and to a remote machine. I've been suggested to do a triggered procedure in Oracle so that everytime a file is inserted, deleted or updated, a perl script is run which modifies in turn the PSQL DB. I'd like to know if this is the proper approach, and also if someone else has done anything similar (this is for office work, and the bosses would feel more comfortable with a pre-fabricated solution that with some quick hacking of mine, even if it's the same stuff ;). If there isn't previous work, doing the hacking is still fine. What do you recommend me? Thanks in advance, Miguel ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] thread_test.c problems
On 4/4/04 11:43 PM, Bruce Momjian [EMAIL PROTECTED] wrote: OK, new patch applied that causes all threads to wait until the parent checks their thread-specific pointers. I ran 1000 tests and all passed. Hopefully it will good for you too. I'll try to give it a test tonight. Dumb question... Why would you not always use the _r functions if they exist? Yes, we do use *_r functions in 7.5 if they exist, but in 7.4.X, I think we use the non-R if we can, though we actually just use getaddrinfo() in 7.4.X if it exists. Basically, the threading tests are still in flux, as you can see, in 7.4.X. It works, but it isn't 100% configure perfect yet. I'm still not clear on this... The thread_test program checks to see if the non-r functions are thread safe. If so, it directs you to set =yes in the template file - I assume that causes the non-r function to be used. If they are not thread safe, it directs you to use =no - which I assume causes the *_r functions to be used. Why would you not *always* use the _r functions if they exist, and only check for thread safety if the _r functions do not exist? Or, am I misunderstanding how the xxx=yes is used? Wes ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Mirroring tables from Oracle to Postgresql
Hello Miguel, On Monday 05 April 2004 15:58, Miguel Guzman Miranda wrote: My issue is that I need to do some replication from Oracle tables to PSQL. One-way only, incremental, and to a remote machine. I have to replicate a lot of data from Oracle/MS SQL/Progress to PostgreSQL I have written an Java library which synchronize the data from a database to another database and do some usefull things if a record was inserted/changed/deleted. In our situation, we synchronize once a day. At the moment I'm rewritting the library to use Metadata. Regards, Cees. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Storing jpgs
Dear All, What's the best way to store jpgs in postgresql to use in a web page? I tried to use large objects, but how would you extract them from a table to be viewed in a web-page without having to write them to a scratch file somewhere first? Thanks Colin _ Stay in touch with absent friends - get MSN Messenger http://www.msn.co.uk/messenger ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] thread_test.c problems
Greg Stark wrote: Bruce Momjian [EMAIL PROTECTED] writes: [EMAIL PROTECTED] wrote: On 4/4/04 11:43 PM, Bruce Momjian [EMAIL PROTECTED] wrote: OK, new patch applied that causes all threads to wait until the parent checks their thread-specific pointers. I ran 1000 tests and all passed. Hopefully it will good for you too. I'll try to give it a test tonight. Please grab CVS. I added sched_yield() too. Make sure to test this on linux 2.6. They changed sched_yield() to not cause context switches in a lot of cases where it did previously. You might need a usleep() there instead or in addition. Usleep is probably more unportable than sched_yield on platforms that support threads. I actually don't need sched_yield to context switch, only to allow the other threads to complete. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Storing jpgs
I won't say what is right or wrong to do. But some will say to store the file location in a field such as /mydocs/mypictures/myimage.jpg and then have your php or what ever open that file. by using a select statement. Not sure how you would store them in the database and pull it righ out. I would prefer your method for many reasons. For one backing up the database also backups up all your image files. Good luck. |-+-- | | C G | | | [EMAIL PROTECTED] | | | Sent by: | | | [EMAIL PROTECTED]| | | tgresql.org| | | | | | | | | 04/05/2004 11:20 AM| | | | |-+-- --| | | | To: [EMAIL PROTECTED] | | cc: | | Subject: [GENERAL] Storing jpgs | --| Dear All, What's the best way to store jpgs in postgresql to use in a web page? I tried to use large objects, but how would you extract them from a table to be viewed in a web-page without having to write them to a scratch file somewhere first? Thanks Colin _ Stay in touch with absent friends - get MSN Messenger http://www.msn.co.uk/messenger ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) * PRIVILEGED AND CONFIDENTIAL: This communication, including attachments, is for the exclusive use of addressee and may contain proprietary, confidential and/or privileged information. If you are not the intended recipient, any use, copying, disclosure, dissemination or distribution is strictly prohibited. If you are not the intended recipient, please notify the sender immediately by return e-mail, delete this communication and destroy all copies. * ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] thread_test.c problems
Bruce Momjian [EMAIL PROTECTED] writes: [EMAIL PROTECTED] wrote: On 4/4/04 11:43 PM, Bruce Momjian [EMAIL PROTECTED] wrote: OK, new patch applied that causes all threads to wait until the parent checks their thread-specific pointers. I ran 1000 tests and all passed. Hopefully it will good for you too. I'll try to give it a test tonight. Please grab CVS. I added sched_yield() too. Make sure to test this on linux 2.6. They changed sched_yield() to not cause context switches in a lot of cases where it did previously. You might need a usleep() there instead or in addition. -- greg ---(end of broadcast)--- TIP 3: 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] Storing jpgs
I've found bytea works better for me than large objects. As far as how to retrieve and display, that depends. What scripting language are you using? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of C G Sent: Monday, April 05, 2004 12:20 PM To: [EMAIL PROTECTED] Subject: [GENERAL] Storing jpgs Dear All, What's the best way to store jpgs in postgresql to use in a web page? I tried to use large objects, but how would you extract them from a table to be viewed in a web-page without having to write them to a scratch file somewhere first? Thanks Colin _ Stay in touch with absent friends - get MSN Messenger http://www.msn.co.uk/messenger ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Storing jpgs
On Mon, 5 Apr 2004, C G wrote: Dear All, What's the best way to store jpgs in postgresql to use in a web page? I tried to use large objects, but how would you extract them from a table to be viewed in a web-page without having to write them to a scratch file somewhere first? There are several ways to skin this cat, and your choice depends largely on what type of system you'll be deploying. Will you have more than one front end server? If so, will they be able to share a network file system mount for the files? Then the preferred method for many people is to store the jpg in the file system with a path in the database. If you can't mount the same point from multiple servers (or don't want to) then you'll need to store them in the database. However, maybe you want to be able to update multiple jpegs at the same time in a transaction? then storing them in either a bytea field or base64 encoded in a text field will work well. Storing them as base64 or as a path with a file system is likely more portable than using large objects. Also, you have to dump large objects seperately, so your backup process may be more complicated than you want. As for displaying them whether you store them as bytea, base64 encoded text, or large objects, most languages will allow you to build and deliver an image without having to write it to some temporary place. Here's a simple example from PHPBuilder on doing it with the file path in the database, and using a directory of files that may lie outside the documentroot of apache: http://www.phpbuilder.com/board/showthread.php?s=postid=10497815#post10497815 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Simple insert not returning
PostgreSQL v7.4 on Solaris 9
I've got a simple table
CREATE TABLE public.tbl_system
(
system_id int4 NOT NULL DEFAULT nextval('public.id_seq'::text),
system_name varchar(50) NOT NULL,
description varchar(300),
active bool DEFAULT true,
CONSTRAINT pk_tbl_system PRIMARY KEY (system_id),
CONSTRAINT unique_system_name UNIQUE (system_name)
) WITHOUT OIDS;
GRANT ALL ON TABLE public.tbl_system TO postgres WITH GRANT OPTION;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE public.tbl_system TO GROUP
webapps;
I then try to issue the following statement in psql...
INSERT INTO tbl_system ( system_name, description ) VALUES ( 'test',
'test system' );
after executing this query, it just sits there and never returns...
Am I missing something?
-={ Kyle }=-
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] RPM init-script: Why the locale setting?
On Sunday 04 April 2004 10:50 pm, Tom Lane wrote:
Troels Arvin [EMAIL PROTECTED] writes:
In the init-script contained in the RPMs downloadable from the PostgreSQL
site (I checked the one for Fedora), an explicit locale is set before
running initdb. - And the explicit locale is not C.
Only if you don't have a sysconfig file:
# Just in case no locale was set, use en_US
[ ! -f /etc/sysconfig/i18n ] echo LANG=en_US $PGDATA/../initdb.i18n
I agree though that it seems like a bad choice to default to en_US
rather than C. Lamar, any reason why it's like that?
Yes.
A bit of history before I enclose an e-mail from Trond Eivind Glomsrd (former
Red Hat internal PostgreSQL RPMmaintainer) on the subject. I am only
enclosing a single e-mail of an exchange that occurred over a period of a
couple of weeks; I have pretty much whole exchange archived if you want to
read more, although I cannot reveal the whole exchange due to some NDA stuff
in it. Although it might be OK at this point, since that was, after all, 3
years ago.
Back in PostgreSQL 7.1 days, locale settings and the issue of a database being
initdb'ed in one locale and the postmaster starting in another locale reared
up its head. I 'solved' the issue by hardcoding LC_ALL=C in the initscript.
This had the side-effect of making the regression tests pass. Trond wasn't
happy with my choice of C locale, and here is why:
Re: Thought you might find this very interesting.
From: [EMAIL PROTECTED] (Trond Eivind Glomsrd)
To: Lamar Owen [EMAIL PROTECTED]
Lamar Owen [EMAIL PROTECTED] writes:
On Friday 25 May 2001 15:04, you wrote:
Lamar Owen [EMAIL PROTECTED] writes:
I also intend to kill the output from database initialization.
I thought you had, at least in the RedHat 7.1 7.0.3 set.
Yup, but it has started showing up again in PostgreSQL 7.1.x
I need to sync that in with this set.
I've fixed a couple of issues with the inistscript, I'll send it to
you when it's finished even after sourcing a file with locale
values, the postmaster process doesn't seem to respect it. I'll need
to make this work before I build (I've confirmed that the current way
of handling this, using C, is not acceptable. The locale needs to be
different, and if that causes problems for pgsql, it's a bug in pgsql
which needs fixing - handling other aspects, like ordering, in a bad
way isn't an acceptable workaround.
C equals broken for non-English locales, and isn't an acceptable choice.
That is one argument I'll not be involved in, as I'm so used to the ASCII
sequence that it is second-nature, thus disqualifying me from commenting on
any collation issues.
1) It's not a vaslid choice for English - if you're looking in a
lexicon, you'll find Aspen, bridge, Cambridge, not Aspen,
Cambridge, bridge.
2) It's much worse in other locales... it gets the order of
chaaracters wrong as well.
Here is a test:
create table bar(
ord varchar(40),
foo int,
primary key(ord));
insert into bar values('re',2);
insert into bar values('re',3);
insert into bar values('are',4);
insert into bar values('zsh',5);
insert into bar values('begynne',6);
insert into bar values('ve',7);
select ord,foo from bar order by ord;
Here is a valid result:
are | 4
begynne | 6
zsh | 5
re | 2
ve | 7
re | 3
Here is an invalid result:
are | 4
begynne | 6
zsh | 5
re | 3
re | 2
ve | 7
The last one is what you get with LANG=C - as you can see, the
ordering of the Norwegian characters is wrong. The same would be the
issue for pretty much any non-English characters - their number in the
character table (as used by C) is not the same as their location in
the local alphabet (as used by the local locale).
--
Trond Eivind Glomsrd
Red Hat, Inc.
So there is a reason it is like it is. If you want to change that in the
local setting, you will have to reinitdb in C locale (and
edit /var/lib/pgsql/initdb.i18n accordingly, and be prepared for collation
differences and problems). The initial initdb is done in the system locale,
unless one does not exist, in which case en_US is used (again, so that when
you do store non-English characters you get sane ordering, and so that you
get the mixed-case ordering preferred by many people). The initdb locale
settings are stored in initdb.i18n, and they are re-sourced everytime
postgresql is started to prevent data corruption if postmaster is started
with a different locale from the initdb. Tom, is the data corruption issue
still an issue with 7.4.x, or is this just historical? It has been a long
time since I've looked in this corner of the RPM :-)
--
Lamar Owen
Director of Information Technology
Pisgah Astronomical Research Institute
1 PARI Drive
Rosman, NC 28772
(828)862-5554
www.pari.edu
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not
Re: [GENERAL] RPM init-script: Why the locale setting?
Lamar Owen [EMAIL PROTECTED] writes: ... The initdb locale settings are stored in initdb.i18n, and they are re-sourced everytime postgresql is started to prevent data corruption if postmaster is started with a different locale from the initdb. Tom, is the data corruption issue still an issue with 7.4.x, or is this just historical? That's historical. For several versions now, the LC_COLLATE and LC_CTYPE settings seen by initdb have been saved in pg_control and re-adopted by the postmaster at start, so that index order corruption problems are impossible. We do still adopt other settings such as LC_MESSAGES from the postmaster environment, although I believe that these will generally be read from postgresql.conf if you haven't toyed with what initdb puts into that file. In short then I doubt there's a need for initdb.i18n anymore. It would make more sense to have postgres' bash_profile source /etc/sysconfig/i18n directly. The question of what postgresql.init should do if there's no available LANG or LC_ALL setting seems orthogonal to me. I do not find Trond's arguments convincing at all: a person who feels that C locale is broken ought to set up /etc/sysconfig/i18n to specify another locale. The POSIX standards say that the default locale in the absence of any environmental variable is C, not en_US, and the fact that Trond doesn't like that default doesn't give him license to change it, nor IMHO to try to make an end run around the standard by pressuring initscript authors to override the POSIX spec. I have no objection to making en_US the default at the sysconfig level, but inserting it in lower levels of the system seems at best misguided. regards, tom lane ---(end of broadcast)--- TIP 3: 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] Large DB
On Sat, 03 Apr 2004 22:39:31 -0800, Mooney, Ryan [EMAIL PROTECTED] wrote: Ok, so I ran a vacuum analyse. It took ~1.7 days to finish. Just to make it clear: VACUUM and ANALYSE are two different commands. VACUUM is for cleaning up. It has to visit every tuple in every page, and if there are dead row versions, it also has to scan all indices belonging to the table. If there are lots of deleted rows and vacuum_mem is to small, VACUUM has to scan each index several times to delete all index entries pointing to dead rows. This might raise the cost to even more than O(tuplecount). ANALYSE collects a random sample of the rows in the table, the sample size depends on default_statistics_target and the maximum value you have set any column's statistics target to (ALTER TABLE ... ALTER COLUMN ... SET STATISTICS ...). If you didn't touch either, the sample size is 3000 rows. Then these 3000 rows are sorted and counted in different ways to generate statistics. The number of pages that have to be touched to collect the sample depends on the table size, but it does by far not grow proportionally to the number of pages, nblocks. The cost growth rate is greater than O(ln(nblocks)) and significantly lesser than O(nblocks). I have no simple formula for it, but I estimate that analysing your tp3 table would need between 28000 and 3 page reads, which should be doable in a few minutes. VACUUM ANALYSE is just VACUUM followed by ANALYSE with the added benefit, that the number of rows does not have to be estimated by ANALYSE, because VACUUM knows the exact value. The invalid page block was caused when I tried the 2.6 kernel (for other reasons than DB performance), its been there for a while, and I can deal w/ the data loss ERROR: invalid page header in block 10257032 of tp3_point_starttime AFAICS the invalid page is in an index, so there is no data loss. You could simply drop and re-create that index. That might take some time, though :-( Here is the explain analyse, you can see why I think that an index on just host might be better - hosts are a small set, starttime is a large set so the index should be more Efficient, I think you got that backwards. If there are not many hosts, then an index on host is not very selective, IOW you get a lot of hits when you look for a particular host. OTOH if you select a sufficiently small starttime interval, you get only a few rows, so using an index is most efficient. at the very least it should be (starttime, host), not (host, starttime) unless the indexing engine is smart enough to make that not matter (I suspect its not???). Yes, it should be (starttime, host). And no, PG is generally not smart enough to use an index if there is no condition on the first index column. - Index Scan using tp3_host_starttime, tp3_host_starttime, [...], tp3_host_starttime on tp3 (cost=0.00..195.07 rows=1 width=25) (actual time=59.42..96366.43 rows=206238 loops=1) Estimated number of rows: 1 Actual number of rows: 206238 The planner is way off here. Furtunately your huge number of rows makes it rule out every other (most probably slower) plan. How many hosts are there? Even if there are a few hundred, an index scan with that condition has to access and skip millions of index tuples. An index on (starttime, host) would visit less index tuples, and would more likely access the heap tuples in physical order. Having followed the ongoing discusion about this I can concur that it is definitely NOT O(1). Unfortunately I didn't track the time to vacuum The discussion on -hackers and the patch I posted earlier today are about ANALYSE, not VACUUM. However I believe that I'm going to follow the suggestions about reducing the table size so I'll have a brand new BD to play with in a couple weeks, Hopefully we'll see a success story here. so knowing what I know now, I can track that if anyones interested in the data besides me :) VACUUM and ANALYSE times? Certainly. Servus Manfred ---(end of broadcast)--- TIP 3: 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
