Re: [GENERAL] Commercial postgresql
BL == Bo Lorentsen [EMAIL PROTECTED] writes: BL Hi .. BL He likes to know about others using Postgres and simple replication (we BL need it in at 24/7 system), to know how postgres performs, and how BL stable it is regarding its data and the backup feature etc. I use it in 24/7/365 system which is heavily written to and read from. The drawbacks I have are: 1) upgrade to major versions require dump/restore which is a significant amount of downtime for a large DB. 2) the need to run vacuum on tables to keep them from bloating too much. on my system which is very busy, sometimes running vacuum pushes the disk beyond its limits and slows the whole system to a crawl. 3) Index bloat is apparently a bigger problem than I thought. Yesterday I took the hit of reindexing all my tables. On a 2.7Gb database (those of you who notice my numbers keep changing... this is the correct value -- I read wrongly before) I just shaved 900Mb of 'dead' index pages. Unfortunately, my largest table of 92M rows takes about 43 minutes *per index* to reindex. I think 2 is probably amplified by 3. We'll see how the system holds up this week after the indexes have been optimized. BL Are there some detailed busisness cases, that he/we can read to feel BL more assured that PG is a good choice ? If you want commercial support, it is out there. There are at least two companies offering it. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: [EMAIL PROTECTED] Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] adding SERIAL to a table
Hello
Now perhaps thisis a bit dumb,
but...
I just populated a new table via \copy.
After that, I realize that perhaps is a good thing to have a row identifier in
it, so I try
clapidus= alter table tickets add
column rid serial;NOTICE: ALTER TABLE will create implicit sequence
"tickets_rid_seq" for SERIAL column "tickets.rid"ERROR: adding columns
with defaults is not implemented
So my next guess is to definea test
tablefrom scratch, this time with the serial field in place. Next I try
the \copy:
clapidus= create table test(rid serial,
col_a text);NOTICE: CREATE TABLE will create implicit sequence
"test_rid_seq" for SERIAL column "test.rid"CREATE TABLEclapidus= \d
test
Table "test"Attribute | Type
|
Modifier---+-+---rid
| integer | not null default
nextval('public.test_rid_seq'::text)col_a |
text |
clapidus= \copy test from
stdin23 a
record45 another record\.clapidus=
select * from test ;rid |
col_a-+ 23 |a record 45 | another
record(2 rows)
Now the first character from stdin is a
tab, in a try to let thesequence come into action:
clapidus= \copy test from
stdin still another
record\.ERROR: invalid input syntax for integer: ""PQendcopy:
resetting connection
Grrr. Third attempt:
clapidus= \copy test from stdin with
null as 'NULL'NULL still another one\.ERROR:
null value for attribute "rid" violates NOT NULL constraintPQendcopy:
resetting connection
So? Is there a way to add the sequence to
an existing table?
Or, alternatively, is there a way to issue
a \copy command while letting the sequence fill in the serial
field?
thanks in advance
cl.
Re: [GENERAL] Commercial postgresql
On Monday 01 September 2003 22:08, Vivek Khera wrote: I use it in 24/7/365 system which is heavily written to and read from. The drawbacks I have are: Nitpik: that should be 24/7/52, since there aren't 365 weeks in a year. 1) upgrade to major versions require dump/restore which is a significant amount of downtime for a large DB. I have harped on this at length. Maybe one day we'll get real upgrading. Search the archives for the discussions; there are many, and they are long threads. -- Lamar Owen Director of Information Technology Pisgah Astronomical Research Institute ---(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] adding SERIAL to a table
Claudio Lapidus [EMAIL PROTECTED] writes:
So? Is there a way to add the sequence to an existing table?
Sure. You have to break the SERIAL down to its component parts though.
Something like
CREATE SEQUENCE seq;
ALTER TABLE tab ADD COLUMN ser INTEGER;
UPDATE tab SET ser = nextval('seq');-- this will take awhile
ALTER TABLE tab ALTER COLUMN ser SET DEFAULT nextval('seq');
ALTER TABLE tab ALTER COLUMN ser SET NOT NULL;
-- possibly also add a UNIQUE constraint
We haven't yet got round to supporting ADD COLUMN ... DEFAULT because
according to the SQL spec that implies doing the UPDATE to fill the
column values immediately, and that's just a chunk of code no one's
written yet.
Or, alternatively, is there a way to issue a \copy command while letting th=
e sequence fill in the serial field?
Yes, you have to list in the COPY command just the columns that are
actually being supplied in the input data. Columns not mentioned (like
the serial column) get filled from their defaults. I think this is
new in 7.3 ... it's pretty recent anyway.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[GENERAL] distributed.net now runs postgresql
Just thought everyone might be interested to know that the distributed.net project now uses postgresql for its stats tracking system. They just migrated from Sysbase which they had been using for several years. Check out: http://n0cgi.distributed.net/cgi/dnet-finger.cgi?user=decibel Matthew ---(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] pg_autovacuum
On Fri, 2003-08-29 at 17:21, Adam Kavan wrote: I decided to try pg_autovacuum and am having a problem. It never analyzes or vacuums. I looked at the code and had it display printf(%s,%s,%s\n,PQgetvalue(res, j, PQfnumber(res, n_tup_ins)),PQgetvalue(res, j, PQfnumber(res, n_tup_upd)),PQgetvalue(res, j, PQfnumber(res, n_tup_del))); All of these values are always 0 for all tables irregardless of activity on the tables. Any clue what could be causing this? In postgresql.conf I have : # - Query/Index Statistics Collector - stats_start_collector = true #stats_command_string = true #stats_block_level = true stats_row_level = true stats_reset_on_server_start = false which is fine if I am correct. Am I missing something? I am using 7.4beta2 but the same problem occurred yesterday with 7.4beta1. All of this appears to be in order. I think we need some more information. First, what options are you setting? What is the exact command you are using to launch pg_autovaccum. Some output from pg_autovacuum with debug level set to 2 would be helpful. Also, what OS / compiler are you using? BTW: are you sure that you have performed enough inserts / updates / deletes to exceed the thresholds? (debug level 2 will tell you what the thresholds are. Matthew ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] pg_autovacuum
On Fri, 2003-08-29 at 17:21, Adam Kavan wrote: I decided to try pg_autovacuum and am having a problem. FYI, I just downloaded beta2 and compiled it on RH9 and pg_autovacuum works fine. ---(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] Commercial postgresql
Lamar Owen wrote: Nitpik: that should be 24/7/52, since there aren't 365 weeks in a year. Oh, great. It's just that 7*52 = 364. That leaves us with a full day to idle and still honor the SLA, right? Sorry, couldn't resist :) cl. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] About GPL and proprietary software
Bruce Momjian [EMAIL PROTECTED] writes: The FSF would _like_ dynamic linking to pass the GPL to the closed-source binary, but that doesn't make it so --- I would like a lot of things but wanting it to happen isn't enough. Their FAQ says (http://www.gnu.org/licenses/gpl-faq.html): What is the difference between mere aggregation and combining two modules into one program? Mere aggregation of two programs means putting them side by side on the same CD-ROM or hard disk. We use this term in the case where they are separate programs, not parts of a single program. In this case, if one of the programs is covered by the GPL, it has no effect on the other program. Combining two modules means connecting them together so that they form a single larger program. If either part is covered by the GPL, the whole combination must also be released under the GPL--if you can't, or won't, do that, you may not combine them. What constitutes combining two parts into one program? This is a legal question, which ultimately judges will decide. We believe that a ^^^ proper criterion depends both on the mechanism of communication (exec, pipes, rpc, function calls within a shared address space, etc.) and the semantics of the communication (what kinds of information are interchanged). You can bet that RMS, control freak that he is, wouldn't have put that disclaimer in there if he felt he had much chance of making the GPL dynamic linking restriction enforceable. Name calling (control freak) is childish. If you are not a lawyer and you want to bet that dynamic linking to a GPL'ed library doesn't invoke the GPL then I think you're taking a gamble. Clearly you think you know more about the law than the FSF General Counsel Eben Moglen (professor of law at Columbia). Combined works dynamically linked to GPL libraries involve untested legal issues. The legal issues are complex, and when law and technology collide it can be hard to predict the outcome. RMS believes the GPL is enforcable in this case, but until someone is willing to be sued by the FSF over this no one will know for sure. (None of the GPL violators the FSF has pursued have been willing to risk a trial so far.) ---(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] About GPL and proprietary software
Doug Quale wrote: Bruce Momjian [EMAIL PROTECTED] writes: You can bet that RMS, control freak that he is, wouldn't have put that disclaimer in there if he felt he had much chance of making the GPL dynamic linking restriction enforceable. Name calling (control freak) is childish. Eh? Bruce is childish for opining that RMS is a control freak? What is the proper terminology for those that call people who think RMS is a control freak (childish)? If you are not a lawyer and you want to bet that dynamic linking to a GPL'ed library doesn't invoke the GPL then I think you're taking a gamble. Clearly you think you know more about the law than the FSF General Counsel Eben Moglen (professor of law at Columbia). A bit hypersensitive, aren't we? Mike Mascari ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] pgadmin3 on Gentoo
Hi, I'm really happy to see that pgadmin3 will run on Linux! I tried to build it on Gentoo but got errors. Was anybody of you able to build it for Gentoo or will there be an emerge file? Regards, Christian ---(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] pgadmin3 on Gentoo
On Tue, 02 Sep 2003 08:42:32 +0200 Christian Traber [EMAIL PROTECTED] wrote: Hi, I'm really happy to see that pgadmin3 will run on Linux! I tried to build it on Gentoo but got errors. Was anybody of you able to build it for Gentoo or will there be an emerge file? Can't help with the build problems but I have found this client is better than pga3 in its current state. http://squirrel-sql.sourceforge.net/ http://jdbc.postgresql.org/download.html http://java.sun.com/j2se/1.4.2/download.html Regards, Christian ---(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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] automatic update
A trigger is the right way. Rules rewrite a statement whereas triggers are at row level. so in plpgsql: create or replace function trig_param_ins_upd() returns trigger as ' begin new.ts = current_timestamp; return new; end;' language 'plpgsql'; create trigger trig_param_ins_upd before insert or update on param for each row execute procedure trig_param_ins_upd(); now whenever a new row is insert or a row is updated the trigger will ensure that ts always contains the current timestamp. Rgds, Jason On Tue, 2 Sep 2003 06:56 pm, Alessandro GARDICH wrote: Hi to all ... I'm looking to a way to auto update some fields of a row when other fileds are updated. the table structure are simple, CREATE TABLE param ( id int4 PRIMARY KEY, val int4, ts timestam(3) DEFAULT CURRENT_TIMESTAMP ); so when a new entry are insert ts areautomatically update, but i would make the same on update ... i would that on UDPATE param SET val=100 WHERE id=1; also ts field have to be updated to CURRENT_TIMESTAMP I try with a RULE but obtain only a loop, seem RULE aren't good to make such things, modify a statment on the same table it's related :( i look for a trigger but I suppose the same problem arise ... how i can solve the problem ??? mhhh does i have to have a VIEW of parm ... called param2 without the ts field and make a rule on param2 that update param.ts ??? seem a bit tricky :( ... thanks in advance ... ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Commercial postgresql
On Tue, 2003-09-02 at 04:08, Vivek Khera wrote: I use it in 24/7/365 system which is heavily written to and read from. The drawbacks I have are: How depressing, may I ask that PG version you are using ? 1) upgrade to major versions require dump/restore which is a significant amount of downtime for a large DB. Ok, this is not a thing you do very often, and it would help is we got a diff (since last backup) pg_dump. As one could install the new DB in parallel with produktion, and then just apply the diff dump on the db swap. 2) the need to run vacuum on tables to keep them from bloating too much. on my system which is very busy, sometimes running vacuum pushes the disk beyond its limits and slows the whole system to a crawl. How often does this vacuum run, and how many delete/updates are there in between ? 3) Index bloat is apparently a bigger problem than I thought. This does not sound too nice ! If you want commercial support, it is out there. There are at least two companies offering it. But you have not been unsing any of there services ? /BL ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Commercial postgresql
On 2 Sep 2003 at 11:49, Bo Lorentsen wrote: 3) Index bloat is apparently a bigger problem than I thought. This does not sound too nice ! That's fixed in 7.4. Vacuum in 7.4 prevents index bloat as well. Couple with autovacuum daemon or scheduled vacuums, things (hopefully) will be lot better than earlier days.. Bye Shridhar -- QOTD: If you're looking for trouble, I can offer you a wide selection. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL]
Hi, where can be fount Win32 versions of Postgress? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Multiple Connections
On 01/09/2003 20:10 Carmen Gloria Sepulveda Dedes wrote: I'm working with ECPG. Somebody know how can I establish multiple connections to the database?? (I have used ENABLE THREADS and CONTEXT ALLOCATE in oracle ... I'm looking for something similar to this ... if exists) Have you tried using AS connection-name in your EXEC SQL CONNECT ... statement? Hi! ... I don't know thar statement ... Can you explain me more??? Thanks. It's in section 4.2 of ECPG docs. -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL]
On 2 Sep 2003 at 14:08, [EMAIL PROTECTED] wrote: Hi, where can be fount Win32 versions of Postgress? http://techdocs.postgresql.org/guides/Windows See, if that helps you.. Bye Shridhar -- No problem is insoluble.-- Dr. Janet Wallace, The Deadly Years, stardate 3479.4 ---(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] Commercial postgresql
On Tue, 2003-09-02 at 11:55, Shridhar Daithankar wrote: That's fixed in 7.4. Vacuum in 7.4 prevents index bloat as well. Couple with autovacuum daemon or scheduled vacuums, things (hopefully) will be lot better than earlier days.. Are there a list of things that will be done in 7.4, or better a development roadmap like the one the Mozilla folks are using ? These things are quite important when you have to convince your boss :-) /BL ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] ADD_Months Function in Postgres
Title: ADD_Months Function in Postgres Hi, I would like to know if there is a Function in Postgres Like the add_months Function in Oracle?? Thanxx Schoeib 4Tek Gesellschaft für angewandte Informationstechnologien mbH Schoeib Amin Tel. +49 (0) 69 697688-132 Fax. +49 (0) 69 697688-111 http://www.4tek.de
Re: [GENERAL] Commercial postgresql
On Sat, Aug 30, 2003 at 01:59:20PM +0200, Bo Lorentsen wrote: He likes to know about others using Postgres and simple replication (we need it in at 24/7 system), to know how postgres performs, and how stable it is regarding its data and the backup feature etc. The replication system released last week (erserver: see gborg) is the one that we originally used on our production systems. It held up under load for 2 years. If you do a whois for .info or .org domain names, you are hitting a PostgreSQL database. You can check out the SLAs on those systems on the ICANN site, since they post all the contracts. A Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada [EMAIL PROTECTED] M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Commercial postgresql
These things are quite important when you have to convince your boss :-) Convincing boss just say: Caugh board or use postgresql. Meaning of this is that you can't compare comercical or free ware. Probably you want to use postgre because of its free, if this isn't case why you won use oracle ? Bo Lorentsen [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] On Tue, 2003-09-02 at 11:55, Shridhar Daithankar wrote: That's fixed in 7.4. Vacuum in 7.4 prevents index bloat as well. Couple with autovacuum daemon or scheduled vacuums, things (hopefully) will be lot better than earlier days.. Are there a list of things that will be done in 7.4, or better a development roadmap like the one the Mozilla folks are using ? These things are quite important when you have to convince your boss :-) /BL ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(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] ADD_Months Function in Postgres
Hello You can try select current_date + interval '1 month'; regards Pavel Stehule On Tue, 2 Sep 2003, Amin Schoeib wrote: Hi, I would like to know if there is a Function in Postgres Like the add_months Function in Oracle?? Thanxx Schoeib 4Tek Gesellschaft fr angewandte Informationstechnologien mbH Schoeib Amin Tel. +49 (0) 69 697688-132 Fax. +49 (0) 69 697688-111 http://www.4tek.de ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Commercial postgresql
On 2 Sep 2003 at 12:25, Bo Lorentsen wrote: On Tue, 2003-09-02 at 11:55, Shridhar Daithankar wrote: That's fixed in 7.4. Vacuum in 7.4 prevents index bloat as well. Couple with autovacuum daemon or scheduled vacuums, things (hopefully) will be lot better than earlier days.. Are there a list of things that will be done in 7.4, or better a development roadmap like the one the Mozilla folks are using ? Yes. Do a CVS checkout and check History and TODO files. There are fairly well maintained. Also check pending patches. http://developers.postgresql.org is the palce to start. 7.4 Documentation is another place you could check for new features.. I think it could have been better organised. A huge bugzilla like KDE uses could be a great thing. But for sure, there is nothing missing. You just need to get tuned to the way postgresql gets developed. Hang on on hackers for some time and you will pick up. These things are quite important when you have to convince your boss :-) I know.:-) Bye Shridhar -- Hand, n.: A singular instrument worn at the end of a human arm and commonly thrust into somebody's pocket. -- Ambrose Bierce, The Devil's Dictionary ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Problems with GRANTING SELECT to a table
Title: Problems with GRANTING SELECT to a table
Hi,
I have a problem with permissions for SELECT.
Here is what I did:
test= GRANT SELECT ON poi.fondsstamm TO flex;
GRANT
test= \dp fondsstamm;
Access privileges for database test
Schema | Table | Access privileges
++---
poi | fondsstamm | {=arwdRxt,poi=arwdRxt,flex=r}
(1 row)
Now when I try to select from the table poi.fondsstamm
I become always the error: Permission denied,
psql test flex
Welcome to psql 7.3.2, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
test= select * from poi.fondsstamm;
ERROR: poi: permission denied
What am I doing wrong??
Thanxx
Schoeib
4Tek Gesellschaft für angewandte Informationstechnologien mbH
Schoeib Amin
Tel. +49 (0) 69 697688-132
Fax. +49 (0) 69 697688-111
http://www.4tek.de
[GENERAL] LAST_DAY Function in Postgres
Title: LAST_DAY Function in Postgres Hi, I would like to know if there is a Function in Postgres Like the LAST_DAY Function in Oracle?? In Oracle you can use the function the get the last day of a month. Thanxx Schoeib 4Tek Gesellschaft für angewandte Informationstechnologien mbH Schoeib Amin Tel. +49 (0) 69 697688-132 Fax. +49 (0) 69 697688-111 http://www.4tek.de
CPAN, P for postgresql [Re: [GENERAL] LAST_DAY Function in Postgres]
On 2 Sep 2003 at 15:08, Amin Schoeib wrote: I would like to know if there is a Function in Postgres Like the LAST_DAY Function in Oracle?? In Oracle you can use the function the get the last day of a month. While this is not a direct answer to question.. It made me think. I am sure lot of people have lot of functions written in order to get job done. Can we have an archive of such functions, if authors are willing to make it open. Something like CPAN? Yeah we don't need to change that name..:-) Bye Shridhar -- ...very few phenomena can pull someone out of Deep Hack Mode, with twonoted exceptions: being struck by lightning, or worse, your *computer*being struck by lightning.(By Matt Welsh) ---(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] LAST_DAY Function in Postgres
Hi,
if didn't find this function on
http://www.postgresql.org/docs/7.3/interactive/functions-datetime.html,
You have to write own function
testdb= create or replace function last_day(date) returns date as 'select
cast(date_trunc(''month'', $1) + ''1 month''::interval as date) - 1'
language sql;
CREATE FUNCTION
testdb= select last_day(current_date);
last_day
2003-09-30
(1 dka)
Bye
Pavel
On Tue, 2 Sep 2003, Amin Schoeib wrote:
Hi,
I would like to know if there is a Function in Postgres
Like the LAST_DAY Function in Oracle??
In Oracle you can use the function the get the last day of a month.
Thanxx
Schoeib
4Tek Gesellschaft fr angewandte Informationstechnologien mbH
Schoeib Amin
Tel. +49 (0) 69 697688-132
Fax. +49 (0) 69 697688-111
http://www.4tek.de
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: CPAN, P for postgresql [Re: [GENERAL] LAST_DAY Function in
On Tue, 2 Sep 2003, Shridhar Daithankar wrote: On 2 Sep 2003 at 15:08, Amin Schoeib wrote: I would like to know if there is a Function in Postgres Like the LAST_DAY Function in Oracle?? In Oracle you can use the function the get the last day of a month. While this is not a direct answer to question.. It made me think. I am sure lot of people have lot of functions written in order to get job done. Can we have an archive of such functions, if authors are willing to make it open. Something like CPAN? Yeah we don't need to change that name..:-) Cookbook pages? http://www.brasileiro.net/postgres/cookbook/ Pavel Bye Shridhar -- ...very few phenomena can pull someone out of Deep Hack Mode, with twonoted exceptions: being struck by lightning, or worse, your *computer*being struck by lightning.(By Matt Welsh) ---(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 8: explain analyze is your friend
Re: [GENERAL] Commercial postgresql
BL == Bo Lorentsen [EMAIL PROTECTED] writes: BL On Tue, 2003-09-02 at 04:08, Vivek Khera wrote: I use it in 24/7/365 system which is heavily written to and read from. The drawbacks I have are: BL How depressing, may I ask that PG version you are using ? Currently 7.2 in production, 7.4b2 in testing on the new system... 1) upgrade to major versions require dump/restore which is a significant amount of downtime for a large DB. BL Ok, this is not a thing you do very often, and it would help is we got a BL diff (since last backup) pg_dump. As one could install the new DB in BL parallel with produktion, and then just apply the diff dump on the db BL swap. Well, the thing is for a large DB which is very active, it still requires significant down-time, since you can't do this 'live'. 2) the need to run vacuum on tables to keep them from bloating too much. on my system which is very busy, sometimes running vacuum pushes the disk beyond its limits and slows the whole system to a crawl. BL How often does this vacuum run, and how many delete/updates are there in BL between ? There are *at least* 1 million inserts and 1 million updates per day. Every two weeks, I purge some old data, which means something like 25 to 30 million rows deleted across several tables (thank $DIETY for cascade delete). 3) Index bloat is apparently a bigger problem than I thought. BL This does not sound too nice ! No, like I said, I shaved 900Mb of index table size this weekend by re-indexing. Unfortunately it meant I was partially down for about 45 minutes per index on my largest table, and about 15 per index on the second largest table, and 5 per index on the third largest, then about 90 seconds total for the rest of the tables ;-) If you want commercial support, it is out there. There are at least two companies offering it. BL But you have not been unsing any of there services ? yes. but for a very specific type of support. ---(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] Commercial postgresql
On 2 Sep 2003 at 9:36, Vivek Khera wrote: BL == Bo Lorentsen [EMAIL PROTECTED] writes: BL On Tue, 2003-09-02 at 04:08, Vivek Khera wrote: 3) Index bloat is apparently a bigger problem than I thought. BL This does not sound too nice ! No, like I said, I shaved 900Mb of index table size this weekend by re-indexing. Unfortunately it meant I was partially down for about 45 minutes per index on my largest table, and about 15 per index on the second largest table, and 5 per index on the third largest, then about 90 seconds total for the rest of the tables ;-) Umm.. Since you have only 2.7GB of data, all inclusive, would it be real downtime if you reindex in a transaction, assuming the downtime was not due to crunch of IO bandwidth.. Just a thought.. Bye Shridhar -- Beauty: What's in your eye when you have a bee in your hand. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] distributed.net now runs postgresql
On Mon, 2003-09-01 at 21:46, Matthew T. O'Connor wrote: Just thought everyone might be interested to know that the distributed.net project now uses postgresql for its stats tracking system. They just migrated from Sysbase which they had been using for several years. Check out: http://n0cgi.distributed.net/cgi/dnet-finger.cgi?user=decibel Great. I've been grinding away on d.net for almost 3 years, now. I hope that decibel joins the mailing lists. -- - Ron Johnson, Jr. [EMAIL PROTECTED] Jefferson, LA USA After seeing all the viruses, trojan horses, worms and Reply mails from stupidly-configured anti-virus software that's been hurled upon the internet for the last 3 years, and the time/money that is spent proteting against said viruses, trojan horses worms, I can only conclude that Microsoft is dangerous to the internet and American commerce, and it's software should be banned. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Restarting, ownership, and permissions
On Tuesday 02 September 2003 21:51, Aurangzeb M. Agha wrote: I run my Postgres DB on a virtual linux server where I don't have root. I'm 'admin', by default, and have created a 'postgres' user to own the DB dir. My problem is that on occassion, my ISP restarts my server, stranding all my processes. While I have scripts to restart Apache and my Servlet engine on reboot, I don't know how to get the Postgres DB up and running again. The main problem is that the dir is owned (postgres.postgres) and chmod'd to 700, keeping anyone but the 'postgres' user from starting it up. I tried chownin'g the DB dir to postgres.admin, and chmodding to 770, but when I tried to start the DB, I got a fatal error in the log indicating that postgres must own the dir, and the permissions must be u=rwx. What can I do? Hmm - so you can't su - postgres because as admin you'll need a password. Off the top of my head you could: 1. chmod PG's startup script, so it runs suid as postgres (chmod u+s) 2. Use ssh to login as user postgres on localhost and execute pg_ctl start (you can setup ssh so it doesn't need a password) Either of those should work, with a little effort. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] web hosting postgres
Hi, Can anyone out there point me to an inexpensive web hosting solution that offers postgres database support??? Thanks Aaron ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Restarting, ownership, and permissions
On Tue, 2 Sep 2003, Richard Huxton wrote: On Tuesday 02 September 2003 21:51, Aurangzeb M. Agha wrote: I run my Postgres DB on a virtual linux server where I don't have root. I'm 'admin', by default, and have created a 'postgres' user to own the DB dir. My problem is that on occassion, my ISP restarts my server, stranding all my processes. While I have scripts to restart Apache and my Servlet engine on reboot, I don't know how to get the Postgres DB up and running again. The main problem is that the dir is owned (postgres.postgres) and chmod'd to 700, keeping anyone but the 'postgres' user from starting it up. I tried chownin'g the DB dir to postgres.admin, and chmodding to 770, but when I tried to start the DB, I got a fatal error in the log indicating that postgres must own the dir, and the permissions must be u=rwx. What can I do? Hmm - so you can't su - postgres because as admin you'll need a password. Off the top of my head you could: 1. chmod PG's startup script, so it runs suid as postgres (chmod u+s) 2. Use ssh to login as user postgres on localhost and execute pg_ctl start (you can setup ssh so it doesn't need a password) Either of those should work, with a little effort. But not without root access or logging in as postgres :) I would suggest that if you can't get your ISP to install the startup script where is should be (/etc/init.d/ and the appropiate symbolic link) then don't use the postgres user for your database. Presumably as admin you can administer users. So create a new user, say postgres2. Initialise a data directory: $ initdb -D ~postgres2/data as that new user. Then when you need to you can at least start the db manually by logging in as postgres2, which you are able to do since you have control over that user. -- Nigel J. Andrews ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Commercial postgresql
Bruno Wolff III [EMAIL PROTECTED] writes: Bo Lorentsen [EMAIL PROTECTED] wrote: Are there a list of things that will be done in 7.4, or better a development roadmap like the one the Mozilla folks are using ? For a quick look see the current version of the release notes on the web at: http://developer.postgresql.org/docs/postgres/release.html This will be a little easier than checking out a copy from CVS. However, I think the HISTORY file has more detail in it. I don't believe Bruce has yet made any effort to update the SGML release-notes file for 7.4. Instead look at the CVS-tip HISTORY file: http://developer.postgresql.org/cvsweb.cgi/pgsql-server/HISTORY regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Commercial postgresql
On Tue, 2003-09-02 at 22:21, Bruno Wolff III wrote: This will be a little easier than checking out a copy from CVS. However, I think the HISTORY file has more detail in it. I think you are right, maybe a cvsview utility would help, regarding this problem ? But a bugzilla tool, may also be nice :-) /BL ---(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] Commercial postgresql
On Tue, 2 Sep 2003, Bo Lorentsen wrote: On Tue, 2003-09-02 at 22:21, Bruno Wolff III wrote: This will be a little easier than checking out a copy from CVS. However, I think the HISTORY file has more detail in it. I think you are right, maybe a cvsview utility would help, regarding this problem ? But a bugzilla tool, may also be nice :-) /BL There is cvsweb available for the repository. I think it's linked from somewhere on http://developers.postgresql.org/ -- Nigel J. Andrews ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] pg_autovacuum
What do you mean which options am I setting? I am using ./pg_autovacuum -U akavan -s30 -d3 Yeah, this is what I was looking for. I am using redhat 9 with gcc 3.2.2. And I will paste some output from debug level 2 at the end of this post. During the run I inserted 10523 lines into PointUsage then deleted them... between pg_autovac scans. I only included the first scan of my database because I didn't want to clutter up the list to much, the output is mostly unchanged between scans (the amount of time taken at the bottom changes nothing else does. [snip] [2003-09-02 02:46:00 PM] table name: data.public.PointUsage [2003-09-02 02:46:00 PM] relfilenode: 17498 [2003-09-02 02:46:00 PM] reltuples: 5; relpages: 1 [2003-09-02 02:46:00 PM] curr_analyze_count: 0; cur_delete_count: 0 [2003-09-02 02:46:00 PM] ins_at_last_analyze: 0; del_at_last_vacuum: 0 [2003-09-02 02:46:00 PM] insert_threshold: 505; delete_threshold1010 [2003-09-02 02:46:00 PM] added table: data.public.PointUsage [snip more log detail...] [2003-09-02 02:46:00 PM] table name: data.public.PointUsage [2003-09-02 02:46:00 PM] relfilenode: 17498 [2003-09-02 02:46:00 PM] reltuples: 5; relpages: 1 [2003-09-02 02:46:00 PM] curr_analyze_count: 0; cur_delete_count: 0 [2003-09-02 02:46:00 PM] ins_at_last_analyze: 0; del_at_last_vacuum: 0 [2003-09-02 02:46:00 PM] insert_threshold: 505; delete_threshold1010 [2003-09-02 02:46:00 PM] 1 All DBs checked in: 260135 usec, will sleep for 30 secs. OK, so all you have shown me is the output from the first loop of pg_autovacuum. The PointUsage table was added, the stats system had reported 0 inserts update or deletes when the table was added, and when it was checked 0.2 seconds later, it still had reported no deletes, so at this point it is correct in not doing a vacuum. What I really need is the logoutput from before, during and just after you insert / update / delete from the PointUsage table. Also, setting your threshold values lower helps if you just want to see pg_autovacuum actually fire off a vacuum. So you probabaly want to do somethign like this: pg_autovacuum -d2 -s1 -S0 -v10 -V0 -a10 -A0 -L pg_autovacuum.out this will set debug=2, sleep only 1 sec after each look, and perform vacuums and analyzes after only 10 insert/update/delets and log it all to pg_autovacuum.out Run your update script Kill pg_autovacuum bzip the logfile and send it to the mailing list (or just to me if you prefer). Also a copy of your script might help. Matthew ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Tomcat Connection Pool?
I am running a connection pool for the PostgreSQL and I was wondering which values you would reccommend for the connection pool? parameter namemaxWait/name value5000/value /parameter parameter namemaxActive/name value10/value /parameter parameter namemaxIdle/name value2/value /parameter What does maxWait and maxIdle means? Regards, BTJ -- --- Bjrn T Johansen (BSc,MNIF) Executive Manager [EMAIL PROTECTED] Havleik Consulting Phone : +47 67 54 15 17 Conradisvei 4 Fax : +47 67 54 13 91 N-1338 Sandvika Cellular : +47 926 93 298 http://www.havleik.no --- The stickers on the side of the box said Supported Platforms: Windows 98, Windows NT 4.0, Windows 2000 or better, so clearly Linux was a supported platform. ---
Re: [GENERAL] pg_autovacuum
So you probabaly want to do somethign like this: pg_autovacuum -d2 -s1 -S0 -v10 -V0 -a10 -A0 -L pg_autovacuum.out this will set debug=2, sleep only 1 sec after each look, and perform vacuums and analyzes after only 10 insert/update/delets and log it all to pg_autovacuum.out Run your update script Kill pg_autovacuum bzip the logfile and send it to the mailing list (or just to me if you prefer). Also a copy of your script might help. Matthew Ok I ran pg_autovacuum using the supplied command line and have attached the resulting log. My script is just INSERT INTO PointUsage VALUES(DEFAULT,32,now(),1024); repeated several thousand times. I ran autovac, then my script, then verified all of the rows were inserted then ran DELETE FROM PointUsage WHERE Value = 1024; to kill all of the new rows, and then killed autovac. The results are attached. Thank you very much for taking the time to look at this for me. --- Adam Kavan --- [EMAIL PROTECTED] pg_autovacuum.out.bz2 Description: Binary data ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Querying $libdir
The subject says it really. Is there a way to query the value of $libdir, and I don't mean the dynamic_library_path GUC? I ask because, I have scripts to create databases and I've come to make adjustments to load tsearch2. Now contrib/tsearch2 installs itself into $libdir with tsearch2.sql installed as $libdir/contrib/tsearch2.sql. So my scripts either need to know via hardcoding where tsearch2.sql is installed, try to work it out from file system searching, ask the user running the scripts or obtain it from the horses mouth. Hardcoding where I think it is installed is fine until the build happens on a system I don't control. Same thing for a list of hardcoded likely locations. File system searching again uses information about where I think things are installed or tries to determine it by finding out where applications like psql are located (assuming the picked up psql is part of the same installation as the backend it is talking to) and doing some jiggery pokery from there, again making some installation location assumptions to find a suitably named file. Asking the user is fine, would work and is a reasonably quick solution. It just spoils the look of things when scripts run by other scripts start pestering the user for info about the system things. Or, I could just ask the horses mouth by issuing a query in the backend via psql. Guess which I think I want to use. The question being is this even possible in a stock 7.3 server? I know it's not the right list but seeing as I'm emailing. tsearch2 doesn't even compile outside of contrib/ I presume that is necessary? Is there any particular reason why the stop word files are installed with no permissions for 'world'? It kinda screws up installing as one user and running as another. -- Nigel J. Andrews ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] web hosting postgres
HI, how much storage space etc. Thanks Aaron On Wed, 2003-09-03 at 02:44, Richard Welty wrote: On 03 Sep 2003 00:26:04 +0300 Aaron [EMAIL PROTECTED] wrote: Can anyone out there point me to an inexpensive web hosting solution that offers postgres database support??? i have a linux server in colo that provides postgresql. about $160/year for virtual domain support with apache and php or perl. richard ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] pg_autovacuum
First, I meant to ask for debug level 3, (-d3, not -d2) sorry Also, are you sure that pg_autovacuum is connecting to the same database cluster as your insert delete script? I have attached a new log with a debug level of 3. And I am sure they are connecting to the same cluster, I only have one postgres box that is active. In the output you should see the cur_analyze_count increase with each insert and the curr_delete_count increase with every delete. Also, the debug output should tell you the threshold you have to reach for a vacuum or analyze. And there is the problem, all of the counts stay at 0 no matter what I do. --- Adam Kavan --- [EMAIL PROTECTED] pg_autovacuum.out.bz2 Description: Binary data ---(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
[GENERAL] Debian Woody 7.3.4 packages?
Hi, Does anyone have any packages for 7.3.4 for debian woody? Thanks in advance, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ All that is needed for the forces of evil to triumph is for enough good men to do nothing. - Edmond Burke The penalty good people pay for not being interested in politics is to be governed by people worse than themselves. - Plato pgp0.pgp Description: PGP signature
Re: [GENERAL] pg_autovacuum
On Tue, 2003-09-02 at 20:40, Adam Kavan wrote: And there is the problem, all of the counts stay at 0 no matter what I do. OK, so why is this happening... a bug in the stats system? Ignoring pg_autovaccu, what numbers do you get from the stats system when you do a: SELECT relname,n_tup_ins,n_tup_upd,n_tup_del from pg_stat_user_tables where relname = 'foobar'; both before and after your insert / update script. Matthew ---(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: [HACKERS] [GENERAL] identifying each individual tuple locked
I understand that ObjID of LOCKtag recognizes each individual row locked by a row level lock. BUt i have noticed that if i lock 2 different rows of the same table they have the same blkno. is this deduction ok? If they happen to be stored in the same block, they'd have the same blkno ... then what field can be used to recognize each individual row locked? thanks _ Help protect your PC: Get a free online virus scan at McAfee.com. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Pass parameters to a TCL function
Hello list,
I have a tcl trigger function called audit_log(varchar, bpchar), if I
write a trigger in a table
CREATE TRIGGER tg_test
AFTER INSERT OR UPDATE OR DELETE
ON xtable
FOR EACH ROW
EXECUTE PROCEDURE audit_log('xtable','internal');
it gives this error:
ERROR: CreateTrigger: function audit_log() does not exist
I also tried to make a kinda wrapper plpgsql trigger to call the
audit_log function but didn't work, any idea what's wrong?, how do I
pass parameters to a trigger function?
Thanks in advance,
Josue Maldonado.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[GENERAL] how to call a TCL function from within a plpgsql trigger?
Hi list, That's the question, anyone knows a way to do that? Thanks in advance, Josue Maldonado. ---(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] Pass parameters to a TCL function
I'm sorry, found my mistake, the way I'm calling the function is wrong
it should be audit_log ('xtable','internal'), it works.
Thanks
Josué Maldonado wrote:
Hello list,
I have a tcl trigger function called audit_log(varchar, bpchar), if I
write a trigger in a table
CREATE TRIGGER tg_test
AFTER INSERT OR UPDATE OR DELETE
ON xtable
FOR EACH ROW
EXECUTE PROCEDURE audit_log('xtable','internal');
it gives this error:
ERROR: CreateTrigger: function audit_log() does not exist
I also tried to make a kinda wrapper plpgsql trigger to call the
audit_log function but didn't work, any idea what's wrong?, how do I
pass parameters to a trigger function?
Thanks in advance,
Josue Maldonado.
---(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] Debian Woody 7.3.4 packages?
Martijn van Oosterhout [EMAIL PROTECTED] writes: Hi, Does anyone have any packages for 7.3.4 for debian woody? I'd like that too--Oliver Elphick has 7.3.2 but it doesn't look like he's had time to build 7.3.4. I may try to build it myself but probably won't get to it until later this week... -Doug ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] identifying each individual tuple locked
I understand that ObjID of LOCKtag recognizes each individual row locked by a row level lock. BUt i have noticed that if i lock 2 different rows of the same table they have the same blkno. is this deduction ok? thanks jenny _ Get MSN 8 and help protect your children with advanced parental controls. http://join.msn.com/?page=features/parental ---(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] identifying each individual tuple locked
Jenny - [EMAIL PROTECTED] writes: I understand that ObjID of LOCKtag recognizes each individual row locked by a row level lock. BUt i have noticed that if i lock 2 different rows of the same table they have the same blkno. is this deduction ok? If they happen to be stored in the same block, they'd have the same blkno ... regards, tom lane ---(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] identifying each individual tuple locked
I understand that ObjID of LOCKtag recognizes each individual row locked by a row level lock. BUt i have noticed that if i lock 2 different rows of the same table they have the same blkno. is this deduction ok? If they happen to be stored in the same block, they'd have the same blkno ... then how would we recognize each individual row locked? From: Tom Lane [EMAIL PROTECTED] To: Jenny - [EMAIL PROTECTED] CC: [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: [GENERAL] identifying each individual tuple locked Date: Tue, 02 Sep 2003 14:08:44 -0400 Jenny - [EMAIL PROTECTED] writes: I understand that ObjID of LOCKtag recognizes each individual row locked by a row level lock. BUt i have noticed that if i lock 2 different rows of the same table they have the same blkno. is this deduction ok? If they happen to be stored in the same block, they'd have the same blkno ... regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) _ MSN 8: Get 6 months for $9.95/month. http://join.msn.com/?page=dept/dialup ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
