Re: [GENERAL] Question regarding autovacuum in 8.1
Alvaro Herrera ha scritto: However i have no idea of what tables the autovacuum daemon is processing because there aren't autovacuum info columns on pg_stat_all_tables (as there are for 8.2.x). For that, you need to change log_min_messages to debug2. Keep track of the PID of autovacuum from the processing database message and see if you can spot an ERROR message from it. Ok. Thank you. Another question/idea: why don't put messages about what tables got vacuumed by the autovacuum daemon as normal log messages (instead of debug2)? I think it could be useful because in this way you can also know what tables are used more often then other... If i'm not wrong, the old autovacuum process in 7.4 and 8.0 did that... Denis
[GENERAL] \copy only select rows
Is there a way to do a dump of a database using a select statement? eg: \copy trd to 'file' select * from table limit 10 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] \copy only select rows
am Thu, dem 30.08.2007, um 14:59:06 +0800 mailte Ow Mun Heng folgendes: Is there a way to do a dump of a database using a select statement? A complete database or just a simple table? eg: \copy trd to 'file' select * from table limit 10 Since 8.2 you can use COPY (select * from table) TO 'file'. Other solution, use a regular UNIX-Shell and psql, a la echo select * from table limit 10; | psql database file 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] \copy only select rows
On Thu, 2007-08-30 at 09:14 +0200, A. Kretschmer wrote: am Thu, dem 30.08.2007, um 14:59:06 +0800 mailte Ow Mun Heng folgendes: Is there a way to do a dump of a database using a select statement? A complete database or just a simple table? a simple table.. couple million records, want some of them for testing.. say ~100pcs (right now, using insert into foo select * fromt able limit 10) eg: \copy trd to 'file' select * from table limit 10 Since 8.2 you can use COPY (select * from table) TO 'file'. superuser priviledge only right? Other solution, use a regular UNIX-Shell and psql, a la echo select * from table limit 10; | psql database file I want them to be in comma delimited format.. will the output be importable again using \copy into pg? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] What kind of locks does vacuum process hold on the db?
Why are you doing FULL vacuums? Is there some problem that regular vacuums
aren't solving?
Using dump/restore from a live DB to fresh DB, I get a DB that takes (49M +
12M - {I have two table spaces)) 61M of disk. Maximum size that I can grow by
the quota allocated to DB is 100M.
A regular vacuum doesn't stop the database growth, and DB grows beyond 100M.
Then we have to trigger a script that dump/restores on the live database. For
that we have a small outage (which is right now automated).
A full vacuum keeps the database below 100M and no outage.
Yes, vacuum full takes a hard lock on a table.
That means Table Level AccessExclusiveLock, right?
-Original Message-
From: Scott Marlowe [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 30, 2007 10:29 AM
To: Nitin Verma
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] What kind of locks does vacuum process hold on the db?
On 8/29/07, Nitin Verma [EMAIL PROTECTED] wrote:
What kind of locks does it hold on the db? Table level / Row level /
AccessExclusiveLock ?
Is there a document that details vacuum process, and its internals?
Use case: I am doing a vacuum {all full analyze} on a postgres 8.1 hosting
two user databases. For this I am using ${pg-home}/bin/vacuumdb, not direct
SQLs. Parallel to this I am running transaction on both user-DBs.
Observation:
Transactions don't pause while vacuum is running. (Odd, how come?)
Why are you doing FULL vacuums? Is there some problem that regular
vacuums aren't solving?
After this I tried two vacuums in parallel but those lock each other.
Yes, vacuum full takes a hard lock on a table.
Vacuum full is to be avoided.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Out of Memory - 8.2.4
On 8/29/07, Tom Lane [EMAIL PROTECTED] wrote: Alvaro Herrera [EMAIL PROTECTED] writes: I'm not having much luck really. I think the problem is that ANALYZE stores reltuples as the number of live tuples, so if you delete a big portion of a big table, then ANALYZE and then VACUUM, there's a huge misestimation and extra index cleanup passes happen, which is a bad thing. Yeah ... so just go with a constant estimate of say 200 deletable tuples per page? Note that it's much better to err on the smaller values. Extra index pass is really no problem. VACUUM getting Out of memory may not sound like a big problem, but the scary thing is - the last VACUUM's memory request may succeed and that means following queries start failing and that is big problem. -- marko ---(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] accessing PG using Perl:DBI
Hi all, I'm sure some of you guys do perl-dbi to access perl. need some pointers. (pg specific I guess) 1. Possible to execute queries to PG using multiple statemments? eg: prepare(A) bind_param($A) execute() prepare(BB) bind_param($B) execute() prepare(CC) bind_param($B) execute() right now, I found that this works.. prepare(A;BB;CC) but not sure how bind_param will work in this context 2. how do I perform a list of SQL using transactions. eg: like above, but wrap it into a transaction. Many Thanks ---(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_dump.... pg_restore...how long does it take?
Hi, Thanks for the answer. I've checked and the archive is corrupted. Every time I dump my database with pg_dump command mentioned in the previous post, I've got the same error. Could you please suggest me other compination of parameters for pg_dump, so that it dumps complete database (inluding functions, triggers, procedures, operators, sequences, tables, views etc. and obviously data)? It doesn't have to be stored in .tar archive. Moreover I'd like to inform that I work in Windows environment. Looking forward the answer. BR, Rafal Tom Lane-2 wrote: rafikoko [EMAIL PROTECTED] writes: I've got also the following message after dumping: pg_dump: [tar archiver] actual file length (4104361) does not match expected (4104361) which in fact does not make sense for me. Hmm, it looks like that code is printing the wrong variable's value. However, the condition it's complaining of shouldn't have happened, so you need to look more closely. I suspect you've got a corrupt tar archive. Does it look sane if you do tar tvf on it? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- View this message in context: http://www.nabble.com/pg_dump-pg_restore...how-long-does-it-take--tf4347124.html#a12402596 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(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] Parallel transactions to full vacuum
I have experimented vacuuming a table and running transaction on the same. It works fine without pause injection. Is that because of MVCC?
[GENERAL] Autovacuum not vacuuming pg_largeobject
I'm a bit concerned about the autovacuum daemon. Today I runned a vacuum full during a normal maintainance task and I noticed that the size of pg_largeobject decreased from 14GB to 4GB... Every night we have a procedure that deletes large object no more referenced using the vacuumlo program. This program issues delete commands to the pg_largeobject table in order to erase the rows of the los no more referenced. Autovacuum is up and running... but now i'm thinking it doesn't examine system tables such as pg_largeobject... Am I wrong? Thank you, Denis ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] lc_collate issue
I'm just looking for the correct workaround. While adding a new collation is the correct solution it's a lot of work. Even then pg can't use different collations anyway unless you reinit the datadir using initdb. One workaround is to cast the text value into a bytea value, and then it will be sorted in byte order no matter what locale you have. Like this: SELECT * FROM foo ORDER BY CAST (some_column AS BYTEA); This work except that there is no cast from text to bytea. But we can add one using: CREATE CAST (text AS bytea) WITHOUT FUNCTION AS ASSIGNMENT; I can't promise that WITHOUT FUNCTION will always work but currently bytea and text values are stored in the same way so it should work (and it probably will in future versions as well). /Dennis ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] pg_dump.... pg_restore...how long does it take?
Hi, Thanks for the answer. I've checked and the archive is corrupted. Every time I dump my database with pg_dump command mentioned in the previous post, I've got the same error. Could you please suggest me other compination of parameters for pg_dump, so that it dumps complete database (inluding functions, triggers, procedures, operators, sequences, tables, views etc. and obviously data)? It doesn't have to be stored in .tar archive. Moreover I'd like to inform that I work in Windows environment. Looking forward the answer. BR, Rafal Tom Lane-2 wrote: I suspect you've got a corrupt tar archive. Does it look sane if you do tar tvf on it? -- View this message in context: http://www.nabble.com/pg_dump-pg_restore...how-long-does-it-take--tf4347124.html#a12404132 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] pg_dump.... pg_restore...how long does it take?
Hi, Thanks for the answer. I've checked and the archive is corrupted. Every time I dump my database with pg_dump command mentioned in the previous post, I've got the same error. Could you please suggest me other compination of parameters for pg_dump, so that it dumps complete database (inluding functions, triggers, procedures, operators, sequences, tables, views etc. and obviously data)? It doesn't have to be stored in .tar archive. Moreover I'd like to inform that I work in Windows environment. Looking forward the answer. BR, rafikoko Tom Lane-2 wrote: rafikoko [EMAIL PROTECTED] writes: I've got also the following message after dumping: pg_dump: [tar archiver] actual file length (4104361) does not match expected (4104361) which in fact does not make sense for me. Hmm, it looks like that code is printing the wrong variable's value. However, the condition it's complaining of shouldn't have happened, so you need to look more closely. I suspect you've got a corrupt tar archive. Does it look sane if you do tar tvf on it? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- View this message in context: http://www.nabble.com/pg_dump-pg_restore...how-long-does-it-take--tf4347124.html#a12404040 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] What kind of locks does vacuum process hold on the db?
On 8/30/07, Nitin Verma [EMAIL PROTECTED] wrote:
Why are you doing FULL vacuums? Is there some problem that regular vacuums
aren't solving?
Using dump/restore from a live DB to fresh DB, I get a DB that takes (49M +
12M - {I have two table spaces)) 61M of disk. Maximum size that I can grow by
the quota allocated to DB is 100M.
A regular vacuum doesn't stop the database growth, and DB grows beyond 100M.
Then we have to trigger a script that dump/restores on the live database. For
that we have a small outage (which is right now automated).
A full vacuum keeps the database below 100M and no outage.
Then you aren't doing regular vacuum often enough and / or don't have
high enough fsm settings.
Yes, vacuum full takes a hard lock on a table.
That means Table Level AccessExclusiveLock, right?
Not sure which name it is. It definitely blocks writes to the table
while it is vacuuming it. But that's secondary. You're having to do
regular vacuum fulls because of too infrequent regular vacuum and / or
too low fsm setting.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[GENERAL] PostgreSQL with Kerberos and Active Directory
Hi everyone, I'm trying to configure PostgreSQL version 8.2.4 with Kerberos and Active Directory. The AD is run on a windows 2003 server, and the postgre on gentoo. The gentoo computer name is postgre and it's added to the windows 2003 server AD domain. I did the following: - I compiled postgre with kerberos support and installed it on the gentoo machine. - I created a keytab for the user postgres/postgre on the windows 2003 server machine and copied it to the gentoo machine. - I configured the postgresql.conf to point to the keytab. - I configured pg_hba.conf to authenticate remote users by kerberos. - I followed additional configurations from the howto in the mailing list archives. Now, when trying to log in with an AD user to postgre I get: psq: krb5_sendauth: Bad application version was sent (via sendauth) Any help will be appreciated. Thanks, Idan.
Re: [GENERAL] What kind of locks does vacuum process hold on the db?
Then you aren't doing regular vacuum often enough and / or don't have high
enough fsm settings.
Right now it's just default, can you please point me to a document that
elaborates on calculation of FSM for a given load (or to say averaged load)
I found ( http://www.varlena.com/GeneralBits/Tidbits/perf.html#maxfsmp ) but
does not have details.
# - Free Space Map -
#max_fsm_pages = 2 # min max_fsm_relations*16, 6 bytes
each
#max_fsm_relations = 1000 # min 100, ~70 bytes each
-Original Message-
From: Scott Marlowe [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 30, 2007 4:07 PM
To: Nitin Verma
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] What kind of locks does vacuum process hold on the db?
On 8/30/07, Nitin Verma [EMAIL PROTECTED] wrote:
Why are you doing FULL vacuums? Is there some problem that regular
vacuums
aren't solving?
Using dump/restore from a live DB to fresh DB, I get a DB that takes (49M +
12M - {I have two table spaces)) 61M of disk. Maximum size that I can grow
by
the quota allocated to DB is 100M.
A regular vacuum doesn't stop the database growth, and DB grows beyond
100M.
Then we have to trigger a script that dump/restores on the live database.
For
that we have a small outage (which is right now automated).
A full vacuum keeps the database below 100M and no outage.
Then you aren't doing regular vacuum often enough and / or don't have
high enough fsm settings.
Yes, vacuum full takes a hard lock on a table.
That means Table Level AccessExclusiveLock, right?
Not sure which name it is. It definitely blocks writes to the table
while it is vacuuming it. But that's secondary. You're having to do
regular vacuum fulls because of too infrequent regular vacuum and / or
too low fsm setting.
---(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] date of next Version 8.2 release
We would like to use the 8.2 version of postgres with our next software build. The next 8.2 version will contain a software patch which is critical to our needs. When is the next release of 8.2 expected to occur? As always, TIA. Paul Tilles ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] date of next Version 8.2 release
Hi, On Thu, 2007-08-30 at 08:03 -0400, Paul Tilles wrote: When is the next release of 8.2 expected to occur? http://archives.postgresql.org/pgsql-general/2007-08/msg01803.php Regards, -- Devrim GÜNDÜZ PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/ signature.asc Description: This is a digitally signed message part
Re: [GENERAL] PostgreSQL with Kerberos and Active Directory
We tried to connect from a different gentoo machine. both client and server are running version 8.2.4 of postgresql. right now, we are trying to connect from gentoo, but we want to connect from windows as well Idan On 8/30/07, Magnus Hagander [EMAIL PROTECTED] wrote: On Thu, Aug 30, 2007 at 02:07:13PM +0300, Idan Miller wrote: Hi everyone, I'm trying to configure PostgreSQL version 8.2.4 with Kerberos and Active Directory. The AD is run on a windows 2003 server, and the postgre on gentoo. The gentoo computer name is postgre and it's added to the windows 2003 server AD domain. I did the following: - I compiled postgre with kerberos support and installed it on the gentoo machine. - I created a keytab for the user postgres/postgre on the windows 2003 server machine and copied it to the gentoo machine. - I configured the postgresql.conf to point to the keytab. - I configured pg_hba.conf to authenticate remote users by kerberos. - I followed additional configurations from the howto in the mailing list archives. Now, when trying to log in with an AD user to postgre I get: psq: krb5_sendauth: Bad application version was sent (via sendauth) Any help will be appreciated. Are you sure you have postgresql 8.2 on both ends of the connection? Are yuor clients on windos or unix? //Magnus
[GENERAL] rules and rows affected
Hi all... I'm experimenting for the first time with rules to make a view updatable, but I've found a strange effect... I'll try to explain what I'm trying to achieve Suppose I have a table friends and a table hobbies. Each friend can be interested in one or more hobbies: CREATE TABLE friends( friend_id varchar(5) NOT NULL, friend_name varchar(40), CONSTRAINT friends_pkey PRIMARY KEY (friend_id)); CREATE TABLE hobbies ( hobby_id varchar(5) NOT NULL, hobby_desc varchar(40), CONSTRAINT hobbies_pkey PRIMARY KEY (hobby_ib)); CREATE TABLE friends_hobbies ( friend_id varchar(5) NOT NULL, hobby_id varchar(5) NOT NULL, CONSTRAINT friends_hobbies_pk PRIMARY KEY (friend_id, hobby_id); Now I use this view to show one friend with all hobbies and a boolean value that means interested (if present in friends_hobbies table) or not interested (if record is not present). CREATE OR REPLACE VIEW test_rule (friend_id, friend_name, hobby_id, hobby_desc, intersted) AS SELECT a.friend_id, b.friend_name, a.hobby_id, c.hobby_desc, true FROM friends_hobbies a JOIN friends b ON a.friend_id = b.friend_id JOIN hobbies c ON a.hobby_id = c.hobby_id UNION SELECT a.friend_id, a.friend_name, b.hobby_id, b.hobby_desc, false FROM friends a, hobbies b WHERE NOT b.hobby_id IN ( SELECT friends_hobbies.hobby_id FROM friends_hobbies WHERE friends_hobbies.friend_id = a.friend_id); So far so good. Now I'd like to make this view updatable, so that acting on the boolean flag you can insert a row in friends_hobbies (flag from false to true) or delete a row (flag from true to false); any other action on view (inserting or deleting or updating something different than flag) is refused. CREATE RULE test_rule_del AS ON DELETE TO test_rule DO INSTEAD NOTHING; CREATE RULE test_rule_ins AS ON INSERT TO test_rule DO INSTEAD NOTHING; CREATE RULE test_rule_upd AS ON UPDATE TO test_rule DO INSTEAD NOTHING; CREATE RULE test_rule_upd1 AS ON UPDATE TO test_rule WHERE new.interested old.interested AND new.interested = False DO INSTEAD DELETE FROM friends_hobbies WHERE friend_id = new.friend_id and hobby_id = new.hobby_id; CREATE RULE test_rule_upd2 AS ON UPDATE TO test_rule WHERE new.interested old.interested and new.interested = True DO INSTEAD INSERT INTO friends_hobbies (friend_id, hobby_id) VALUES (new.friend_id, new.hobby_id); Everything works perfectly... BUT the query returns always 0 rows affected, even if one record is inserted or deleted in friends_hobbies...why?? My development environment complaints that as there are no rows affected could be a potential concurrency conflict and rollbacks my update Is there a way to overcome this and obtain the real number of rows affected??? Thank you. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Out of Memory - 8.2.4
Marko Kreen [EMAIL PROTECTED] writes: Note that it's much better to err on the smaller values. Extra index pass is really no problem. I beg to differ ... What this may actually suggest is that autovacuum needs its own value of maintenance_work_mem, or that it should automatically divide the value by the authorized number of workers. regards, tom lane ---(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] Out of Memory - 8.2.4
Marko Kreen escribió: On 8/29/07, Tom Lane [EMAIL PROTECTED] wrote: Alvaro Herrera [EMAIL PROTECTED] writes: I'm not having much luck really. I think the problem is that ANALYZE stores reltuples as the number of live tuples, so if you delete a big portion of a big table, then ANALYZE and then VACUUM, there's a huge misestimation and extra index cleanup passes happen, which is a bad thing. Yeah ... so just go with a constant estimate of say 200 deletable tuples per page? Note that it's much better to err on the smaller values. Extra index pass is really no problem. Humm, is it? If you have a really big table (say, a hundred million tuples) and two indexes then you are not happy when vacuum must make two passes over the indexes. It may mean vacuum taking five hours instead of three with vacuum delay. Remember, you must scan each index *completely* each time. VACUUM getting Out of memory may not sound like a big problem, but the scary thing is - the last VACUUM's memory request may succeed and that means following queries start failing and that is big problem. Maybe what we should do is spill the TID list to disk instead. TODO for 8.4? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(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] Out of Memory - 8.2.4
Tom Lane escribió: Marko Kreen [EMAIL PROTECTED] writes: Note that it's much better to err on the smaller values. Extra index pass is really no problem. I beg to differ ... What this may actually suggest is that autovacuum needs its own value of maintenance_work_mem, or that it should automatically divide the value by the authorized number of workers. Hmm, this makes sense. -- Alvaro Herrera Valdivia, Chile ICBM: S 39º 49' 18.1, W 73º 13' 56.4 Cómo ponemos nuestros dedos en la arcilla del otro. Eso es la amistad; jugar al alfarero y ver qué formas se pueden sacar del otro (C. Halloway en La Feria de las Tinieblas, R. Bradbury) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Out of Memory - 8.2.4
Tom Lane escribió: Alvaro Herrera [EMAIL PROTECTED] writes: I'm not having much luck really. I think the problem is that ANALYZE stores reltuples as the number of live tuples, so if you delete a big portion of a big table, then ANALYZE and then VACUUM, there's a huge misestimation and extra index cleanup passes happen, which is a bad thing. Yeah ... so just go with a constant estimate of say 200 deletable tuples per page? How about we use a constant estimate using the average tuple width code? -- Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4 In fact, the basic problem with Perl 5's subroutines is that they're not crufty enough, so the cruft leaks out into user-defined code instead, by the Conservation of Cruft Principle. (Larry Wall, Apocalypse 6) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Question regarding autovacuum in 8.1
Denis Gasparin wrote: Another question/idea: why don't put messages about what tables got vacuumed by the autovacuum daemon as normal log messages (instead of debug2)? We did that for 8.3, actually. I think it could be useful because in this way you can also know what tables are used more often then other... Well, if you want to know which tables are used more, check the pg_stat views. -- Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J No hay cielo posible sin hundir nuestras raíces en la profundidad de la tierra(Malucha Pinto) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] why does a system catalog insert/update/delete not fire a trigger?
[EMAIL PROTECTED] As far as i know, the reason a trigger doesn't work on system catalog (e.g. DDL statements) is because it's not a INSERT SQL statement. It seems 'simple_heap_insert' is used for insert's. So i thought why not fire a trigger event after the simple_heap_insert or the index update's? What's wrong with fire a trigger event after/before the system catalog insert/update/delete? Is it impossible cause it affects data consistency in case the trigger crashes? If youre asking yourself why i need this: i already had some cases where this feature would be helpful. It's just a nice-to-have and i'm interested in the reasons ;) [EMAIL PROTECTED] ---(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] Removing pollution from log files
Andrus wrote: That's not pollution; it's telling you you need to fix your application to escape the backslashes differently. I havent seen that ODBC specification requires escaping strings. So this is task of ODBC driver. So complain to the ODBC guys. OTOH, maybe you are using ODBC wrongly. -- Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4 El destino baraja y nosotros jugamos (A. Schopenhauer) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Out of Memory - 8.2.4
Alvaro Herrera [EMAIL PROTECTED] writes: Tom Lane escribió: Yeah ... so just go with a constant estimate of say 200 deletable tuples per page? How about we use a constant estimate using the average tuple width code? I think that's overthinking the problem. The point here is mostly for vacuum to not consume 512MB (or whatever you have maintenance_work_mem set to) when vacuuming a ten-page table. I think that if we significantly increase the risk of having to make multiple index passes on medium-size tables, we'll not be doing anyone any favors. If we went with allocating MaxHeapTuplesPerPage slots per page (292 in CVS HEAD), 512MB would correspond to a bit over 300,000 pages, and you'd get memory savings for anything less than that. But that's already a 2GB table --- do you want to risk multiple index passes because you were chintzy with your memory allocation? Ultimately, the answer for a DBA who sees out of memory a lot is to reduce his maintenance_work_mem. I don't think VACUUM should be trying to substitute for the DBA's judgment. BTW, if an autovac worker gets an elog(ERROR) on one table, does it die or continue on with the next table? 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] Out of Memory - 8.2.4
On 8/30/07, Tom Lane [EMAIL PROTECTED] wrote: Marko Kreen [EMAIL PROTECTED] writes: Note that it's much better to err on the smaller values. Extra index pass is really no problem. I beg to differ ... Well, if Postgres tries to cut down passes by using max memory then admin is forced to cut down maint_mem for safety reasons... What this may actually suggest is that autovacuum needs its own value of maintenance_work_mem, or that it should automatically divide the value by the authorized number of workers. +1 Autovacuum having it's own value and cutting pieces from there and giving to vacuums is probably the easiest path. Then the per-backend maint_mem does not need to be large. -- marko ---(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] Out of Memory - 8.2.4
Tom Lane escribió: BTW, if an autovac worker gets an elog(ERROR) on one table, does it die or continue on with the next table? It continues with the next table if interrupted (SIGINT), but the worker exits on any other error. I would ask you to review that code -- it's in do_autovacuum, the PG_TRY block at the end. It was committed in rev 1.52 of autovacuum.c. -- Alvaro Herrerahttp://www.advogato.org/person/alvherre We are who we choose to be, sang the goldfinch when the sun is high (Sandman) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Out of Memory - 8.2.4
Alvaro Herrera [EMAIL PROTECTED] writes: Tom Lane escribió: BTW, if an autovac worker gets an elog(ERROR) on one table, does it die or continue on with the next table? It continues with the next table if interrupted (SIGINT), but the worker exits on any other error. Hmm, that seems backwards. What's the rationale? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Out of Memory - 8.2.4
Tom Lane escribió: Alvaro Herrera [EMAIL PROTECTED] writes: Tom Lane escribió: BTW, if an autovac worker gets an elog(ERROR) on one table, does it die or continue on with the next table? It continues with the next table if interrupted (SIGINT), but the worker exits on any other error. Hmm, that seems backwards. What's the rationale? I don't remember :-( We now use SIGTERM for shutdown, which closes the worker for good. It may well be that we need to change this so that the worker is shut down on specific error codes, instead of continuing on some other codes. Or it may need more thorough surgery. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(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] What kind of locks does vacuum process hold on the db?
On 8/30/07, Nitin Verma [EMAIL PROTECTED] wrote: Then you aren't doing regular vacuum often enough and / or don't have high enough fsm settings. Right now it's just default, can you please point me to a document that elaborates on calculation of FSM for a given load (or to say averaged load) Run a full vacuum / reindex to reclaim the lost space. Set up the autovacuum daemon to run. After a day or so, vacuum all your databases, and on the last one do a vacuum verbose. At the end you'll have a few lines like this: DETAIL: A total of 9612 page slots are in use (including overhead). 9612 page slots are required to track all free space. Current limits are: 153600 page slots, 1000 relations, using 965 kB. VACUUM So, I'm only using about 10,000 page slots out of a maximum of 153,600 slots. If the number of pages slots requires exceeds your current limits then you'll need to raise them. If not, then autovacuum is doing its job. ---(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_dump.... pg_restore...how long does it take?
rafikoko [EMAIL PROTECTED] writes: Could you please suggest me other compination of parameters for pg_dump, so that it dumps complete database (inluding functions, triggers, procedures, operators, sequences, tables, views etc. and obviously data)? It doesn't have to be stored in .tar archive. Moreover I'd like to inform that I work in Windows environment. If you don't have a very specific reason for using -Ft, don't --- the -Fc and plain-sql-script alternatives are much more commonly used and hence better debugged. -Ft also has some other problems like a need for extra temporary files. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Autovacuum not vacuuming pg_largeobject
Denis Gasparin [EMAIL PROTECTED] writes: I'm a bit concerned about the autovacuum daemon. Today I runned a vacuum full during a normal maintainance task and I noticed that the size of pg_largeobject decreased from 14GB to 4GB... Every night we have a procedure that deletes large object no more referenced using the vacuumlo program. This program issues delete commands to the pg_largeobject table in order to erase the rows of the los no more referenced. Autovacuum is up and running... but now i'm thinking it doesn't examine system tables such as pg_largeobject... Am I wrong? Yeah, you're wrong. The difference is that plain vacuum does not try very hard to reduce the length of a table file --- it just frees up space within the file for reuse. vacuum full will actually move things from the end of the file to free space nearer the head of the file, so that it can shorten the file. What I suspect the above observations really prove is you don't have max_fsm_pages set high enough, and so pg_largeobject was bloating because the free space was being forgotten instead of reused. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] rules and rows affected
Roberto Icardi [EMAIL PROTECTED] writes: Everything works perfectly... BUT the query returns always 0 rows affected, even if one record is inserted or deleted in friends_hobbies...why?? The rules for this are explained here: http://www.postgresql.org/docs/8.2/static/rules-status.html If you're replacing an update with a delete you're not going to get far with that. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Out of Memory - 8.2.4
Alvaro Herrera [EMAIL PROTECTED] writes: VACUUM getting Out of memory may not sound like a big problem, but the scary thing is - the last VACUUM's memory request may succeed and that means following queries start failing and that is big problem. Maybe what we should do is spill the TID list to disk instead. TODO for 8.4? I'm inclined to think that that'd be counterproductive. Searching the TID list has to be *fast*, else the index cleanup scans will take forever. It's probably better to have a smaller list and do two index passes than to try to do it in one pass using a list that doesn't fit in memory --- in the former case you're at least doing a seqscan of the index, rather than randomly faulting in different parts of the TID list. It's fairly likely that 512MB is already too high and is causing some swapping on the OP's machine ... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] why does a system catalog insert/update/delete not fire a trigger?
=?ISO-8859-15?Q?G=FCnther_Jedenastik?= [EMAIL PROTECTED] writes: What's wrong with fire a trigger event after/before the system catalog insert/update/delete? This has been discussed before, see the archives. 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] accessing PG using Perl:DBI
On Aug 30, 2007, at 4:03 AM, Ow Mun Heng wrote: 2. how do I perform a list of SQL using transactions. eg: like above, but wrap it into a transaction. assuming $dbh is your open handle to the database via DBI, then you do something like this: $dbh-begin_work() or die; $sth = $dbh-prepare(...) or die; $sth-execute() or die; ... more queries as needed... $dbh-commit() or die; Did you read the DBI manuals at all? It has examples. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Select question
Hi all, I am pretty sure I've done this before, but I am drawing a blank on how I did it or even what commands I need. Missing the later makes it hard to search. :P I've got Postfix working using PostgreSQL as the backend on a small, simple test database where I have a simple table called 'users' with a column called 'usr_email' which holds, surprisingly, the user's email address (ie: '[EMAIL PROTECTED]'). To tell Postfix where the user's email inbox is (to write incoming email to) I tell it to do this query: SELECT substring(usr_email FROM '@(.*)')||'/'||substring(usr_email FROM '(.*)@')||'/inbox' AS email_file FROM users WHERE usr_email='[EMAIL PROTECTED]'; Which returns: email_file - feneon.com/mkelly/inbox Now I want to move to a more complex database where the email name comes from 'users' - 'usr_email' (ie: 'mkelly') and the domain suffix comes from 'domains' - 'dom_name' (ie: 'test.com'). The problem is, I am limited to how I can tell Postfix to generate the query. Specifically, I can't (or don't know how to) tell Postfix to create a join or split the email address. I can only tell Postfix what table to query, what the SELECT field to use, and what column to do the WHERE on. So, my question, Can I create a 'virtual table' table (or some such) that would take something like?: SELECT email_file FROM virtual_table WHERE email_addy='[EMAIL PROTECTED]'; Where the email_addy can be split to create this query: SELECT b.dom_name||'/'||a.usr_email||'/inbox' AS email_file FROM users a, domains b WHERE a.usr_dom_id=b.dom_id AND a.usr_email='mkelly' AND b.dom_name='test.com'; Which would still return: email_file -- alteeve.com/mkelly/inbox I hope I got the question across well enough. :) Thanks all! Madi ---(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] Out of Memory - 8.2.4
Tom Lane escribió: Alvaro Herrera [EMAIL PROTECTED] writes: VACUUM getting Out of memory may not sound like a big problem, but the scary thing is - the last VACUUM's memory request may succeed and that means following queries start failing and that is big problem. Maybe what we should do is spill the TID list to disk instead. TODO for 8.4? I'm inclined to think that that'd be counterproductive. Searching the TID list has to be *fast*, else the index cleanup scans will take forever. It's probably better to have a smaller list and do two index passes than to try to do it in one pass using a list that doesn't fit in memory --- in the former case you're at least doing a seqscan of the index, rather than randomly faulting in different parts of the TID list. Maybe we could load it in a more compact form after the heap cleanup pass, instead of a plain TID list. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Out of shared memory (locks per process) using table-inheritance style partitioning
I've recently been developing for work a website backed by PostgreSQL, showing information about network flows. This data is extremely high volume in places, and I was hard pressed to come up with a good way to speed up data loading times until I came across the recommendation to use table inheritance for partitioning large tables. This data has a few strange sorts of features. One feature is that it generally comes in one hour chunks. The base data that's being analyzed comes in one hour segments. It's possible to narrow down to smaller segments, but you're still paying the I/O cost of processing a whole hour, so there's not much point. The second troublesome feature is that data isn't completely gathered together until some time after the time period it represents has passed. It is desirable to give a first 90%-good summary of what has happened as quickly as possible, and then to later replace it with a 100%-good summary. This combination lead me to make initial data partitions on one hour segments. These segments generally hold datapoints for five minute bins within the time period, with some hundreds of thousands of rows for each five minute bin. By using one hour partitions, the re-loading problem is easy. In a transaction, I drop the old table (foo_MMDDTHH, say) and create a new table of the same shape. I load the new data into it, create indices on it, and then make it a subtable of the partitioned table. So, what's the problem? Well—I have twelve tables that are partitioned by hour. There are 24 hours in a day, there are seven days in a week, and... you may see where I'm going here. PostgreSQL gets a lock on each individual table queried (in this case, every single partition) and it doesn't take very long at all for the maximum number of locks (~2240 by default) to be taken out, particularly when data is being aggregated across the twelve different partitioned tables. (Note that the partition tables are locked even when left out of the query by constraint exclusion.) That's by a SINGLE TRANSACTION, mind you, with in excess of 2000 tables locked. For our purposes, we have some tools to automatically re-collect these partitions. So, we'll be using cron jobs to take the hourly partitions for a day and turning them into a single daily partition, and then take the daily partitions for a week or a month and combining them further. This is somewhat undesirable, but not really avoidable. Even when doing this regularly, I think we're still going to need to increase the max_locks_per_transaction parameter. So, my question is this: This inheritance-based partitioning model is quite powerful, but the lock problem is serious. Even if I had an easy problem--a system with monthly partitions, working with two years' worth of data, say--I would potentially start having trouble somewhere between working with two and working with three partitioned tables (assuming the max_locks_per_transaction of 64 was really being used across most connections.) It's possible to mitigated the problem by being militant about partition management, and bumping up the max_locks_per_transaction numbers, but... it's still a very awkward sort of constraint. Are there plans in the works for a new partitioning system (either based on inheritance, or based on something else)? If there are, has any thought been put into how to avoid locking massive numbers of partitions? Thanks very much, John Prevost. ---(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] Select question
Woops, I wasn't careful enough when I wrote that email, sorry. The results showed my real domains instead of 'test.com'. I had different domains in the test and real DBs. Madison Kelly wrote: email_file - feneon.com/mkelly/inbox and email_file -- alteeve.com/mkelly/inbox *sigh* Should have shown: email_file --- test.com/mkelly/inbox I'll go get a coffee and wake up some more. :) Madi ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Autovacuum not vacuuming pg_largeobject
Yeah, you're wrong. The difference is that plain vacuum does not try very hard to reduce the length of a table file --- it just frees up space within the file for reuse. vacuum full will actually move things from the end of the file to free space nearer the head of the file, so that it can shorten the file. What I suspect the above observations really prove is you don't have max_fsm_pages set high enough, and so pg_largeobject was bloating because the free space was being forgotten instead of reused. I tried to issue the vacuum command on one of my database and i got the following log messages: LOG: max_fsm_relations(1000) equals the number of relations checked HINT: You have at least 1000 relations. Consider increasing the configuration parameter I suspect I must increase max_fsm_relations. The value of max_fsm_pages is 2 (the default value). I suspect I must change this to a higher value... but how high should this value be? Thank you in advance, Denis ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Out of Memory - 8.2.4
Alvaro Herrera [EMAIL PROTECTED] writes: Maybe we could load it in a more compact form after the heap cleanup pass, instead of a plain TID list. Hmm ... a non-lossy bitmap would be an interesting alternative. I think I went with bsearch mainly because I knew I could generate the TID list already sorted. Somebody should try to measure the probe time of the tidbitmap code against bsearch ... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Out of Memory - 8.2.4
Tom Lane escribió:
Alvaro Herrera [EMAIL PROTECTED] writes:
I'm not having much luck really. I think the problem is that ANALYZE
stores reltuples as the number of live tuples, so if you delete a big
portion of a big table, then ANALYZE and then VACUUM, there's a huge
misestimation and extra index cleanup passes happen, which is a bad
thing.
Yeah ... so just go with a constant estimate of say 200 deletable tuples
per page?
This seems the most reasonable approach for now. So this is the patch.
--
Alvaro Herrera http://www.PlanetPostgreSQL.org/
I call it GNU/Linux. Except the GNU/ is silent. (Ben Reiter)
Index: src/backend/commands/vacuumlazy.c
===
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/commands/vacuumlazy.c,v
retrieving revision 1.90
diff -c -p -r1.90 vacuumlazy.c
*** src/backend/commands/vacuumlazy.c 30 May 2007 20:11:57 - 1.90
--- src/backend/commands/vacuumlazy.c 30 Aug 2007 17:23:34 -
***
*** 11,20
* on the number of tuples and pages we will keep track of at once.
*
* We are willing to use at most maintenance_work_mem memory space to keep
! * track of dead tuples. We initially allocate an array of TIDs of that size.
! * If the array threatens to overflow, we suspend the heap scan phase and
! * perform a pass of index cleanup and page compaction, then resume the heap
! * scan with an empty TID array.
*
* We can limit the storage for page free space to MaxFSMPages entries,
* since that's the most the free space map will be willing to remember
--- 11,22
* on the number of tuples and pages we will keep track of at once.
*
* We are willing to use at most maintenance_work_mem memory space to keep
! * track of dead tuples. We initially allocate an array of TIDs of that size,
! * with an upper limit that depends on table size (this limit ensures we don't
! * allocate a huge area uselessly for vacuuming small tables). If the array
! * threatens to overflow, we suspend the heap scan phase and perform a pass of
! * index cleanup and page compaction, then resume the heap scan with an empty
! * TID array.
*
* We can limit the storage for page free space to MaxFSMPages entries,
* since that's the most the free space map will be willing to remember
***
*** 68,73
--- 70,81
#define REL_TRUNCATE_MINIMUM 1000
#define REL_TRUNCATE_FRACTION 16
+ /*
+ * Guesstimation of number of dead tuples per page. This is used to
+ * provide an upper limit to memory allocated when vacuuming small
+ * tables.
+ */
+ #define LAZY_ALLOC_TUPLES 200
typedef struct LVRelStats
{
*** lazy_space_alloc(LVRelStats *vacrelstats
*** 971,979
if (vacrelstats-hasindex)
{
! maxtuples = (maintenance_work_mem * 1024L) / sizeof(ItemPointerData);
maxtuples = Min(maxtuples, INT_MAX);
maxtuples = Min(maxtuples, MaxAllocSize / sizeof(ItemPointerData));
/* stay sane if small maintenance_work_mem */
maxtuples = Max(maxtuples, MaxHeapTuplesPerPage);
}
--- 979,990
if (vacrelstats-hasindex)
{
! /* no need to allocate more space than we have pages */
! maxtuples = LAZY_ALLOC_TUPLES * relblocks;
! maxtuples = Min(maxtuples, (maintenance_work_mem * 1024L) / sizeof(ItemPointerData));
maxtuples = Min(maxtuples, INT_MAX);
maxtuples = Min(maxtuples, MaxAllocSize / sizeof(ItemPointerData));
+
/* stay sane if small maintenance_work_mem */
maxtuples = Max(maxtuples, MaxHeapTuplesPerPage);
}
---(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] accessing PG using Perl:DBI
First, read the Perl DBI documentation that is relevant:
perldoc DBD::Pg
perldoc DBI
Your examples do not make sense. You prepare a SQL statement, not just
data. E.g.:
$sth = $dbh-prepare(INSERT INTO test3(nameval, boolval) VALUES (?, ?))
or die($sth-errstr);
foreach my $nm ('Joe', 'Fred', 'Sam') {
$sth-bind_param(1, $nm);
$sth-bind_param(2, 'true');
$sth-execute;
die($sth-errstr) if $sth-err;#very important to check for errors,
if RaiseError not set
print inserted $nm\n;
}
or
$sth = $dbh-prepare(INSERT INTO test3(nameval, boolval) VALUES (?, ?))
or die($sth-errstr);
foreach my $nm ('Joe', 'Fred', 'Sam') {
$sth-execute($nm, 'true');
die($sth-errstr) if $sth-err;
print inserted $nm\n;
}
whichever form you like better.
For a repeated SELECT:
$sth = $dbh-prepare(SELECT nameval, boolval from test3 where nameval =
?) or die($sth-errstr);
foreach my $nm ('Joe', 'Fred', 'Sam') {
$sth-execute($nm);
while (@data = $sth-fetchrow_array) {
print data: $data[0] $data[1]\n;
}
}
If I understood question 1, I don't see why you would even want to string
multiple SQL statements together. They can only be actually executed
serially anyway, one at a time. And, you really should check for errors
after each statement executed, too. Of course, you can use do instead
of prepare and execute for non-SELECT statements with no placeholders
(internally, it does the prepare/execute for you).
You only use bind_param if using placeholders in the prepared statement.
If you have varchar data in an INSERT or UPDATE, but are not using
placeholders, you need to use $dbh-quote($txtval) to properly escape
data, e.g.:
$bq=$dbh-quote('false');
foreach my $nm ('Joe', 'Fred', 'Sam') {
$nameq=$dbh-quote($nm);
$rows_affected = $dbh-do(INSERT into test3(nameval, boolval) VALUES
($nameq, $bq));
die($dbh-errstr) if (! $rows_affected);
print inserted $rows_affected row: $nm\n;
}
To wrap the whole thing in a transaction (this is a simple example, see
the perldoc documentation for a more robust example):
$dbh-{AutoCommit} = 0; #assuming that AutoCommit was previously set to
1
$sth = $dbh-prepare(INSERT INTO test3(nameval, boolval) VALUES (?, ?));
foreach my $nm ('Joe', 'Fred', 'Sam') {
$sth-execute($nm, 'true');
die($sth-errstr) if $sth-err;
print inserted $nm\n;
}
$dbh-commit;
Lots of examples are in the perldoc documentation.
Susan Cassidy
Ow Mun Heng [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
08/30/2007 01:07 AM
To
pgsql-general@postgresql.org
cc
Subject
[GENERAL] accessing PG using Perl:DBI
Hi all,
I'm sure some of you guys do perl-dbi to access perl. need some
pointers. (pg specific I guess)
1. Possible to execute queries to PG using multiple statemments?
eg:
prepare(A)
bind_param($A)
execute()
prepare(BB)
bind_param($B)
execute()
prepare(CC)
bind_param($B)
execute()
right now, I found that this works..
prepare(A;BB;CC)
but not sure how bind_param will work in this context
2. how do I perform a list of SQL using transactions. eg: like above,
but wrap it into a transaction.
Many Thanks
---(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
Tiered Data Protection Made Simple
http://www.overlandstorage.com/
Re: [GENERAL] Reliable and fast money transaction design
On Wed, Aug 29, 2007 at 10:22:32PM +0100, Gregory Stark wrote: mode. In fact I believe SERIALIZABLE mode is actually measurably faster in benchmarks but haven't run one in READ COMMITTED mode recently (for that reason). I think there's a reason why SERIALIZABLE could be slower, and that is that it's waiting on possibly-conflicting (but not actually conflicting) commits to happen in READ COMMITTED mode. No? Won't it have to check those things when it COMMITs? A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Out of Memory - 8.2.4
Alvaro Herrera [EMAIL PROTECTED] writes: ! /* no need to allocate more space than we have pages */ ! maxtuples = LAZY_ALLOC_TUPLES * relblocks; Uh ... you need to guard against integer overflow in this calculation. Perhaps leave the current initialization alone, and then after the Min-reduction steps add /* curious coding to ensure the multiplication can't overflow */ if ((BlockNumber) (maxtuples / LAZY_ALLOC_TUPLES) relblocks) maxtuples = relblocks * LAZY_ALLOC_TUPLES; which is demonstrably overflow-proof once maxtuples has been clamped to less than INT_MAX. regards, tom lane ---(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] Out of Memory - 8.2.4
Tom Lane [EMAIL PROTECTED] writes: Alvaro Herrera [EMAIL PROTECTED] writes: Maybe we could load it in a more compact form after the heap cleanup pass, instead of a plain TID list. Hmm ... a non-lossy bitmap would be an interesting alternative. I think I went with bsearch mainly because I knew I could generate the TID list already sorted. Somebody should try to measure the probe time of the tidbitmap code against bsearch ... Incidentally, there was a previous discussion about this a while back. I can't seem to find it in the archives though. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(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] Reliable and fast money transaction design
Andrew Sullivan [EMAIL PROTECTED] writes: I think there's a reason why SERIALIZABLE could be slower, and that is that it's waiting on possibly-conflicting (but not actually conflicting) commits to happen in READ COMMITTED mode. No? Won't it have to check those things when it COMMITs? SERIALIZABLE mode does not introduce any waits that wouldn't happen anyway. It only affects what happens after you stop waiting. The sequence is that if you go to update or delete a row, and you see there's already an uncommitted change on the row, you have to wait for that transaction to commit or roll back. If it rolls back, you can proceed. If it commits, then either throw an error (in SERIALIZABLE mode) or attempt to update/delete the newest tuple version (in READ COMMITTED mode). 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] date of next Version 8.2 release
On Thu, Aug 30, 2007 at 08:03:07AM -0400, Paul Tilles wrote: We would like to use the 8.2 version of postgres with our next software build. The next 8.2 version will contain a software patch which is critical to our needs. When is the next release of 8.2 expected to occur? Note that the difference between release and _STABLE from CVS is the quantity of pre-built stuff in the tarball. In my experience, using the current tip of _STABLE is as good as any dot-release. A -- Andrew Sullivan | [EMAIL PROTECTED] This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie ---(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
Solved! Was: Re: [GENERAL] Select question
Madison Kelly wrote: Hi all, I am pretty sure I've done this before, but I am drawing a blank on how I did it or even what commands I need. Missing the later makes it hard to search. :P I've got Postfix working using PostgreSQL as the backend on a small, simple test database where I have a simple table called 'users' with a column called 'usr_email' which holds, surprisingly, the user's email address (ie: '[EMAIL PROTECTED]'). To tell Postfix where the user's email inbox is (to write incoming email to) I tell it to do this query: SELECT substring(usr_email FROM '@(.*)')||'/'||substring(usr_email FROM '(.*)@')||'/inbox' AS email_file FROM users WHERE usr_email='[EMAIL PROTECTED]'; Which returns: email_file - feneon.com/mkelly/inbox Now I want to move to a more complex database where the email name comes from 'users' - 'usr_email' (ie: 'mkelly') and the domain suffix comes from 'domains' - 'dom_name' (ie: 'test.com'). The problem is, I am limited to how I can tell Postfix to generate the query. Specifically, I can't (or don't know how to) tell Postfix to create a join or split the email address. I can only tell Postfix what table to query, what the SELECT field to use, and what column to do the WHERE on. So, my question, Can I create a 'virtual table' table (or some such) that would take something like?: SELECT email_file FROM virtual_table WHERE email_addy='[EMAIL PROTECTED]'; Where the email_addy can be split to create this query: SELECT b.dom_name||'/'||a.usr_email||'/inbox' AS email_file FROM users a, domains b WHERE a.usr_dom_id=b.dom_id AND a.usr_email='mkelly' AND b.dom_name='test.com'; Which would still return: email_file -- alteeve.com/mkelly/inbox I hope I got the question across well enough. :) Thanks all! Madi ---(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 I got the answer from an Ian Peterson from the GTALUG. Thought I'd post the answer here, for the record. -=-=-=- CREATE VIEW email_file AS SELECT u.usr_email || '@' || d.dom_name AS email, d.dom_name || '/' || u.usr_email || '/inbox' AS file FROM users u JOIN domains d ON u.usr_dom_id=d.dom_id; -=-=-=- Which allows the query: -=-=-=- SELECT file FROM email_file WHERE email='[EMAIL PROTECTED]'; -=-=-=- To return: -=-=-=- file --- test.com/mkelly/inbox -=-=-=- Perfect! :) Madi ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Out of shared memory (locks per process) using table-inheritance style partitioning
John Prevost [EMAIL PROTECTED] writes: So, what's the problem? Well=97I have twelve tables that are partitioned by hour. There are 24 hours in a day, there are seven days in a week, and... you may see where I'm going here. PostgreSQL gets a lock on each individual table queried (in this case, every single partition) and it doesn't take very long at all for the maximum number of locks (~2240 by default) to be taken out, particularly when data is being aggregated across the twelve different partitioned tables. So what's the problem? Increase max_locks_per_transaction. The reason we have that as a tunable is mainly to support systems with very large numbers of tables. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Reliable and fast money transaction design
On Thu, Aug 30, 2007 at 02:21:56PM -0400, Tom Lane wrote: SERIALIZABLE mode does not introduce any waits that wouldn't happen anyway. It only affects what happens after you stop waiting. Ok, this makes me think I'm deeply confused about something. (Uh, well, on this specific topic. Anyone who's ever read any of my posts or talked to me for 10 seconds will attest that I'm deeply confused about plenty of things generally!) I had the impression that, when working in READ COMMITTED mode, you could see (for instance) _new_ rows that were INSERTed by others who were also doing work. In SERIALIZABLE, you couldn't. So in cases where the additional rows met criteria in your WHERE clause late in your transaction, SERIALIZABLE had to throw them away. For instance, in READ COMMITTED, in a longish transaction, the WHERE processed on IS NULL might match more rows than were available at the beginning of the transaction when you SELECTed them, but in SERIALIZABLE, you can't see those additional rows. Is that wrong? (If so, I'm going to have to spend some time working out clarifications for the manual.) And doesn't the SERIALIZABLE transaction have to figure out that this row doesn't count for me? A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Reliable and fast money transaction design
Andrew Sullivan [EMAIL PROTECTED] writes: I had the impression that, when working in READ COMMITTED mode, you could see (for instance) _new_ rows that were INSERTed by others who were also doing work. In SERIALIZABLE, you couldn't. So in cases where the additional rows met criteria in your WHERE clause late in your transaction, SERIALIZABLE had to throw them away. For instance, in READ COMMITTED, in a longish transaction, the WHERE processed on IS NULL might match more rows than were available at the beginning of the transaction when you SELECTed them, but in SERIALIZABLE, you can't see those additional rows. Is that wrong? (If so, I'm going to have to spend some time working out clarifications for the manual.) And doesn't the SERIALIZABLE transaction have to figure out that this row doesn't count for me? Sure, but so does READ COMMITTED. Both of them work with MVCC snapshot data structures that tell them which tuples to consider good and which were changed too recently to consider visible. The only difference is that SERIALIZABLE takes one snapshot at transaction start and works with that for the whole transaction, whereas READ COMMITTED takes a new snap for each statement. It's certainly true that a later snapshot might allow more tuples to be seen, but it might allow fewer as well. I don't see that that argument proves anything in general --- it'd all depend on details of how your application works. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Reliable and fast money transaction design
On Thu, Aug 30, 2007 at 03:32:40PM -0400, Tom Lane wrote: difference is that SERIALIZABLE takes one snapshot at transaction start and works with that for the whole transaction, whereas READ COMMITTED takes a new snap for each statement. Oh, I get it. This explains then why in principle READ COMMITTED oughta be faster in the absence of conflicts: additional snapshot checks are not needed? (Sorry to be obtuse. I think I had a backward mental picture of how this worked: like SERIALIZABLE did everything RC did, and then threw stuff away, or in any case did additional work to ensure a nearly-mathematical serializability.) A -- Andrew Sullivan | [EMAIL PROTECTED] The year's penultimate month is not in truth a good way of saying November. --H.W. Fowler ---(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] Metadata
Hello, I need constraint definitions like pg_indexes.indexdef , in order to drop / create them on the fly. BTW, where can I find a complete doc about Postgres Metadata ? - AV ---(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] Metadata
On Aug 30, 2007, at 2:45 PM, André Volpato wrote: Hello, I need constraint definitions like pg_indexes.indexdef , in order to drop / create them on the fly. BTW, where can I find a complete doc about Postgres Metadata ? - AV The system catalogs are in the manual appendix. However, for what your looking for, type \df pg_get* in psql and you see a listing of available functions that'll give you just what you're looking for and are documented in Section 9.19 of the manual (http://www.postgresql.org/docs/8.2/interactive/functions- info.html). Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Reliable and fast money transaction design
Andrew Sullivan [EMAIL PROTECTED] writes: On Thu, Aug 30, 2007 at 03:32:40PM -0400, Tom Lane wrote: difference is that SERIALIZABLE takes one snapshot at transaction start and works with that for the whole transaction, whereas READ COMMITTED takes a new snap for each statement. Oh, I get it. This explains then why in principle READ COMMITTED oughta be faster in the absence of conflicts: additional snapshot checks are not needed? To my mind it ought to be slower in principle: computing snapshots isn't free (though it's not tremendously expensive either, unless you have a huge number of active backends). The actual tuple visibility checks are going to be about the same speed either way, it's only a question of which snapshot you are using. Anyway, this is probably all down in the noise compared to the details of what is happening on the application level. If you end up having to retry a lot of serialization failures, or if you use stronger locking to avoid such failures (thereby losing concurrency), you'll more than swamp out any possible engine-level difference. I suspect that something of the sort is responsible for JD's experiences. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] auditing in postgresql
I know already it's possible to audit changes to data in postgresql tables using triggers, etc. A lot of other things can also be logged using the logging mechanism, such as permission errors (by logging all error messages), etc. However, there are also other things that would be useful to audit, such as data being _read_. For instance, if it's normal for a certain user to read data once per month (running a report, etc), and that same user reads the same data at an unexpected time, that may reveal a security problem. I could wrap the table in a SRF that emits a LOG, but that is not very elegant, and the SRF may not perform well because the query could not be optimized the same way. It would also be nice if there was a more unified and complete way of doing this stuff, rather than trying to separate the audit logs from the rest of the logs after the fact. And there is also no way to audit reads, for example, on all objects within a schema or tablespace. And the logging mechanism doesn't have a lot of conditionals, so it's hard to log only statements by privileged users. I'm sure this has been discussed before, so I'd appreciate links to discussions, etc. Regards, Jeff Davis ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Autovacuum not vacuuming pg_largeobject
Denis Gasparin wrote: Yeah, you're wrong. The difference is that plain vacuum does not try very hard to reduce the length of a table file --- it just frees up space within the file for reuse. vacuum full will actually move things from the end of the file to free space nearer the head of the file, so that it can shorten the file. What I suspect the above observations really prove is you don't have max_fsm_pages set high enough, and so pg_largeobject was bloating because the free space was being forgotten instead of reused. I tried to issue the vacuum command on one of my database and i got the following log messages: LOG: max_fsm_relations(1000) equals the number of relations checked HINT: You have at least 1000 relations. Consider increasing the configuration parameter I suspect I must increase max_fsm_relations. The value of max_fsm_pages is 2 (the default value). I suspect I must change this to a higher value... but how high should this value be? First increase max_fsm_relations to more than the number of tables,etc in your system. Then, after it's running a while, do the same vacuum command and see if it gives you a hint to increase that parameter too. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] auditing in postgresql
On Thu, 2007-08-30 at 15:39 -0600, Guy Fraser wrote: Below is the logging section from the postgresql.conf file. It would appear that you can configure PostgreSQL to log as much detail as you want to where you want. You can then write a program to parse the log file and present the information you want based on your needs. I do something similar with a different application which I have configured to use syslog. In syslog I direct the logging data to a pipe which I read as a stream from an application I wrote, that processes the realtime activity and extracts the useful information which I send to an SQL database for further processing on a batch basis. Capturing everything possible via logging and filtering/processing later was a consideration of mine. It might work, but it's not ideal. I'm a little concerned about it for a few reasons: 1. Performance (although I haven't measured) 2. Trying to figure out which tables are actually being read by grepping the logs is a mess. What if someone makes a rule/view/function over the table (and they have read permissions on the table), and then reads from that? There may even be built-in functions that could accomplish that as long as the user has read access to the table. 3. I'd have to have the schema or tablename unique enough that filtering wouldn't get false positives. Solvable, but not an elegant solution either. My concern is that logging is for logging, not auditing. There's some overlap, but logging doesn't seem to do everything that I need directly. Regards, Jeff Davis ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] auditing in postgresql
On 8/30/07, Jeff Davis [EMAIL PROTECTED] wrote: I know already it's possible to audit changes to data in postgresql tables using triggers, etc. A lot of other things can also be logged using the logging mechanism, such as permission errors (by logging all error messages), etc. However, there are also other things that would be useful to audit, such as data being _read_. For instance, if it's normal for a certain user to read data once per month (running a report, etc), and that same user reads the same data at an unexpected time, that may reveal a security problem. I could wrap the table in a SRF that emits a LOG, but that is not very elegant, and the SRF may not perform well because the query could not be optimized the same way. It would also be nice if there was a more unified and complete way of doing this stuff, rather than trying to separate the audit logs from the rest of the logs after the fact. And there is also no way to audit reads, for example, on all objects within a schema or tablespace. And the logging mechanism doesn't have a lot of conditionals, so it's hard to log only statements by privileged users. Well, a SRF may be unsuitable for various reasons, but maybe views are better. I really like views more and more lately (better than functions as a rule, I think). you have some query, select yadda create view log_yadda as select yadda union all select null, null, null from log_func(); This is a nearly free invocation but not perfect...a limit clause can prevent log_func from executing in some cases for example. I'm looking for a better way to express this. merlin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Select question
On 8/30/07, Madison Kelly [EMAIL PROTECTED] wrote: Hi all, I am pretty sure I've done this before, but I am drawing a blank on how I did it or even what commands I need. Missing the later makes it hard to search. :P I've got Postfix working using PostgreSQL as the backend on a small, simple test database where I have a simple table called 'users' with a column called 'usr_email' which holds, surprisingly, the user's email address (ie: '[EMAIL PROTECTED]'). To tell Postfix where the user's email inbox is (to write incoming email to) I tell it to do this query: SELECT substring(usr_email FROM '@(.*)')||'/'||substring(usr_email FROM '(.*)@')||'/inbox' The problem is, I am limited to how I can tell Postfix to generate the query. Specifically, I can't (or don't know how to) tell Postfix to create a join or split the email address. I can only tell Postfix what table to query, what the SELECT field to use, and what column to do the WHERE on. I seem to recall giving out a query about that in the IRC channel a while back...so if you got it from me, now I'll attempt to finish the job :-). If you can get postfix to look at a view, maybe you could CREATE VIEW email_v AS SELECT usr_email, dom_name, b.dom_name||'/'||a.usr_email||'/inbox' AS email_file FROM users a, domains b WHERE a.usr_dom_id=b.dom_id; AND a.usr_email='mkelly' AND b.dom_name='test.com'; and just select * from email_v where usr_email = 'mkelly' and dom_name = 'test.com'; merlin ---(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] E_BAD_ACCESS with palloc/pfree in base type
Hello all!
I'm working on a new base type. It's apparently installing fine via
PG_XS. I'm using malloc and free for memory management within the
module code.
x=# select '+'::x_type.x_type;
x_type
+
(1 row)
x=# select '+'::x_type.x_type;
x_type
---
+
(1 row)
It was recommended that I use palloc and pfree instead, so I replaced
malloc/free with palloc/pfree. However, now I'm getting E_BAD_ACCESS
errors (determined by stepping through the code with gdb) and
backends dying on me on input.
x=# select '+'::x_type.x_type;
x_type
+
(1 row)
x=# select '+'::x_type.x_type;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!
I suspect it's something straightforward as not only is this the
first base type I've worked on, I'm also not very experienced with C.
It was suggested that perhaps I'm having a problem as I'm not
defining a memory context. I thought that the memory context of the
calling code would be used by default. Is this the case? I've
included portions of the code below. I'd be happy to provide more
details if it would help.
Any ideas as to what's going on? Any comments or suggestions very
much appreciated.
Michael Glaesemann
grzm seespotcode net
--
--- x_type_base.c: X_TYPE_BASE_CHECK is not defined when compiling
with USE_PGXS
#ifndef X_TYPE_BASE_CHECK
#define free pfree
#define malloc palloc
#endif
/*
* xTypeFromString takes a string as its second argument and
* assigns to its first argument the XType value represented
* by the string. If the assignment is successful, xTypeFromString
* returns true, and false otherwise.
*/
static inline
XTypeParseResult xTypeFromString (XType * aNumber, char * aString)
{
char * theDigits = malloc(sizeof(XTypeMaximumStringLength));
XTypeCountryCode * countryCode = malloc(sizeof(XTypeCountryCode));
char * subscriberNumber = malloc(XTypeMaximumStringLength);
XTypeParseResult xTypeParseResult;
xTypeParseResult = parseXTypeString(aString, theDigits,
countryCode, subscriberNumber);
free(subscriberNumber);
if (XTypeNoParseError == xTypeParseResult)
{
XType xTypeResult;
char *cp;
initializeXTypeWithCountryCode(xTypeResult, countryCode);
xTypeResult = (xTypeResult | strtoll(theDigits, cp, 10));
*aNumber = xTypeResult;
}
free(countryCode);
free(theDigits);
return xTypeParseResult;
}
--- x_type.c includes x_type_base.c
#define DatumGetXTypeP(X) ((XType *) DatumGetPointer(X))
#define XTypePGetDatum(X) PointerGetDatum(X)
#define PG_GETARG_X_TYPE_P(X) DatumGetXTypeP(PG_GETARG_DATUM(X))
#define PG_RETURN_X_TYPE_P(X) return XTypePGetDatum(X)
#define PG_GETARG_X_TYPE(X) PG_GETARG_INT64((int64) X)
#define PG_RETURN_X_TYPE(X) PG_RETURN_INT64((int64) X)
PG_FUNCTION_INFO_V1(x_type_in);
Datum
x_type_in(PG_FUNCTION_ARGS)
{
char * theString = PG_GETARG_CSTRING(0);
XType theNumber;
XTypeParseResult parseResult = xTypeFromString(theNumber,
theString);
if (XTypeNoParseError == parseResult)
{
XType * numberResult = palloc(sizeof(XType));
*numberResult = theNumber;
PG_RETURN_X_TYPE_P(numberResult);
}
else
handleXTypeParseError(parseResult, theString);
}
PG_FUNCTION_INFO_V1(x_type_out);
Datum
x_type_out(PG_FUNCTION_ARGS)
{
XType * theNumber = PG_GETARG_X_TYPE_P(0);
char * theString = palloc(XTypeMaximumStringLength + 1);
(void) stringFromXType(theString, theNumber,
XTypeMaximumStringLength);
PG_RETURN_CSTRING(theString);
}
---(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
