Re: [GENERAL] PANIC: corrupted item pointer
Am 06.04.2012 23:49, schrieb Jeff Davis: No, i didn't found any in my postgresql dirs. Should i have a core file around when i see a segmentation fault? What should i look for? It's an OS setup thing, but generally a crash will generate a core file if it is allowed to. Use ulimit -c unlimited on linux in the shell that starts postgresql and I think that will work. You can test it by manually doing a kill -11 on the pid of a backend process. My system was setup with $ cat /proc/32741/limits Limit Soft Limit Hard Limit Units ... Max core file size0unlimited bytes ... to bad, no core dump. I will follow instructions on peters blog here http://petereisentraut.blogspot.de/2011/06/enabling-core-files-for-postgresql-on.html So next time i'll be ready to handle this issue. Thanks a lot for your help, jeff. regards Janning -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PANIC: corrupted item pointer
Thank you so much for still helping me... Am 30.03.2012 20:24, schrieb Jeff Davis: On Fri, 2012-03-30 at 16:02 +0200, Janning Vygen wrote: The PANIC occurred first on March, 19. My servers uptime ist 56 days, so about 4th of February. There was no power failure since i started to use this machine. This machine is in use since March, 7. I checked it twice: Now power failure. Just to be sure: the postgres instance didn't exist before you started to use it, right? I don't really understand your question, but it was like this: The OS was installed a few days before, the i installed the postgresql instance. I configured my setup with a backup server by WAL archiving. Then i tested some things and i played around with pg_reorg (but i didn't use ist till then) then i dropped the database, shut down my app, installed a fresh dump and restarted the app. Did you get the PANIC and WARNINGs on the primary or the replica? It might be worth doing some comparisons between the two systems. It only happend on my primary server. My backup server has no suspicious log entries. Do you have a full copy of the two data directories? It might be worth exploring the differences there, but that could be a tedious process. Is it still worth to make the copy now? At the moment everything is running fine. It is pretty obvious to me the segmentation fault is the main reason for getting the PANIC afterwards. What can cause a segmentation fault? Is there anything to analyse further? It's clear that they are connected, but it's not clear that it was the cause. To speculate: it might be that disk corruption caused the segfault as well as the PANICs. Do you have any core files? No, i didn't found any in my postgresql dirs. Should i have a core file around when i see a segmentation fault? What should i look for? Can you get backtraces? I have never done it before. But as everything runs fine at the moment it's quite useless, isn't it? regards Janning Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PANIC: corrupted item pointer
Hi, thanks so much for answering. I found a segmentation fault in my logs so please check below: On Tue, 2012-03-27 at 11:47 +0200, Janning Vygen wrote: I am running postgresql-9.1 from debian backport package fsync=on full_page_writes=off That may be unsafe (and usually is) depending on your I/O system and filesystem. However, because you didn't have any power failures, I don't think this is the cause of the problem. I think i should switch to full_page_writes=on. But as my harddisk are rather cheap, so I used to tune it to get maximum performance. These WARNINGs below could also be caused by a power failure. Can you verify that no power failure occurred? E.g. check uptime, and maybe look at a few logfiles? The PANIC occurred first on March, 19. My servers uptime ist 56 days, so about 4th of February. There was no power failure since i started to use this machine. This machine is in use since March, 7. I checked it twice: Now power failure. But i found more strange things, so let me show you a summary (some things were shortened for readability) 1. Segmentation fault Mar 13 19:01 LOG: server process (PID 32464) was terminated by signal 11: Segmentation fault Mar 13 19:01 FATAL: the database system is in recovery mode Mar 13 19:01 LOG: unexpected pageaddr 22/8D402000 in log file 35, segment 208, offset 4202496 Mar 13 19:01 LOG: redo done at 23/D0401F78 Mar 13 19:01 LOG: last completed transaction was at log time 2012-03-13 19:01:58.667779+01 Mar 13 19:01 LOG: checkpoint starting: end-of-recovery immediate 2. PANICS Mar 19 22:14 PANIC: corrupted item pointer: offset = 21248, size = 16 Mar 20 23:38 PANIC: corrupted item pointer: offset = 21248, size = 16 Mar 21 23:30 PANIC: corrupted item pointer: offset = 21248, size = 16 Mar 23 02:10 PANIC: corrupted item pointer: offset = 21248, size = 16 Mar 24 06:12 PANIC: corrupted item pointer: offset = 21248, size = 16 Mar 25 01:28 PANIC: corrupted item pointer: offset = 21248, size = 16 Mar 26 22:16 PANIC: corrupted item pointer: offset = 21248, size = 16 Mar 27 09:17 PANIC: corrupted item pointer: offset = 21248, size = 16 Mar 27 09:21 PANIC: corrupted item pointer: offset = 21248, size = 16 Mar 27 09:36 PANIC: corrupted item pointer: offset = 21248, size = 16 Mar 27 09:48 PANIC: corrupted item pointer: offset = 21248, size = 16 Mar 27 10:01 PANIC: corrupted item pointer: offset = 21248, size = 16 What I additionally see, that my table rankingentry was not autovacuumed anymore after the first PANIC on March,19. But it was still autovacuumed after segmentation fault without error. 3. Then I rebuilt all index on this table, dropped old indexes, and did run vacuum on this table: WARNING: relation rankingentry page 424147 is uninitialized --- fixing WARNING: relation rankingentry page 424154 is uninitialized --- fixing WARNING: relation rankingentry page 424155 is uninitialized --- fixing WARNING: relation rankingentry page 424166 is uninitialized --- fixing WARNING: relation rankingentry page 424167 is uninitialized --- fixing WARNING: relation rankingentry page 424180 is uninitialized --- fixing After this everything is running just fine. No more problems, just headache. Did you get the PANIC and WARNINGs on the primary or the replica? It might be worth doing some comparisons between the two systems. It only happend on my primary server. My backup server has no suspicious log entries. It is pretty obvious to me the segmentation fault is the main reason for getting the PANIC afterwards. What can cause a segmentation fault? Is there anything to analyse further? kind regards Janning -- Kicktipp GmbH Venloer Straße 8, 40477 Düsseldorf Sitz der Gesellschaft: Düsseldorf Geschäftsführung: Janning Vygen Handelsregister Düsseldorf: HRB 55639 http://www.kicktipp.de/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PANIC: corrupted item pointer
Hi, I am running postgresql-9.1 from debian backport package fsync=on full_page_writes=off I didn't had any power failures on this server. Now I got this: 1. Logfile PANIC postgres[27352]: [4-1] PANIC: corrupted item pointer: offset = 21248, size = 16 postgres[27352]: [4-2] STATEMENT: insert into RankingEntry (rankingentry_mitglied_name, rankingentry_spieltagspunkte, rankingentry_gesamtpunkte, rankingentry_spieltagssiege, rankingentry_spieltagssieger, tippspieltag_id, mitglied_id) values ($1, $2, $3, $4, $5, $6, $7) postgres[26286]: [2-1] LOG: server process (PID 27352) was terminated by signal 6: Aborted postgres[26286]: [3-1] LOG: terminating any other active server processes 2. All my database connections are closed after this log entry 3. My Application is throwing lots of java.io.EOFException because of this. Sometimes i get exactly the same behaviour but without no.1. So there is no PANIC logged but all connections are closed suddenly with an EOFException I searched the archive and found http://archives.postgresql.org/pgsql-general/2007-06/msg01268.php So I first reindexed all indexes on table rankingentry concurrently and replaced the old ones. No errors. Then I run VACUUM rankingentry and i got: kicktipp=# VACUUM rankingentry ; WARNING: relation rankingentry page 424147 is uninitialized --- fixing WARNING: relation rankingentry page 424154 is uninitialized --- fixing WARNING: relation rankingentry page 424155 is uninitialized --- fixing WARNING: relation rankingentry page 424166 is uninitialized --- fixing WARNING: relation rankingentry page 424167 is uninitialized --- fixing WARNING: relation rankingentry page 424180 is uninitialized --- fixing VACUUM Time: 138736.347 ms Now I restarted my process which issued the insert statement which caused the server panic. Everything runs fine now. I am worried because i never had any error like this with postgresql. I just switched to 9.1 and started to have a hot standby server (WAL shipping). Does this error has any relation to this? Should I check or exchange my hardware? Is it a hardware problem? Should I still worry about it? regards Janning -- Kicktipp GmbH Venloer Straße 8, 40477 Düsseldorf Sitz der Gesellschaft: Düsseldorf Geschäftsführung: Janning Vygen Handelsregister Düsseldorf: HRB 55639 http://www.kicktipp.de/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Anonymized database dumps
pgcrypto does not work for this scenario as far as i know. pgcrypto enables me to encrypt my data and let only a user with the right password (or key or whatever) decrypt it, right? So if i run it in a test environment without this password the application is broken. I still want to use these table columns in my test environment but instead of real email addresses i want addresses like random_num...@example.org. You might be right that it is a good idea to additional encrypt this data. regards Janning Am 19.03.2012 06:24, schrieb Kiriakos Georgiou: I would store sensitive data encrypted in the database. Check the pgcrypto module. Kiriakos On Mar 18, 2012, at 1:00 PM, Janning Vygen wrote: Hi, I am working on postgresql 9.1 and loving it! Sometimes we need a full database dump to test some performance issues with real data. Of course we don't like to have sensible data like bunches of e-mail addresses on our development machines as they are of no interest for developers and should be kept secure. So we need an anonymized database dump. I thought about a few ways to achieve this. 1. Best solution would be a special db user and some rules which fire on reading some tables and replace privacy data with some random data. Now doing a dump as this special user doesn't even copy the sensible data at all. The user just has a different view on this database even when he calls pg_dump. But as rules are not fired on COPY it can't work, right? 2. The other solution I can think of is something like pg_dump | sed pgdump_anon where 'sed' does a lot of magical replace operations on the content of the dump. I don't think this is going to work reliable. 3. More reliable would be to dump the database, restore it on a different server, run some sql script which randomize some data, and dump it again. hmm, seems to be the only reliable way so far. But it is no fun when dumping and restoring takes an hour. Does anybody has a better idea how to achieve an anonymized database dump? regards Janning -- Kicktipp GmbH Venloer Straße 8, 40477 Düsseldorf Sitz der Gesellschaft: Düsseldorf Geschäftsführung: Janning Vygen Handelsregister Düsseldorf: HRB 55639 http://www.kicktipp.de/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Kicktipp GmbH Venloer Straße 8, 40477 Düsseldorf Sitz der Gesellschaft: Düsseldorf Geschäftsführung: Janning Vygen Handelsregister Düsseldorf: HRB 55639 http://www.kicktipp.de/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Anonymized database dumps
Am 19.03.2012 um 13:22 schrieb Bill Moran wmo...@potentialtech.com: In response to Janning Vygen vy...@kicktipp.de: I am working on postgresql 9.1 and loving it! Sometimes we need a full database dump to test some performance issues with real data. Of course we don't like to have sensible data like bunches of e-mail addresses on our development machines as they are of no interest for developers and should be kept secure. So we need an anonymized database dump. I thought about a few ways to achieve this. 1. Best solution would be a special db user and some rules which fire on reading some tables and replace privacy data with some random data. Now doing a dump as this special user doesn't even copy the sensible data at all. The user just has a different view on this database even when he calls pg_dump. But as rules are not fired on COPY it can't work, right? 2. The other solution I can think of is something like pg_dump | sed pgdump_anon where 'sed' does a lot of magical replace operations on the content of the dump. I don't think this is going to work reliable. 3. More reliable would be to dump the database, restore it on a different server, run some sql script which randomize some data, and dump it again. hmm, seems to be the only reliable way so far. But it is no fun when dumping and restoring takes an hour. Does anybody has a better idea how to achieve an anonymized database dump? I highly recommend #3. It's how we do it where I work. At first it seems like a big, slow, complicated monster, but once you've built the tools and have it running reliably it's very nice. Our system does the dumps overnight via cron (we have over 100 production databases) then changes the sensitive data, as well changing all the passwords to password so developers can easily log in as any account. During the day, the developers have access to all the sanitized dump files and can use them to make as many testing databases as they need. Yes, the data gets up to 24 hours out of date, but it's never been a problem for us. Thanks for your response and your insights to your process. Sounds reasonable. Regards Janning -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Anonymized database dumps
Hi, I am working on postgresql 9.1 and loving it! Sometimes we need a full database dump to test some performance issues with real data. Of course we don't like to have sensible data like bunches of e-mail addresses on our development machines as they are of no interest for developers and should be kept secure. So we need an anonymized database dump. I thought about a few ways to achieve this. 1. Best solution would be a special db user and some rules which fire on reading some tables and replace privacy data with some random data. Now doing a dump as this special user doesn't even copy the sensible data at all. The user just has a different view on this database even when he calls pg_dump. But as rules are not fired on COPY it can't work, right? 2. The other solution I can think of is something like pg_dump | sed pgdump_anon where 'sed' does a lot of magical replace operations on the content of the dump. I don't think this is going to work reliable. 3. More reliable would be to dump the database, restore it on a different server, run some sql script which randomize some data, and dump it again. hmm, seems to be the only reliable way so far. But it is no fun when dumping and restoring takes an hour. Does anybody has a better idea how to achieve an anonymized database dump? regards Janning -- Kicktipp GmbH Venloer Straße 8, 40477 Düsseldorf Sitz der Gesellschaft: Düsseldorf Geschäftsführung: Janning Vygen Handelsregister Düsseldorf: HRB 55639 http://www.kicktipp.de/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Client hangs in socket read
Hi, we have some trouble with a few cronjobs running inside a tomcat webapp. The problem is exactly described here by David Hustace david(at)opennms(dot)org: but wasn't solved, it was just recognized as weired. http://archives.postgresql.org/pgsql-jdbc/2006-01/msg00115.php In short: we are running some jobs nightly and sometimes on of those threads just hangs forever at at java.net.SocketInputStream.socketRead0(Native Method) I happens every week or so. We are running PostgreSQL 9.0.4 on the server side. At the moment the thread starts hanging we see some unexpected EOF on client connection But i don't know if these EOF messages really relate to it. On the client side we run postgresql JDBC 9.0-801.jdbc3 c3p0 Version 0.9.1.2 with c3p0.minPoolSize = 10 c3p0.maxPoolSize = 110 c3p0.checkoutTimeout = 5000 c3p0.maxConnectionAge = 86400 c3p0.maxStatementsPerConnection = 500 c3p0.acquireIncrement =10 on a hibernate/spring stack It seems like the client is waiting for postgresql sending data, but postgresql had already closed the connection due to whatever reason and the client didn't recognize it. I can't see any running transaction in my DB corresponding to the waiting thread. My options are as far as i know: - check stale connections in c3p0 before checkout - set a statement timeout on postgresql server - set a socket timeout with my jdbc driver - using a different connection pool like bonecp But at the moment i can't figure out why strange things like this happen at all. Is it just a network error and i need a socket timeout? Or is it a bug inside postgresql server (doubt that!)? Or is my connection pool or my app buggy? Is it possible that a router between tomcat and DB server is dropping the connection? I have no clue. Does anybody know how to fix my problem? regards Janning -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Query slower if i add an additional order parameter
Hi, postgresql 8.4 (tuned, analyzed, and so on) we had trouble with one query executing too slow. After checking out some alternatives we encountered that dropping a rather useless second parameter on order by the execution time dropped dramatically. This is our original query with 2 order parameters: select * from Forum where id=33591 order by datum desc, id limit 11; This is the explain analyze output: QUERY PLAN -- Limit (cost=23478.37..23478.39 rows=11 width=229) (actual time=31.830..31.834 rows=11 loops=1) - Sort (cost=23478.37..23516.27 rows=15163 width=229) (actual time=31.828..31.830 rows=11 loops=1) Sort Key: datum, id Sort Method: top-N heapsort Memory: 29kB - Bitmap Heap Scan on forum (cost=235.88..23140.27 rows=15163 width=229) (actual time=5.444..23.409 rows=15173 loops=1) Recheck Cond: (id = 33591) - Bitmap Index Scan on ix_id_datum (cost=0.00..232.08 rows=15163 width=0) (actual time=3.290..3.290 rows=15173 loops=1) Index Cond: (id = 33591) Total runtime: 31.887 ms The second parameter is not really needed. It is just given to be sure we get all records in a predefined order. But it happened only once in a few years seeing the same timestamp in two records. So we tried to drop this parameter (never thought it would change anything) This is our query with only 1 order parameter select * from Forum where id=33591 order by datum desc limit 11; This is the explain analyze output: QUERY PLAN - Limit (cost=0.00..20.66 rows=11 width=229) (actual time=0.039..0.065 rows=11 loops=1) - Index Scan Backward using ix_forum_id_datum on forum (cost=0.00..28482.92 rows=15163 width=229) (actual time=0.038..0.061 rows=11 loops=1) Index Cond: (id = 33591) Total runtime: 0.098 ms (4 rows) we did an ANALYZE before and executed this query many times to be sure that disk cache is in place. the second query is 300 times faster!! So I do not have a question, because for me its fine, running this query without ordering by id. I just would like to help making postgresql even better. I think postgresql could be smart enough to take a closer look at the query and optimize its execution plan. If this problem is already known i apologize, but I searched the mailing list and didn't found any mails regarding this topic. Hard to believe i am the first hit by this. kind regards Janning PS: Postgresql is so great! Thank you all! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] second concurrent update takes forever
Hi folks, I don't need this list very often because postgresql works like a charm! But today we encountered a rather complicated puzzle for us. We really need your help! we are using postgresql 8.4 on a debian lenny with latest security patches applied. We are running a rather complicated Update statement from time to time which is only triggered by administrators. The statement updates about 50.000 rows. It takes usually about 10-30 seconds to execute and that's fine for us. This time two administrator did run this update at approximately the same time, so the second update started before the first finished. The first update took about 30 seconds which is quite long but not a problem for us. the second update was useless, because everything was already updated. Anyway, the second statement took 5 hours! We were able to reproduce this scenario very easily. We had a local database, switched off auto-vacuum daemon and had no other concurrent tasks. We just ran the statement and while it was running we started the very same statement in a different database session. the first statement finished in 3 seconds, the second statement never returned (we canceled it after an hour or so). so we read the documentation about transaction isolation http://www.postgresql.org/docs/current/static/transaction-iso.html we are in read committed isolation. It says about UPDATE statements The search condition of the command (the WHERE clause) is re-evaluated to see if the updated version of the row still matches the search condition. the update statement has an additional from clause, I show you slightly simplified version of it (to make it more readable) UPDATE queue SET queue_status = CASE WHEN status = 0 OR status = 2 THEN status + 1 ELSE status END, queue_index = CASE WHEN status = 0 OR status = 2 THEN updates.index ELSE CASE WHEN queue.index updates.index THEN updates.index ELSE queue.index END END FROM ( SELECT matchday.group_id AS group_id, min (matchday.index) AS index FROM event NATURAL LEFT JOIN matchday WHERE event.event_id IN ( 3033445 ) GROUP BY matchday.group_id ) AS updates WHERE queue.group_id = updates.group_id ; so the essence is: the statement has a rather long executing sub-select statement which takes about a 800ms. I suspect postgresql to do the sub- selection and while updating the first row of this sub-select it sees that this row was updated since the update statement (or the transaction) was started. And then it re-checks the condition just for this row (or maybe for a bunch of rows on the same page). if it comes to updating the next row it happens again. re-checking 4+ rows with a very expensive sub-select plan is not good at all. 4 times 800 ms = 10 hours. Am I right with my suspicion? If yes, how can I fix it? kind regards Janning -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] suggestion: log_statement = sample
On Monday 20 July 2009 18:58:21 Greg Sabino Mullane wrote: Perhaps, but I don't think you've quite overcome the 'log everything' counter-argument. # Not everybody can afford a system with lots of raid arrays or dedicated logging boxes. Many people log to the same disk. I do it in some projects. So log_sample gives you quite as much log analysing as full logging but without the I/O overhead. Maybe most of the postgresql guys have lots of hardware with RAID 10 SCSI Disks but reality sometimes differ. we run most of our boxes on IDE RAID 1 without additional disks for logging. In this scenario you just can't log everything. log_min_duration gives you only a view to long running queries but that is just on half of the whole picture. kind regards Janning -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] suggestion: log_statement = sample
On Monday 20 July 2009 19:24:13 Bill Moran wrote: It is not possible for us. Logging millions of statements take too much time. This is a ridiculous statement. In actual practice, full query logging is 1/50 the amount of disk I/O as the actual database activity. If your systems are so stressed that they can't handle another 2% increase, then you've got bigger problems lurking. Have you benchmarked the load it creates under your workload? Yes, it takes up to 15% of our workload in an average use case. But we have peak times where we can not afford 15% lost for logging! And if we log on the same disk, it slows down reading and writing. So it does not really matter how much work load it creates. If it slows down my response, i dont want to log everything. (I know that logging onto the same disk is bad!) Overall, it seems like you've decided that you want this feature and nothing else will do. If that's the case, then just go ahead and write it. It was really just a suggestion. I know that i can handle my problems in a different way like doing full logging from time to time, better automatic tests, more hardware and so on. I just thought, it couldnt be so hard to log every nth statement instead of all. And i think that there are lot of usescases out there for a directive like this. I really just wanted to help to make postgresql better. Even if it would be implemented soon, it wouldn't help me. As i need to analyze my load now! kind regards Janning -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] suggestion: log_statement = sample
On Tuesday 21 July 2009 15:49:36 Tom Lane wrote: Janning Vygen vy...@kicktipp.de writes: On Monday 20 July 2009 19:24:13 Bill Moran wrote: Have you benchmarked the load it creates under your workload? Yes, it takes up to 15% of our workload in an average use case. But we have peak times where we can not afford 15% lost for logging! Well, you could turn it off during the peak times. It affords a server restart which is not a good idea. And i will never get any real time analyze of what happens at peak time. Or buy better hardware --- if you have less than 15% headroom then it's past time to be doing that anyway. yes, you are right, but software solutions are so much nicer... You really haven't given any convincing case for this behavior... that's fine more me. I still don't understand it why it was not convincing. I guess you guys run your DB with lots of hardware power, RAIDs and NAS and so on. But there are many people running it on small boxes with one IDE hard disk. Switching full logging on with such hardware is like shutting it down as IO is blocked! I still think it has reasonable uses cases without lots of effort (just a guess, i havn't looked at the source code yet). But, hey: I just wanted to help improving postgresql. If you think this is not an improvment, its fine for me. Thanks to all for discussing it. kind regards Janning -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] suggestion: log_statement = sample
On Tuesday 21 July 2009 18:09:57 you wrote: Janning Vygen vy...@kicktipp.de writes: On Tuesday 21 July 2009 15:49:36 Tom Lane wrote: Well, you could turn it off during the peak times. It affords a server restart which is not a good idea. Changing logging options does not require a server restart. great. I didn't know that. thank you! Janning -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] suggestion: log_statement = sample
hi, http://archives.postgresql.org/pgsql-general/2009-03/msg00581.php This was my suggestion about introducing a statment to get a sample of SQL statements. Nobody answered yet. Why not? i think my suggestion would help a lot. Or was it kind of stupid? kind regards Janning -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] suggestion: log_statement = sample
hi, thanks for your comments on this. On Thursday 16 July 2009 15:05:58 you wrote: In response to Janning Vygen vy...@kicktipp.de: hi, http://archives.postgresql.org/pgsql-general/2009-03/msg00581.php This was my suggestion about introducing a statment to get a sample of SQL statements. Nobody answered yet. Why not? i think my suggestion would help a lot. Or was it kind of stupid? For my part, I don't think this would be useful. Since most of your queries are run by software, you're going to see a fairly predictable pattern to the queries, which means your sampling isn't going to be anywhere near random, thus it will still be inaccurate and incomplete. I dont think so. In my use case i will get a good sampling of queries as I could keep my log_sample running over long period of time. The sampling is in any case much better than with log_minduration while logging all statement is not acceptable in production. In my experience, I've found that enabling full logging for a short time (perhaps a few hours) gathers enough data to run through tools like pgFouine and find problem areas. It is not possible for us. Logging millions of statements take too much time. Also, we have development servers that run automated tests, and since it's not critical that they be performant, we can run full query logging on them all the time. But you dont run the real use cases with automated tests. There so many factors involved in real time: caching, concurrency, data, peaktime, deadlocks, doubleclicks, robots etc. that you just can't reproduce it on a development system without lots of effort. Additionally, we make sure our production systems have enough hardware behind them that we can add additional tasks without it affecting production use. that's nice, but not everybody can afford it. Of course i would love to log every statement. But do you really log every statement in production? I guess not. All of these are (in my opinion) better approaches to the problem than yet another arbitrary query filtering technique. I mean, logging only the most time-consuming queries is already arbitrary enough (as you already stated). With log_min duration i get only most time-consuming queries. With log sample i can detect if there is a fast query which is called to often. This is impossible today. Again: for my use case it makes sense to have a log_sample feature. kind regards Janning -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problem with invalid byte sequence and log_min_error_statement
On Wednesday 15 April 2009 19:21:03 you wrote: Janning Vygen vy...@kicktipp.de writes: Now i see that the errors occur _exactly_ every 4000 seconds (1 hour, 6 minutes and 40 seconds). I have no clue as i only have one cronjob at night concerning postgresql. I have no autovacuum running (only manual at night). my application cronjobs are only running at night. i have a few threads but no thread has a sleep time of 4000 millis, besides the fact that they all work fine. Maybe i have to check my c3p0 pool. however, i know i have to search for my own, as you can't look into my app. But maybe someone has a hint, that something inside or outside postgresql is usually running every 4000 seconds? There's nothing inside of postgres that would operate on such a schedule. Furthermore, unless I'm all wet about where the error is coming from, this *must* be happening during receipt of a command from an external client. You were right. Perhaps turning on log_connections would give you a clue about what it is. thanks. Turning log_connections on helped me a lot! I found a buggy query from my postfix mailserver which retried the query every 4000 seconds. thanks again for this excellent database and excellent support. kind regards Janning Also set log_line_prefix to make sure you can match up the error report with the previous connection log entry (adding the PID is usually enough). regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Problem with invalid byte sequence and log_min_error_statement
Hi, i run the greatest database ever, postgresql-8.3, on debian etch I am investigating some error messages in my log file: Apr 15 08:04:34 postgres[20686]: [4-1] 2009-04-15 08:04:34 CEST ERROR: invalid byte sequence for encoding UTF8: 0x81 Apr 15 08:04:34 postgres[20686]: [4-2] 2009-04-15 08:04:34 CEST HINT: This error can also happen if the byte sequence does not match the encoding expected by the Apr 15 08:04:34 postgres[20686]: [4-3] server, which is controlled by client_encoding. I want to know which statement produces this error. I have show log_min_error_statement ; error show log_error_verbosity; default so i think the error statement should be logged. Why is the error statement not logged? Or do i need to set log_error_verbosity to verbose to see the statement producing the error? I always see 0x81 and 0xe46973 as invalid byte sequence. Can someone give me a hint what characters in which encoding those bytes might be? kind regards Janning -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problem with invalid byte sequence and log_min_error_statement
Hi, Thank you for this great and ultra-fast support! One more question: On Wednesday 15 April 2009 17:38:51 you wrote: Janning Vygen vy...@kicktipp.de writes: I am investigating some error messages in my log file: Apr 15 08:04:34 postgres[20686]: [4-1] 2009-04-15 08:04:34 CEST ERROR: invalid byte sequence for encoding UTF8: 0x81 Apr 15 08:04:34 postgres[20686]: [4-2] 2009-04-15 08:04:34 CEST HINT: This error can also happen if the byte sequence does not match the encoding expected by the Apr 15 08:04:34 postgres[20686]: [4-3] server, which is controlled by client_encoding. I want to know which statement produces this error. I have show log_min_error_statement ; error show log_error_verbosity; default so i think the error statement should be logged. Why is the error statement not logged? Because the error is happening while trying to convert the statement into the database's encoding. We cannot log the string we have without creating an encoding mismatch in the postmaster log. Ok, so i need to track it another way. Now i see that the errors occur _exactly_ every 4000 seconds (1 hour, 6 minutes and 40 seconds). I have no clue as i only have one cronjob at night concerning postgresql. I have no autovacuum running (only manual at night). my application cronjobs are only running at night. i have a few threads but no thread has a sleep time of 4000 millis, besides the fact that they all work fine. Maybe i have to check my c3p0 pool. however, i know i have to search for my own, as you can't look into my app. But maybe someone has a hint, that something inside or outside postgresql is usually running every 4000 seconds? I always see 0x81 and 0xe46973 as invalid byte sequence. Can someone give me a hint what characters in which encoding those bytes might be? 0xe46973 looks like äis which i found in another thread about encodings http://archives.postgresql.org/pgsql-de-allgemein/2006-10/msg7.php äis is part of Westeuropäische Normalzeit (i hope you can see the german umlaut ä) But why would any process run every 4000 seconds doing something like saying Westeuropäische Normalzeit? And as far as i can see, the code sequence 0x81 is not defined in latin-n. kind regards Janning LATIN1, or some other one of the single-byte LATINn encodings, likely. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] suggestion: log_statement = sample
Hi, we ran a large database on moderate hardware. Disks are usually the slowest part so we do not log every statement. Sometimes we do and our IOwait and CPU increases by 10%. too much for peak times! it would be nice if you could say: log_statement = sample sample_rate = 100 you would get a good sample to analyze your database usage. Of course log_min_duration helps a lot as you see your slowest queries. But with a tool like hibernate, you have often have the problem issuing many many small statements like SELECT * from table where id = ?. They don't show up in the log with a reasonable log_min_duration setting. With my proposal every 100th query is logged and you get a detailed view of your database usage without excessive disk IO. Of course it should be combinable with log_min_duration. What do you think about it? kind regards Janning -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] large table starting sequence scan because of default_statistic_target
Hi, we are running a large 8.3 database and had some trouble with a default statistic target. We had set it to one special table some time ago, when we got a problem with a growing table starting with sequence scans. Last week we did manually cluster this table (create table as ... order by; drop table orig, rename table temp to orig ). Of course the statistic target was dropped and we did not remember to set it again. Why does default_statistic_target defaults to 10? The documentation tells me, the only drawback is a longer ANALYZE run. we are setting it to 100 in postgresql.conf and we did not see a much longer run of ANALYZE. Of course, smaller tables won't need a setting of 100. But small tables are usually not very interesting when it comes to performance. With a setting of 10 you run into difficult problems if your table grows. Suddenly an execution plan changes and you get sequence scans on your largest table! We had such problems and it was annoying to have a real slow down just because of this minor configuration parameter. I suggest to setting it to 100 by default: - no problems for small installations - no problems for DBA who always adjust their system in every possible way. - no problems for growing databases with unequal distributed data But maybe there are some other reasons not setting it to a higher value. If so, please tell me. kind regards Janning -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] large table starting sequence scan because of default_statistic_target
On Monday 16 March 2009 15:13:51 Scott Marlowe wrote: On Mon, Mar 16, 2009 at 7:15 AM, Janning Vygen vy...@kicktipp.de wrote: Hi, Why does default_statistic_target defaults to 10? I suggest to setting it to 100 by default: Already done in 8.4 GREAT! sorry for not searching the archives or changelog before. Janning -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Prepared Statements
Am Sonntag 13 Januar 2008 00:46:50 schrieb Tom Lane: Kris Jurka [EMAIL PROTECTED] writes: On Thu, 10 Jan 2008, [EMAIL PROTECTED] wrote: What do you mean with longer lifespan? Doesn't the JDBC driver uses the PREPARE Sql Statement and therefore the prepared Statement has the same lifespan as the connection? If so, as connections are pooled and never closed, the prepared Statement will last forever. What if the table analyzes changes and a better execution plan could be found? Data and stats changes do not trigger a replan. Note that this is no longer true as of 8.3: a stats update from ANALYZE (either manual or autovacuum) will trigger invalidation of cached plans. great, i have too look out for 8.3, but i am quite happy with 8.1 because of automatic security updates coming with debian out of the box. I have to look for another way to replan. Maybe i just have to close and reopen my connections from time to time. kind regards Janning ---(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] why it doesn't work? referential integrity
On Saturday 11 August 2007 12:28:45 Pavel Stehule wrote: Hello I found strange postgresql's behave. Can somebody explain it? Regards Pavel Stehule CREATE TABLE users ( id integer NOT NULL, name VARCHAR NOT NULL, PRIMARY KEY (id) ); INSERT INTO users VALUES (1, 'Jozko'); INSERT INTO users VALUES (2, 'Ferko'); INSERT INTO users VALUES (3, 'Samko'); CREATE TABLE tasks ( id integer NOT NULL, owner INT REFERENCES users (id) ON UPDATE CASCADE ON DELETE SET NULL, worker INT REFERENCES users (id) ON UPDATE CASCADE ON DELETE SET NULL, checked_by INT REFERENCES users (id) ON UPDATE CASCADE ON DELETE SET NULL, PRIMARY KEY (id) ); INSERT INTO tasks VALUES (1,1,NULL,NULL); INSERT INTO tasks VALUES (2,2,2,NULL); INSERT INTO tasks VALUES (3,3,3,3); DELETE FROM users WHERE id = 1; -- works simple DELETE FROM users WHERE id = 2; -- works ok DELETE FROM users WHERE id = 3; -- doesn't work, why? ERROR: insert or update on table tasks violates foreign key constraint tasks_checked_by_fkey DETAIL: Key (checked_by)=(3) is not present in table users. CONTEXT: SQL statement UPDATE ONLY public.tasks SET worker = NULL WHERE $1 OPERATOR(pg_catalog.=) worker looks strange to me too, but i never had foreign keys to the same table. it works if you define your chekced_by FK deferrable with checked_by INT REFERENCES users (id) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, it seams that postgresql does its job in a procedural way instead of relational. kind regards, Janning ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] createing indexes on large tables and int8
Hi i try to populate a database. I dropped all indexes on the target table to speed up the copy. it works fine. After this i create the index and it took 10 hours just for one index (primary key). I have 100.000.000 rows with one PK (int8), two integer data values, and two FK (int8) Are there other options than maintenance_work_mem to speed up index creation? How do i find the optimal value for maintenance_work_mem. At the moment i have 160MB of maintenance work_mem. related questions: I use int8 types in most PK or FK columns. I could change my java code to use integer instead of Long ( i dont know why i took Long in the first place). a) Would int4 instead of int8 speed up creation of index? b) it will reduze the size of the table, of course. Would this reduce size of index, too? By the same amount? c) How much speed up will i gain on queries? Postgresql Doc mention it in section data types without saying how much speed-up i gain. Please, i just want to know if its worth it. Is it more like 0,1%, 1%, 10% or 50%? any help on speeding this up is very appreciated. kind regards, janning ---(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] restore dump to 8.19
On Saturday 14 July 2007 00:04:08 Jim Nasby wrote: On Jul 13, 2007, at 2:11 PM, [EMAIL PROTECTED] wrote: i tried to restore a dump from version 8.1.8 to 8.1.9 and i had in one table a value 1.7383389519587511e-310 i got the following error message: pg_restore: ERROR: type double precision value out of range: underflow CONTEXT: COPY gesamtpunktecache, line 925001, column gc_gesamtsiege: 1.7383389519587511e-310 pg_restore: [archiver (db)] error returned by PQendcopy: ERROR: type double precision value out of range: underflow Is this on *identical* hardware, and were the binaries built the same? Floating point stuff is very hardware dependent, and may have some dependencies on compiler, etc as well. no, it was different hardware and maybe different compiler. Hmm, i do understand why computers are not masters of floating point (though i dont understand how they can fly air planes) , but a dump should be something which can be used across different hardware, shouldn't it? kind regards, janning ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Hardware related question: 3ware 9500S
Hi, i don't know much about hard disks and raid controllers but often there is some discussion about which raid controller rocks and which sucks. my hosting company offers me a raid 10 with 4 serial-ata disks. They will use a 3ware 4-Port-RAID-Controller 9500S More than 4 disks are not possible. Most operations and all time-critical operations are read-only using a lot of indices. My partioning plans are like this: disk 1: OS, tablespace disk 2: indices, WAL, Logfiles - Does my partitioning make sense? - I want to know if 3ware 9500 S is recommended or if its one of those controllers which sucks. kind regards, janning ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Hardware related question: 3ware 9500S
Thanks for your fast reply. Am Mittwoch, 12. April 2006 18:31 schrieb Merlin Moncure: On 4/12/06, Janning Vygen [EMAIL PROTECTED] wrote: Hi, disk 1: OS, tablespace disk 2: indices, WAL, Logfiles - Does my partitioning make sense? with raid 10 all four drives will appear as a single physical device shared by all. I'm personally not a big fan of logical partitioning of a single raid device unless you are trying to keep a physical volume under 1 TB for example. Each sync on the volume is guaranteed to sync all 4 disks regardless of how you set your partitions up. Ok, i am not a raid expert. but in my understanding RAID 10 is faster than two RAID 1 arrays, aren't they? So, given that i can put up to 4 S-ATA disk in my server and the mentioned raid controller. Would you prefer no-raid, RAID1 or RAID 10? - I want to know if 3ware 9500 S is recommended or if its one of those controllers which sucks. escalade is a fairly full featured raid controller for the price. consider it the ford taurus of raid controllers, it's functional and practical but not sexy. Their S line is not native sata but operates over a pata-sata bridge. Stay away from raid 5. thanks for your recommendation. ford taurus is ok for me :-) kind regrads janning ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Updating database structure
Am Mittwoch, 22. März 2006 20:40 schrieb Luuk Jansen: I have a problem with finding a way to update a database structure. This might be a very simple problem, just cannot find the info. I am looking at updating the structure of my database. I put an application on my production server some time ago and further developed the application till a point I have to merge the changes. How can I update the structure on the production server to reflect the database on my test machine in an easy way with preservation of the data on the production server. There are no major changes in the fields types etc., mainly additions/deletions of fields in the tables. usually you write some update SQL scripts which can be deployed to the production database. you have a base schema in sql/schema.sql and further changes are placed inside sql/update_0001.sql with content like BEGIN; ALTER TABLE ... COMMIT; now i use a script called update.pl and a version table inside my database to register which update scripts are already deployed. The script checks which changes have to be deployed and installs them to the production database. if you dont have those update SQL scripts it can become quite difficult. You have to compare original schema and current schema. To get a schema look at pg_dump option '-s'. Compare those schemas and write your update SQL scripts. kind regards janning ---(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] invalid memory alloc request size
Hi, my cron job which is dumping the databse fails this night. I got: pg_dump: ERROR: invalid memory alloc request size 18446744073709551614 pg_dump: SQL command to dump the contents of table spieletipps failed: PQendcopy() failed. pg_dump: Error message from server: ERROR: invalid memory alloc request size 18446744073709551614 pg_dump: The command was: COPY public.spieletipps (tr_kurzname, mg_name, sp_id, stip_heimtore, stip_gasttore) TO stdout; I am running postgresql-server-8.0.3-1.2 on SuSE Linux 9.3 (x86-64) I had this a few days before and decided to use a recent backup. It works fine for two days only. Maybe my harddisk is broken? Maybe 64-bit is broken? i have no clue and no idea what do to. i ve searched the archives and found a recent thread on HACKERS but sorry guys: i dont know how to produce a backtrace. select count(*) from spieletipps; count -- 11612957 (1 Zeile) works fine. When i do something like this: $ select * from spieletipps where sp_id 1000; Server beendete die Verbindung unerwartet Das heißt wahrscheinlich, daß der Server abnormal beendete bevor oder während die Anweisung bearbeitet wurde. Die Verbindung zum Server wurde verloren. Versuche Reset: Fehlgeschlagen. (it means: server closed the connection unexpectedly. ... Try to reset connection failed.) Please help me! kind regards, janning ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Postgresql/DBA/Sysadmin Consultant in Düsseldorf, Germany
Hi, we are running a very popular german website[*] which has grown over the years since 1995. We manage between 10 and 20 millions pageviews a month. We are a small company and myself is responsible for programming, DBA, system administration and hardware. I am a self-educated person since the beginning of the internet. In last couple of months we encountered a lot of problems with our postgresql installation which we couldn't handle appropiate. Most of them are hardware related. There are lot of reasons why some things went bad. We want to start over before things are getting worse. I would like to talk to some professional Postgresql DBA personally (preferred in german language). Of course we will pay for it. I don't want to talk to sells personal, i want to talk to someone who really knows and has lots of expierence. Some topics: - which hardware to use - where to place our hardware (data center) - backup/failover strategies - performance / postgresql.conf - monitoring performance and system health I had a meeting with sun consultants already and i will have another one. That will be fine. But i would like to talk to some who is independent from company selling things i could need. If there is someone nearby who might help, please answer via personal E-Mail ([EMAIL PROTECTED]). I guess we could meet very for a few hours and we will see if further consultancy is needed. kind regards, janning [*] which i not named to get not listed in google.de, but it's called w w w . k i c k t i p p . d e ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] invalid memory alloc request size
Am Montag, 23. Januar 2006 17:05 schrieb Tom Lane: Janning Vygen [EMAIL PROTECTED] writes: pg_dump: ERROR: invalid memory alloc request size 18446744073709551614 pg_dump: SQL command to dump the contents of table spieletipps failed: PQendcopy() failed. This looks more like a corrupt-data problem than anything else. Have you tried the usual memory and disk testing programs? no, i didn't. What are the usual memory and disk testing programs? ( a few weeks ago i wanted to start a troubleshooting guide for guys like me, but i didn't start yet this needs to be documented.). I am not a system administrator and a hard disk is a black box to me. By the way: the database is still running and serving requests. recent thread on HACKERS but sorry guys: i dont know how to produce a backtrace. Time to learn ;-) gdb /path/to/postgres_executable /path/to/core_file gdb bt gdb q I shouldn't call gdb while my database is up and running, don't i? I tried to find and delete the corrupted row (as you mentioned in http://archives.postgresql.org/pgsql-admin/2006-01/msg00117.php) I found it: $ select sp_id from spieletipps limit 1 offset 387583; Server beendete die Verbindung unerwartet Das heißt wahrscheinlich, daß der Server abnormal beendete bevor oder während die Anweisung bearbeitet wurde. Die Verbindung zum Server wurde verloren. Versuche Reset: Fehlgeschlagen. ! \q and i can get the ctid: $ select ctid from spieletipps limit 1 offset 387583; ctid --- (3397,49) (1 Zeile) but when i want to delete it: $ delete from spieletipps where ctid = '(3397,49)'; Server beendete die Verbindung unerwartet Das heißt wahrscheinlich, daß der Server abnormal beendete bevor oder während die Anweisung bearbeitet wurde. Die Verbindung zum Server wurde verloren. Versuche Reset: Fehlgeschlagen. How can i get rid of it? (I don't have oids in the table, i created them without oids) The core file will be somewhere under $PGDATA, named either core or core.n depending on your kernel settings. If you don't see one then it's probable that the postmaster was started under ulimit -c 0. Put ulimit -c unlimited in your postgres startup script, restart, trigger the crash again. It's also a good idea to look in the postmaster log to see if any unusual messages appeared before the crash. this is form the postmaster log: LOG: server process (PID 14756) was terminated by signal 11 LOG: terminating any other active server processes LOG: all server processes terminated; reinitializing FATAL: the database system is starting up LOG: database system was interrupted at 2006-01-23 09:46:03 CET LOG: checkpoint record is at 1/D890C0E0 LOG: redo record is at 1/D88F93E8; undo record is at 0/0; shutdown FALSE LOG: next transaction ID: 485068; next OID: 16882321 LOG: database system was not properly shut down; automatic recovery in progress LOG: redo starts at 1/D88F93E8 LOG: record with zero length at 1/D8953988 LOG: redo done at 1/D8953920 LOG: database system is ready LOG: server process (PID 15198) was terminated by signal 11 LOG: terminating any other active server processes WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. FATAL: the database system is in recovery mode LOG: all server processes terminated; reinitializing LOG: database system was interrupted at 2006-01-23 09:46:15 CET LOG: checkpoint record is at 1/D8953988 LOG: redo record is at 1/D8953988; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 485130; next OID: 16882321 LOG: database system was not properly shut down; automatic recovery in progress LOG: redo starts at 1/D89539D0 LOG: record with zero length at 1/D8966BF8 LOG: redo done at 1/D8966BC8 LOG: database system is ready LOG: server process (PID 15400) was terminated by signal 11 LOG: terminating any other active server processes WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. LOG: all server processes terminated; reinitializing LOG: database system was interrupted at 2006-01-23 09:46:24 CET LOG: checkpoint record is at 1/D8966BF8 LOG: redo record is at 1/D8966BF8; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 485183; next OID: 16882321 LOG: database system was not properly shut down; automatic recovery in progress FATAL: the database system
Re: [GENERAL] invalid memory alloc request size
Am Montag, 23. Januar 2006 20:30 schrieb Tom Lane: Janning Vygen [EMAIL PROTECTED] writes: Ok, i got the reffilnode from pg_class and compiled pg_filedump. result of ./pg_filedump -i -f -R 3397 /home/postgres8/data/base/12934120/12934361 filedump.txt is attached OK, what's the schema of this table exactly? It looks like there are a couple of text or varchar columns to start, but I'm not sure about the last three columns. kicktipp.de= \d spieletipps Tabelle »public.spieletipps« Spalte | Typ| Attribute ---+--+--- tr_kurzname | text | not null mg_name | text | not null sp_id | integer | not null stip_heimtore | smallint | not null stip_gasttore | smallint | not null Indexe: »pk_spieletipps« PRIMARY KEY, btree (tr_kurzname, mg_name, sp_id) »ix_stip_fk_spiele« btree (tr_kurzname, sp_id) CLUSTER Fremdschlüssel-Constraints: »fk_mitglieder« FOREIGN KEY (tr_kurzname, mg_name) REFERENCES mitglieder(tr_kurzname, mg_name) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED »fk_tippspieltage2spiele« FOREIGN KEY (tr_kurzname, sp_id) REFERENCES tippspieltage2spiele(tr_kurzname, sp_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED Regeln: cache_stip_delete AS ON DELETE TO spieletipps DO UPDATE tsptcache SET tc_cache = -2 FROM tippspieltage2spiele tspt2sp, spiele sp WHERE tsptcache.tr_kurzname = old.tr_kurzname AND tspt2sp.tr_kurzname = old.tr_kurzname AND tspt2sp.sp_id = old.sp_id AND tspt2sp.sp_id = sp.sp_id AND sp.sp_abpfiff = true AND tsptcache.tspt_sort = tspt2sp.tspt_sort AND sign((old.stip_heimtore - old.stip_gasttore)::double precision) = sign((sp.sp_heimtore - sp.sp_gasttore)::double precision) AND tsptcache.tc_cache -2 cache_stip_insert AS ON INSERT TO spieletipps DO UPDATE tsptcache SET tc_cache = -2 FROM tippspieltage2spiele tspt2sp, spiele sp WHERE tsptcache.tr_kurzname = new.tr_kurzname AND tspt2sp.tr_kurzname = new.tr_kurzname AND tspt2sp.sp_id = new.sp_id AND tspt2sp.sp_id = sp.sp_id AND sp.sp_abpfiff = true AND tsptcache.tspt_sort = tspt2sp.tspt_sort AND sign((new.stip_heimtore - new.stip_gasttore)::double precision) = sign((sp.sp_heimtore - sp.sp_gasttore)::double precision) AND tsptcache.tc_cache -2 cache_stip_update AS ON UPDATE TO spieletipps DO UPDATE tsptcache SET tc_cache = -2 FROM tippspieltage2spiele tspt2sp, spiele sp WHERE tsptcache.tr_kurzname = new.tr_kurzname AND tspt2sp.tr_kurzname = new.tr_kurzname AND tspt2sp.sp_id = new.sp_id AND tspt2sp.sp_id = sp.sp_id AND sp.sp_abpfiff = true AND tsptcache.tspt_sort = tspt2sp.tspt_sort AND (sign((new.stip_heimtore - new.stip_gasttore)::double precision) = sign((sp.sp_heimtore - sp.sp_gasttore)::double precision) OR sign((old.stip_heimtore - old.stip_gasttore)::double precision) = sign((sp.sp_heimtore - sp.sp_gasttore)::double precision)) AND tsptcache.tc_cache -2 but i guess its item 49 which makes trouble 1258: 0100 616c7465 68656964 6500 alteheide... But it doesn't look very diffrent to item 48: 12a0: 0d00 616c7465 68656964 6500 alteheide... If these are both supposed to be strings 'alteheide', then the problem is the bogus length word on the first one: instead of starting with 0100 it should start with 0d00, like the second one does. yes, they should both be alteheide. Is it possible to open the file and just fix the bit? It's conceivable that this stems from a software problem, but I'm wondering about hardware problems causing dropped bits, myself. I have no clue, why it happens. But i changed my schema a few month ago to use a materialized view (You see all the rules in this schema above). i need some complicated ranking algorithm to calculate the materialzed view. everything is implemented inside postgresql with rules and functions (pgperl and plpgsql). One more aspect are temp tables to me. I use lots of them for a specific tasks (reusing the calculating algorithm mentioned above for a different data view). With lots of temp tables i got problems with pg_type where some old temp values reside and i got to delete some of them manually a few times per month. After all my feeling is that i encouter problems like this one too often to believe in hardware problems. But this time it seems to be a new one and i have no clue if hardware or software related. At this time i just want to fix it. But if you want to take a close look at it, i will send you all you need. Another point is that AFAICS this tuple could not pose a problem for DELETE all by itself, because it doesn't have any toasted fields. Perhaps there is more corruption elsewhere. Could you get a stack trace from the crashed DELETE, rather than a crashed SELECT? Maybe the rule is a problem? here you are. I did: select ctid from spieletipps limit 1 offset 387439; ctid --- (3397,49) (1 Zeile
Re: [GENERAL] invalid memory alloc request size
TOM! Ich will ein Kind von Dir!! (it means 'something like': thank you so much. you just saved my life!) Am Montag, 23. Januar 2006 21:16 schrieb Tom Lane: Janning Vygen [EMAIL PROTECTED] writes: OK, what's the schema of this table exactly? ... Regeln: cache_stip_delete AS ON DELETE TO spieletipps DO UPDATE tsptcache SET tc_cache = -2 [...] Oh, I should have thought of that: the bare DELETE operation doesn't care what's in the tuple, but this ON DELETE rule sure does. That's why the delete crashes, it's trying to extract the field contents so it can execute the rule. I dropped the rule and deleted the row successfully with the ctid. Thanks a lot for the great support! This problem will be my first article in my PostgreSQL Troubleshooting Guide for Dummies. We really need it for guys like me. yes, they should both be alteheide. Is it possible to open the file and just fix the bit? Yeah, if you have a suitable hex editor. You'll probably need to shut down the postmaster first, as it may have a cached copy of the page. i decided not to poke to postgres internal file storage. I have no clue, why it happens. But i changed my schema a few month ago to use a materialized view (You see all the rules in this schema above). i need some complicated ranking algorithm to calculate the materialzed view. everything is implemented inside postgresql with rules and functions (pgperl and plpgsql). One more aspect are temp tables to me. I use lots of them for a specific tasks (reusing the calculating algorithm mentioned above for a different data view). With lots of temp tables i got problems with pg_type where some old temp values reside and i got to delete some of them manually a few times per month. Hmm ... the one part of that that jumps out at me is plperl. We already know that plperl can screw up the locale settings; I wonder whether there are other bugs. Anyway, if you are using plperl I *strongly* recommend updating to the latest PG release ASAP (8.0.6 in your case). ok, shouldn't i upgrade to 8.1 instead of 8.0.6 if i can? If you cannot, at least make sure the postmaster is launched with the same LC_XXX settings in its environment as are embedded in the database. i will look at it! kind regards janning ---(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] invalid memory alloc request size
Am Montag, 23. Januar 2006 21:57 schrieb Tom Lane: Janning Vygen [EMAIL PROTECTED] writes: ok, shouldn't i upgrade to 8.1 instead of 8.0.6 if i can? Up to you --- you have more risk of compatibility issues if you do that, whereas within-branch updates are supposed to be painless. Depends whether you have the time right now to deal with testing your applications against 8.1. ok, i will think about it. one more question: You mentioned standard disk and memory checks. Can you point to some link where i can find more about it or which software do you mean? I guess i have to start looking at it. kind regards, janning ---(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] the best way to catch table modification
Am Dienstag, 25. Oktober 2005 19:40 schrieb David Gagnon: Hi, I posted on the same subject a month ago . .you can search for the current title in the JDBC mailing list [JDBC] implementing asynchronous notifications PLEASE CONFIRM MY I ended using statement-level trigger. I haven't found another way to do it . Regards /David Marek Lewczuk wrote: Hello, I'm implementing db-queries caching system - for this I need to know each table's modification time (or at least modification counter). I know that I can make a statement-level trigger, which will update a table with tables modification times - however this is inefficient if many inserts or updates are made on single table (in single transaction). The best would be some kind of transaction-level trigger, but this is not available. Are there any other, better options ? What did you mean with many inserts or updates? Did you mean statements which modify or insert many rows but are still one single statement: you could use rules instead. I think rules are much more powerful than triggers and they are much faster if a statement affects many rows. Triggers fires for each row, rules are just modifying the original statement. so usually if you can handle the load of the statements you should handle the loads of statements rewritten by rules, too. Rules are usually much more efficient than triggers. And they are much more relational in my opinion. kind regards, janning ---(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] doc typo sql-reindex.html psql vs. postgres
Am Donnerstag, 20. Oktober 2005 19:59 schrieb David Fetter: On Thu, Oct 20, 2005 at 06:04:53PM +0200, Janning Vygen wrote: By the way: What i really miss is a troubleshooting document in the docs. That's a great idea. Please post a doc patch with some of the troubleshooting tips you have in mind :) Hi David, I really would like to start it! Two reasons for me not to do it: I don't speak english very well, technical writing is even worse as my native language is german. Second: i have experience with postgresql for 5 years, but i don't think i have enough technical background for writing articles about troubleshooting. If it still makes sense to start, i will! But there is much more than troubleshooting what i miss. PostgreSQL was my first use of any SQL Database, so i have come across many problems an read the documentation many times. There is a lot of information, but some of the excellent features of PostgreSQL are hidden. Look at the relevant sections for UPDATE statement. http://www.postgresql.org/docs/8.0/interactive/tutorial-update.html http://www.postgresql.org/docs/8.0/interactive/dml-update.html http://www.postgresql.org/docs/8.0/interactive/sql-update.html The UPDATE statement is so powerful in Postgresql, but it is only mentioned in the reference that you can use a from list. But it doesn't show up with any examples of some more complicated UPDATE statements. Of course, people coming from Oracle might know it. But there should be more stuff for people coming from mysql or from nowhere (like me). BTW: Reference Documentation of UPDATE should mention that you can't do a relational UPDATE like UPDATE tableA SET id = id + 1; if id is a primary key (in relational theory it should work!) Another point: the interactive mode looses information between versions. In 7.3 there were some user contributed notes: http://www.postgresql.org/docs/7.3/interactive/sql-update.html in 7.4 and 8.0 they are gone. As i use a lot of PHP i can tell you that user contributed notes are great. They are sometimes wrong, sometimes misleading, but always helps you to see, what problems other people had. I think that postgresql needs much docs and articles, how-tos and tippstricks. (really great stuff is at http://www.varlena.com/varlena/GeneralBits/ and techdocs has more links to great stuff but it is all scattered on many places. Maybe something like a Wiki would help with Sections about: - Troubleshooting - Examples - Howtos - Technical articels - FAQ - Postgresql GUI clients - High availability I would like to help as far as i can: starting with troubleshooting, installing and maintaining a wiki, collecting information. As you are more experienced, give me a hint what to do first if i can help at all. kind regards, Janning ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] doc typo sql-reindex.html psql vs. postgres
At http://www.postgresql.org/docs/8.0/static/sql-reindex.html it says: Rebuild all system indexes in a particular database, without trusting them to be valid already: $ export PGOPTIONS=-P $ psql broken_db It should be: Rebuild all system indexes in a particular database, without trusting them to be valid already: $ export PGOPTIONS=-P $ postgres broken_db ,shouldn't it? kind regards, janning ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] doc typo sql-reindex.html psql vs. postgres
Am Donnerstag, 20. Oktober 2005 16:04 schrieb Tom Lane: Janning Vygen [EMAIL PROTECTED] writes: it says: $ export PGOPTIONS=-P $ psql broken_db It should be: $ export PGOPTIONS=-P $ postgres broken_db No, it's correct as it stands. You used to need a standalone backend for that, but not anymore. (PGOPTIONS isn't recognized by a standalone backend anyway, IIRC, so there would be more wrong with the example than that if it were wrong.) ok, sorry. I had some corrupted indexes and just did $ postgres -P broken_db and got a shell to send $ REINDEX DATATBASE broken_db as far as i can remember, so i thought i could throw in my two cents to improve postgresql. By the way: What i really miss is a troubleshooting document in the docs. I run postgresql for over 4 years now and i have come across many situations where i really would need something like this. You can find most solutions by reading the documentation entirely but it is not very helpful if your production database went wrong and you are quite a bit in a hurry. kind regards, janning ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] ERROR: type temp_gc already exists
I tried to reproduce it, but it seems that my problem vanished since i switched from pg_pconnect to pg_connect in PHP. Maybe this is of any help. But in my understanding the reported failure should not be influenced by selection of pg_connect vs. pg_pconnect. i will report if this problem arises again. kind regards, janning Am Mittwoch, 28. September 2005 16:07 schrieb Tom Lane: Janning Vygen [EMAIL PROTECTED] writes: I recently reported this problem and i would like to help solving it. But how can i build a self-contained test-case? It just happens sometimes under load. I didn't say you need to make it 100% reproducible; you just have to make a case that someone else can run that will eventually produce the error. The sort of poking and prying that will need to happen to debug it will involve things you do not want done to your production database, therefore we need to be able to make the error happen in a test setup. You probably need to create a client script that will issue multiple parallel queries that are similar to what your regular application does. See for instance this discussion: http://archives.postgresql.org/pgsql-hackers/2005-05/msg00613.php If you're handy with C, pgbench might be a useful starting point. But a script in perl python or tcl will be fine too. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] ERROR: type temp_gc already exists
Hi Tom, Hi Damon, Am Dienstag, 27. September 2005 20:36 schrieb Tom Lane: Damon Hart [EMAIL PROTECTED] writes: I came across this thread after intermittently observing very similar error messages last week from a PostgreSQL (8.0.2) server, e.g.: SQL error: = 'type local_roll already exists' in line 1984 ... Sorry I haven't been able to diagnose this any more precisely, but I think a hardware explanation is very doubtful. I agree, it smells more like a software problem. Can you generate a test case, ie, a self-contained program someone could run that'd reproduce the error (possibly after running a long time)? I recently reported this problem and i would like to help solving it. But how can i build a self-contained test-case? It just happens sometimes under load. do you just need DB Schema and my frontend script which causes the error? Or do you need a complete dump of the database? Does it make sense to generate a dump of the database when the error occured? Hardware can't be the reason in my opionion because it happens on two servers at the same time under the same load. The servers are not related to each other in anyway but both run exactly the same application. They run the same hardware but it woul dsurprise me if two hardware failures happen at the same time and produce the same software failure. I got the error in two cases: case 1: a daemon calls a plpgsql function in Transaction mode SERIALIZATION. This function generates a TEMP TABLE for calculating some stuff like this: EXECUTE $$ CREATE TEMP TABLE temp_gc AS SELECT [...] And at the end of the function the temp table is dropped with EXECUTE $$DROP TABLE temp_gc;$$; This function is only called by a daemon which calculates some materialized view. This function is never called by more than one session at the same time. It fails under load (load comes from test case 2) with TYPE temp_gc already exists case 2: Many parallel sessions do the following: BEGIN; CREATE TEMP TABLE spiele ( sp_id int4, sp_heimtore int4, sp_gasttore int4, sp_abpfiff boolean, wb_name text, sn_name text, sp_termin timestamp ) ON COMMIT DROP; INSERT INTO spiele ... SELECT ... COMMIT; there is also a table Spiele in schema public. The session selects a result which depends on the user given game results (table spiele saves game results). Under load it happens that the transaction fails with TYPE spiele already exists. In both cases i got 10-15 of type temp_gc and spiele in pg_type. Then i run something like for I in 1..20 do DROP TYPE pg_temp_$I.spiele; DROP TYPE pg_temp_$I.temp_gc; done; After this everything works fine again. kind regards, Janning Vygen ---(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] Problems with leftover types in pg_temp schemas
Hi, last week i asked a question about how to remove a left over pg_type from a temp table. http://archives.postgresql.org/pgsql-general/2005-09/msg00409.php Tom Lane helped me managing it by reindexing pg_depends and DROPping the pg_temp_X.temp_gc. Now i have the same problem again but with another function which creates and drops a temporary table (not temp_gc this time, but spiele) If the server is overloaded and lots of requests are made to this function it seems that cleaning up pg_type isn't working. It happend on two different database servers today. On one of them it happens yesterday too. So it can't be a hardware failure. If i do DROP pg_temp_N.spiele serveral times (i found the type about 10 times), everything works fine. What happens in my function is the following: I have a table spiele (games) where i save scores. Users can use a html form which offers to input scores and temporarly overwrite the table spiele to do some calculation like what would be the result if the scores were like my input. For this reason i take the input and build a temporary table spiele which overrides the main table because both schemas public and pg_temp are in the search_path. the last two days, when many people used this function, suddenly i got lots of pg_type spiele which are not deleted after dropping the temporary table. Is the amount of temporary schemas bound to a specific number? Or can i create as many temporary schemas as sessions. Why can there be some types left after dropping a temporary table. To me it seems like an odd bug which occurs only when using a lot of temporary tables in parallel sessions. the problem is quite urgent because i can't afford the time for rewriting the app to not use temp tables. So i need to fix it. any help is very appreciated. kind regards, janning ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] ERROR: type temp_gc already exists
Janning Vygen vygen ( at ) gmx ( dot ) de writes: Am Samstag, 10. September 2005 18:05 schrieb Tom Lane: If there's no pg_depend entry then DROP TYPE should work. Otherwise you might have to resort to manually DELETEing the pg_type row. Thanks for your detailed answer. I don't want to do anything wrong. To be sure, i have some more questions: - There is no entry in pg_depend. Should i just drop the entry from pg_type or should i REINDEX anyway? Well, what did you do to check that there was no entry? If the index is corrupt and you issued a query that used the index, it might have failed to find an entry that's actually there in the table (in fact, if we're assuming the DROP TYPE didn't happen because the system didn't find the dependency row while dropping the table, this is pretty much exactly what you'd expect). I'd REINDEX and then check again. What i did so far: $ REINDEX TABLE pg_depend $ SELECT * from pg_depend where objid = 16562879; classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype -+---+--++--+-+- (0 rows) $ SELECT * from pg_type where typname = 'temp_gc'; typname | typnamespace | typowner | typlen | typbyval | typtype | typisdefined | typdelim | typrelid | typelem | typinput | typoutput | typreceive | typsend | typanalyze | typalign | typstorage | typnotnull | typbasetype | typtypmod | typndims | typdefaultbin | typdefault -+--+--++--+-+--+--+--+-+---++-+-++--+++-+---+--+---+ temp_gc |16847 | 100 | -1 | f| c | t | ,| 16562879 | 0 | record_in | record_out | record_recv | record_send | - | d| x | f | 0 | -1 |0 | | (1 row) $ DROP TYPE temp_gc; ERROR: type temp_gc does not exist If there's no pg_depend entry then DROP TYPE should work. Otherwise you might have to resort to manually DELETEing the pg_type row. There is no pg_depend entry as far as i can tell, but DROP TYPE doesn't work. Can i just DELETE the pg_type row now?? Sorry for asking again and again, but manipulating system catalogs seems to me very dangerous. It's live database and i dont want to do anything wrong. kind regards Janning ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] ERROR: type temp_gc already exists
Am Donnerstag, 15. September 2005 15:31 schrieb Tom Lane: Janning Vygen [EMAIL PROTECTED] writes: $ DROP TYPE temp_gc; ERROR: type temp_gc does not exist The temp schema is evidently not in your search path. You need something like drop type pg_temp_NNN.temp_gc; great support! great software! thanks a lot again! I managed it and now everything runs fine. kind regards janning ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] ERROR: type temp_gc already exists
Am Samstag, 10. September 2005 18:05 schrieb Tom Lane: Janning Vygen [EMAIL PROTECTED] writes: i guess the table was dropped but not the corresponding type. How can things like this happen? Corrupted pg_depend table maybe? You might try REINDEXing pg_depend to be on the safe side. Also please look to see if there are any relevant entries in it (look for objid = the type's OID, or refobjid = 16562879 which we can see was the table's OID). How can i fix it? Can i just drop the type from pg_type? If there's no pg_depend entry then DROP TYPE should work. Otherwise you might have to resort to manually DELETEing the pg_type row. Thanks for your detailed answer. I don't want to do anything wrong. To be sure, i have some more questions: - There is no entry in pg_depend. Should i just drop the entry from pg_type or should i REINDEX anyway? - Can i REINDEX pg_depend in normal operation mode or do i have to take precautions mentioned in the docs? [1] - How can things like this happen? Hardware failure? If yes, should i change my harddisk? kind regards, Janning [1] It's not clear to me if pg_depend is a shared system catalog because the docs say any of the shared system catalogs (pg_database, pg_group, pg_shadow, or pg_tablespace) Maybe the iteration is final, maybe it shows only examples) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] ERROR: type temp_gc already exists
Hi, i run postgresql 8.0.3 and i have a script which calls a postgresql function to calculate a materialized View. this function creates a temp table. It does so with EXECUTE statments to avoid the caching of plans with temporary tables. It runs on three servers and evrything went fine for a couple of months. the temp table is dropped at the end of the function. everything runs inside a (serialized) transaction. suddenly the script fails on one server with ERROR: type temp_gc already exists if i connect and try to create a temp table it says create temp table temp_gc (id text); ERROR: type temp_gc already exists in my pg_type i have select * from pg_type where typname = 'temp_gc'; typname | typnamespace | typowner | typlen | typbyval | typtype | typisdefined | typdelim | typrelid | typelem | typinput | typoutput | typreceive |typsend | typanalyze | typalign | typstorage | typnotnull | typbasetype | typtypmod | typndims | typdefaultbin | typdefault -+--+--++--+-+--+--+--+-+---++-+-++--+++-+---+--+---+ temp_gc |16847 | 100 | -1 | f| c | t | ,| 16562879 | 0 | record_in | record_out | record_recv | record_send | - | d| x | f | 0 | -1 |0 | | (1 row) i guess the table was dropped but not the corresponding type. How can things like this happen? How can i fix it? Can i just drop the type from pg_type? kind regards, janning ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Trigger disactivation and SELECT WAITING
Am Mittwoch, 27. Juli 2005 09:47 schrieb Philippe Lang: Thanks Tom, thanks Janning, I found triggers very convenient to do different tasks in the database, and these tasks go far beyond what we can do in rules, Janning. Right. There are some things that can't be done with rules. When a line is being inserted in an order, the insert trigger automatically inserts data in a subtable of the order line, for example. In this subtable, there are informations regarding the planning of the order. People can use the GUI to populate the order, but things won't break if the user opens the database table directly, which can happen sometimes. Without the trigger, an insert function click would have to be used each time an order line is being added, and this is not that effective from a user-experience point of view, I think. Or would require a lot a client-coding. As far as i understand your example it can be done with rules, too. Now the use of a trigger has a drawback: when you want to duplicate an order, for example. During the duplication function, I would like to disable the trigger, in order to make a copy of the order, order lines, and order lines subtable data. This is much easier than keeping the trigger, and having to delete default data it inserts in the new order. just a thought: maybe you can insert a column copyof_id in your tabel and mark it if you copy orders. The trigger can check NEW.copyof and quit his work if it is marked. With rules there is a nice advantage when copying: rules are not invoked by COPY command (but sometimes it is a disadvantage if you need the rule invocation) kind regards janning ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Rules vs Triggers
Am Dienstag, 26. Juli 2005 23:53 schrieb Randall Perry: Read the Rules section of the manual and the section on Rules vs Triggers. From what I get triggers are necessary for column constraints. As far as speed, it seems there are some differences between how fast rules/triggers would do the same action, but that some complex analysis is involved to determine this. And I gathered rules are necessary to allow update/insert/delete actions on views. Can anyone give me some simple reasons why they choose rules over triggers in their real-world dbs? Triggers are executed per row, so they are quite procedural. If you insert or update 500 rows they are fired 500 times. Rules modify the sql query tree. So rules are at some point nothing else as rewrites to your sql statement. If you update 500 rows and you have an on update rule. Your query tree is modified once and gets executed for all 500 rows. Rules are much faster an much more relational than triggers are, because they become pure sql before they reach the database. imagine an on delete trigger which record the deletion in an audit table like this: create trigger tg_member before delete on member for each row EXECUTE PROCEDURE audit_meber_deletion(); audit_meber_deletion() does an INSERT to an audit table. no think of members are organized in groups. If you delete a group ALL members are deleted because of cascading foreing keys references. Now delete a group with 2 members. The trigger is fired 2 times No Imagine a rule which does create rule rl_member AS ON DELETE TO member DO INSERT INTO member_deletion (membername) VALUES (OLD.membername) this is executed once and is as fast as SQL can be. Normally you dont see a difference between triggers and rules if you have update and insert statemnts which affect only a few rows. but if it comes to affecting many rows, you should use rules. But rules are more difficult to understand. kind regards, janning ---(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] Trigger disactivation and SELECT WAITING
Am Dienstag, 26. Juli 2005 16:07 schrieb Philippe Lang: Hi, I meant: in 7.4.X databases, is there a way of disabling a trigger without deleting it? I guess the answer is no. That's what my plpgsql insert function does, and because of this, if a view is running at the same moment on the same tables (some views can take up to 2 hours to be calculated), the insert function gets stuck in a SELECT WAITING state. So insertions are impossible in the database when views are being calculated. I guess you should rethink your databse design. Disabling triggers is convinient if your populate a database or you do bulk inserts, but you shouldn't disable them in a production database. In my experience rules are much more powerful and faster than triggers but on the other side much more difficult. Triggers are procedural. they fire on every inserted row. A rule is relational instead. If you use a rule you have only one more statement on insert even if you insert lots of data. On the other hand rules are not called by COPY Statements. And some things can't be done with rules. The waiting state ist ok, because other transaction can just not know if you commit your changes to the trigger or not. And i don't know what you mean with view is running for 2 hours i guess you have some functionality to build so called materialized views, right? if you give me some more information waht you are really doing i can help you. as your mail is .ch you might prefer german language and can contact via personal mail. kind regards, janning ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Query planner refuses to use index
Am Donnerstag, 21. Juli 2005 17:45 schrieb Kilian Hagemann: Hi there, I know this subject has come up before many times, but I'm struggling for hours with the following problem and none of the posts seem to have a solution. I have a table with a good 13 million entries with station_data=# \d speed Table public.speed Column | Type | Modifiers --+--+--- set_id | smallint | not null rec_time | abstime | not null wind_speed | smallint | Indexes: speed_pkey primary key, btree (set_id, rec_time) I use lots of queries of the form SELECT * FROM speed WHERE set_id=xxx AND rec_time =yyy where xxx is an integer and yyy is an abstime. At first, I battled to get the query planner to use an index at all, even when forcing, but http://archives.postgresql.org/pgsql-general/2001-09/msg01120.php suggested I need to use set_id=5::smallint. It works, but why is pg not intelligent enough to figure out that the literal 5 and smallint are compatible? So I thought I had solved my problem, but then it still refused to use the index, even though sequential scans are prohibitively expensive: station_data=# EXPLAIN ANALYZE SELECT * FROM speed WHERE set_id=5::smallint AND rec_time = '1999/01/01'::abstime; QUERY PLAN --- --- Seq Scan on speed (cost=0.00..276640.28 rows=677372 width=8) (actual time=14024.081..78236.525 rows=652389 loops=1) Filter: ((set_id = 5::smallint) AND (rec_time = '1999-01-01 00:00:00+02'::abstime)) Total runtime: 80156.330 ms When I explicitly turn off sequential scans by issuing SET enable_seqscan TO OFF;, I get what I want: station_data=# EXPLAIN ANALYZE SELECT * FROM speed WHERE set_id=5::smallint AND rec_time = '1999/01/01'::abstime; QUERY PLAN --- --- Index Scan using speed_pkey on speed (cost=0.00..2009924.87 rows=677372 width=8) (actual time=50.070..5775.698 rows=652389 loops=1) Index Cond: ((set_id = 5::smallint) AND (rec_time = '1999-01-01 00:00:00+02'::abstime)) Total runtime: 8819.371 ms which is 10 times faster. We're down to the last recommendation of section 11.8 in the documentation, so I increased the statistics gathered with SET default_statistics_target TO 50;, but that makes no difference either. Am I left to disable seqscans for eternity (in which case may I file a bug) or is there something else I might be missing? some hints: - You never want to set enable_seq off in production database. - did you run VACUUM ANALYZE speed lately? - what version are you running? - if you look at the cost values you can see what cost the planner expects. actual time is what it tooks to really execute the query. You see in example 1 with seq-scan the estimated cost is 276,640 and with index scan it is 2,009,924. So the planner chooses the seq scan. Maybe because you have not analyzed lately? - another parameter to look at is random_page_cost: Sets the planner's estimate of the cost of a nonsequentially fetched disk page. This is measured as a multiple of the cost of a sequential page fetch. A higher value makes it more likely a sequential scan will be used, a lower value makes it more likely an index scan will be used. The default is four. kind regards, janning ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Changes to not deferred FK in 8.0.3 to 7.4?
Am Dienstag, 19. Juli 2005 15:40 schrieb Tom Lane: Janning Vygen [EMAIL PROTECTED] writes: On more related question: I updated pg_trigger and pg_constraint and changed all my FK: UPDATE pg_trigger SET tgdeferrable = true, tginitdeferred = true WHERE tgconstrname LIKE 'fk_%' ; UPDATE pg_constraint SET condeferrable = true, condeferred = true WHERE conname LIKE 'fk_%' ; No, only the triggers that are for checks should be marked deferrable/deferred. These are the ones using functions RI_FKey_check_ins RI_FKey_check_upd RI_FKey_noaction_del RI_FKey_noaction_upd You want the others nondeferrable because (a) that's the standard behavior and (b) it'll ensure that the actions happen before the checks are made. ok thanks. i do it now like this: UPDATE pg_trigger SET tgdeferrable = true, tginitdeferred = true WHERE tgconstrname LIKE 'fk_%' AND tgfoid IN ( SELECT oid FROM pg_proc WHERE proname IN ( 'RI_FKey_check_ins', 'RI_FKey_check_upd', 'RI_FKey_noaction_del', 'RI_FKey_noaction_upd') ) ; UPDATE pg_constraint SET condeferrable = true, condeferred = true WHERE conname LIKE 'fk_%' ; COMMIT; This should work i hope, but i feel a little bit unsure if hacking the pg_catalog is a good way to do it. Maybe I should have take the long, but secure way by modifying the schema with ddl statements. kind regards, janning ---(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] Changes to not deferred FK in 8.0.3 to 7.4?
resending it because i used the wrong mail address. sorry! Am Montag, 18. Juli 2005 18:18 schrieb Tom Lane: Stephan Szabo [EMAIL PROTECTED] writes: On Mon, 18 Jul 2005, Tom Lane wrote: I don't see why. Except that before I think the order would have looked like (for 1 row) Originating Action Trigger A on originating table that does update Trigger B on originating table that does update Trigger A1 caused by A Trigger B1 caused by B I think now it acts like: Originating Action Trigger A on originating table that does update Trigger A1 caused by A Trigger B on originating table that does update Trigger B1 caused by B Ah, of course. So that could explain Janning's difference in results without having to assume any rearrangement from pg_dump (not but what we shouldn't take a second look at pg_dump's behavior anyway). a FK results in a referential action which updates the FK attributes and a referential constraint which checks if all FKs are ok, right? So my understanding of what's going on is: table1 / \ table2 table3 \ / table4 UPDATE Table1 PK = $2 WHERE PK = $1 - UPDATE Table2 FK = $2 WHERE FK = $1 - UPDATE Table4 FK1 = $2 WHERE FK1 = $1 - no action - CHECK table4 FK1 in table2 - CHECK table4 FK2 in table3(***) - CHECK table2 FK in table 1 - UPDATE Table3 FK = $2 WHERE FK = $1 - UPDATE Table4 FK2 = $2 WHERE FK2 = $1 - no action - CHECK table4 FK1 in table2 - CHECK table4 FK2 in table3 - CHECK table3 FK in table 1 - no check on table1 if fk1 and fk2 on table4 overlap in one column, i get an error at (***) because table3 is not updated at the moment. this error doesn't show up with deferrable constraints because all check clauses are moved to end of the transaction. so i think my problem is the overlapping of a FK in table4. In 7.4 the beahaviour was like this: UPDATE Table1 PK = $2 WHERE PK = $1 - UPDATE Table2 FK = $2 WHERE FK = $1 - UPDATE Table3 FK = $2 WHERE FK = $1 - no check on table1 - UPDATE Table4 FK1 = $2 WHERE FK1 = $1 - UPDATE Table4 FK2 = $2 WHERE FK2 = $1 - no action - CHECK table4 FK1 in table2 - CHECK table4 FK2 in table3 (***) - CHECK table4 FK1 in table2 - CHECK table4 FK2 in table3 - CHECK table2 FK in table 1 - CHECK table3 FK in table 1 I dont' got an error because table3 is already updated at (***) In my example there are NO circular references, they just overlap on table4 which is a common technique if you have natural primary keys. My feeling is: If you DON'T have circular references, you should not need defferable constraints. So I don't see any benefit of changes the order of execution, but anyway: two remarks: from the docs (CREATE TABLE) A constraint that is not deferrable will be checked immediately after every command. What means command in this sentence. Each Update which is triggered by a FK or my original statement? To me statment means the user statement. so checks should be done after statement and all fired trigger statements are complete. But this isn't the case. It should be A constraint that is not deferrable will be checked immediately after completion of the triggering query. From the realease notes:(8.0) Non-deferred AFTER triggers are now fired immediately after completion of the triggering query, rather than upon finishing the current interactive command. This makes a difference when the triggering query occurred within a function: the trigger is invoked before the function proceeds to its next operation. it should be mentioned, that is makes a difference if you have overlapping FKs like i have. I hope that all this stuff i just wrote is mostly correct and maybe it helps you improving postgresql. If i can help any further with a complete example, please let me know. On more related question: I updated pg_trigger and pg_constraint and changed all my FK: UPDATE pg_trigger SET tgdeferrable = true, tginitdeferred = true WHERE tgconstrname LIKE 'fk_%' ; UPDATE pg_constraint SET condeferrable = true, condeferred = true WHERE conname LIKE 'fk_%' ; did i make it right this time updating the pg_catalog? Or is there more to do in pg_catalog? kind regards janning ---(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] Changes to not deferred FK in 8.0.3 to 7.4?
Am Montag, 18. Juli 2005 18:18 schrieb Tom Lane: Stephan Szabo [EMAIL PROTECTED] writes: On Mon, 18 Jul 2005, Tom Lane wrote: I don't see why. Except that before I think the order would have looked like (for 1 row) Originating Action Trigger A on originating table that does update Trigger B on originating table that does update Trigger A1 caused by A Trigger B1 caused by B I think now it acts like: Originating Action Trigger A on originating table that does update Trigger A1 caused by A Trigger B on originating table that does update Trigger B1 caused by B Ah, of course. So that could explain Janning's difference in results without having to assume any rearrangement from pg_dump (not but what we shouldn't take a second look at pg_dump's behavior anyway). a FK results in a referential action which updates the FK attributes and a referential constraint which checks if all FKs are ok, right? So my understanding of what's going on is: table1 / \ table2 table3 \ / table4 UPDATE Table1 PK = $2 WHERE PK = $1 - UPDATE Table2 FK = $2 WHERE FK = $1 - UPDATE Table4 FK1 = $2 WHERE FK1 = $1 - no action - CHECK table4 FK1 in table2 - CHECK table4 FK2 in table3(***) - CHECK table2 FK in table 1 - UPDATE Table3 FK = $2 WHERE FK = $1 - UPDATE Table4 FK2 = $2 WHERE FK2 = $1 - no action - CHECK table4 FK1 in table2 - CHECK table4 FK2 in table3 - CHECK table3 FK in table 1 - no check on table1 if fk1 and fk2 on table4 overlap in one column, i get an error at (***) because table3 is not updated at the moment. this error doesn't show up with deferrable constraints because all check clauses are moved to end of the transaction. so i think my problem is the overlapping of a FK in table4. In 7.4 the beahaviour was like this: UPDATE Table1 PK = $2 WHERE PK = $1 - UPDATE Table2 FK = $2 WHERE FK = $1 - UPDATE Table3 FK = $2 WHERE FK = $1 - no check on table1 - UPDATE Table4 FK1 = $2 WHERE FK1 = $1 - UPDATE Table4 FK2 = $2 WHERE FK2 = $1 - no action - CHECK table4 FK1 in table2 - CHECK table4 FK2 in table3 (***) - CHECK table4 FK1 in table2 - CHECK table4 FK2 in table3 - CHECK table2 FK in table 1 - CHECK table3 FK in table 1 I dont' got an error because table3 is already updated at (***) In my example there are NO circular references, they just overlap on table4 which is a common technique if you have natural primary keys. My feeling is: If you DON'T have circular references, you should not need defferable constraints. So I don't see any benefit of changes the order of execution, but anyway: two remarks: from the docs (CREATE TABLE) A constraint that is not deferrable will be checked immediately after every command. What means command in this sentence. Each Update which is triggered by a FK or my original statement? To me statment means the user statement. so checks should be done after statement and all fired trigger statements are complete. But this isn't the case. It should be A constraint that is not deferrable will be checked immediately after completion of the triggering query. From the realease notes:(8.0) Non-deferred AFTER triggers are now fired immediately after completion of the triggering query, rather than upon finishing the current interactive command. This makes a difference when the triggering query occurred within a function: the trigger is invoked before the function proceeds to its next operation. it should be mentioned, that is makes a difference if you have overlapping FKs like i have. I hope that all this stuff i just wrote is mostly correct and maybe it helps you improving postgresql. If i can help any further with a complete example, please let me know. On more related question: I updated pg_trigger and pg_constraint and changed all my FK: UPDATE pg_trigger SET tgdeferrable = true, tginitdeferred = true WHERE tgconstrname LIKE 'fk_%' ; UPDATE pg_constraint SET condeferrable = true, condeferred = true WHERE conname LIKE 'fk_%' ; did i make it right this time updating the pg_catalog? Or is there more to do in pg_catalog? kind regards janning ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Changes to not deferred FK in 8.0.3 to 7.4?
[sorry for resending again. i am not at my usual desktop at the moment and used the wrong sender address] Am Dienstag, 19. Juli 2005 15:40 schrieb Tom Lane: Janning Vygen [EMAIL PROTECTED] writes: On more related question: I updated pg_trigger and pg_constraint and changed all my FK: UPDATE pg_trigger SET tgdeferrable = true, tginitdeferred = true WHERE tgconstrname LIKE 'fk_%' ; UPDATE pg_constraint SET condeferrable = true, condeferred = true WHERE conname LIKE 'fk_%' ; No, only the triggers that are for checks should be marked deferrable/deferred. These are the ones using functions RI_FKey_check_ins RI_FKey_check_upd RI_FKey_noaction_del RI_FKey_noaction_upd You want the others nondeferrable because (a) that's the standard behavior and (b) it'll ensure that the actions happen before the checks are made. ok thanks. i do it now like this: UPDATE pg_trigger SET tgdeferrable = true, tginitdeferred = true WHERE tgconstrname LIKE 'fk_%' AND tgfoid IN ( SELECT oid FROM pg_proc WHERE proname IN ( 'RI_FKey_check_ins', 'RI_FKey_check_upd', 'RI_FKey_noaction_del', 'RI_FKey_noaction_upd') ) ; UPDATE pg_constraint SET condeferrable = true, condeferred = true WHERE conname LIKE 'fk_%' ; COMMIT; This should work i hope, but i feel a little bit unsure if hacking the pg_catalog is a good way to do it. Maybe I should have take the long, but secure way by modifying the schema with ddl statements. kind regards, janning ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Changes to not deferred FK in 8.0.3 to 7.4?
Hi, in the release docs it says: Non-deferred AFTER triggers are now fired immediately after completion of the triggering query, rather than upon finishing the current interactive command. This makes a difference when the triggering query occurred within a function: the trigger is invoked before the function proceeds to its next operation. For example, if a function inserts a new row into a table, any non-deferred foreign key checks occur before proceeding with the function. I don't know if it relates to my problem: I have lots of tables with mutli-column PK and multi-column FK. All FK are cascading, so updating a PK should trigger through the whole database. This worked earlier in 7.4: UPDATE tipprunden SET tr_kurzname = 'schwarze2' where tr_kurzname = 'schwarze'; it should cacsade through lots of tables and other primary key as each table has at least a column of tr_kurzname. With 8.0.3 it get error messages like: ERROR: insert or update on table spieletipps violates foreign key constraint fk_tippspieltage2spiele DETAIL: Key (tr_kurzname,sp_id)=(schwarze2,197619) is not present in table tippspieltage2spiele. CONTEXT: SQL statement UPDATE ONLY public.spieletipps SET tr_kurzname = $1, mg_name = $2 WHERE tr_kurzname = $3 AND mg_name = $4 SQL statement UPDATE ONLY public.mitglieder SET tr_kurzname = $1 WHERE tr_kurzname = $2 What happens here to me is, that it cascades first from tipprunden to mitglieder to spieletipps. But tippspieltage2spiele relates to tipprunden as well, so updating spieletipps fails because the FK fk_tippspieltage2spiele fails as the table tippspieltage2spiele is not up to date at this moment. It makes sense to me when i reread the release notes. Not-deferred FK are checked immediatley not at the end of the statement so circular references cant' be handeled with not-deferrable FK !? Then i tried to make all my FK constraint to be deferrable and initially deferred like this: $ UPDATE pg_constraint set condeferrable= 't', condeferred='t' where conname LIKE 'fk_%'; Is it all what needs to be done to pg_catalog? Or did i miss something. But to me it looks ok as a table description with '\d' actually states deferrable initially deferred for all my FK. But with all FK deferred i still get the error above. If i drop a few FK completely to avoid a circular roundtrip everything works fine (but of course this is not an option as i need these FKs) Any help is very appreciated. kind regards, janning ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Changes to not deferred FK in 8.0.3 to 7.4?
Am Montag, 18. Juli 2005 16:28 schrieb Stephan Szabo: On Mon, 18 Jul 2005, Tom Lane wrote: Janning Vygen [EMAIL PROTECTED] writes: I have lots of tables with mutli-column PK and multi-column FK. All FK are cascading, so updating a PK should trigger through the whole database. This worked earlier in 7.4: UPDATE tipprunden SET tr_kurzname = 'schwarze2' where tr_kurzname = 'schwarze'; it should cacsade through lots of tables and other primary key as each table has at least a column of tr_kurzname. With 8.0.3 it get error messages like: ERROR: insert or update on table spieletipps violates foreign key constraint fk_tippspieltage2spiele DETAIL: Key (tr_kurzname,sp_id)=(schwarze2,197619) is not present in table tippspieltage2spiele. CONTEXT: SQL statement UPDATE ONLY public.spieletipps SET tr_kurzname = $1, mg_name = $2 WHERE tr_kurzname = $3 AND mg_name = $4 SQL statement UPDATE ONLY public.mitglieder SET tr_kurzname = $1 WHERE tr_kurzname = $2 What happens here to me is, that it cascades first from tipprunden to mitglieder to spieletipps. But tippspieltage2spiele relates to tipprunden as well, so updating spieletipps fails because the FK fk_tippspieltage2spiele fails as the table tippspieltage2spiele is not up to date at this moment. AFAICS, if it worked for you in 7.4 it was only by pure chance. There was not then, and is not now, any logic that would prevent the FK checks from being applied in an order you don't want. True, although I think in 7.4 it was more likely to work since the check triggers would be put on the trigger queue after the first level of referential action triggers rather than be run immediately between, right? I'm not sure when the triggered update's constraint checks are supposed to fire (is it as part of the referential action's updating action or the original query's constraint checks at end of statement?) ok, i understand that circular references are checked in any order and it worked by luck in 7.4. But why doesn't it work if i make alle FK deferrable initially deferred? IMHO the check should occur at the end of the transaction, right? So at this time alle PK and FK should be updated and everything should work fine. But it doesn't. Or did i just get the pg_catalog update statment wrong making all my fk deferrable inititally deferred? i am kind of helpless. kind regards, janning ---(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] Changes to not deferred FK in 8.0.3 to 7.4?
Am Montag, 18. Juli 2005 16:56 schrieb Tom Lane: Janning Vygen [EMAIL PROTECTED] writes: But why doesn't it work if i make alle FK deferrable initially deferred? You didn't do it right --- I don't believe the code actually looks at pg_constraint, it looks at pg_trigger. And if you are going to hack pg_trigger directly, be careful to only change the check-trigger rows not the action-trigger rows. I forget which is which but Stephen probably remembers. as always: you are absolutly right! I should have checked it properly with the correct syntax before asking wizards. Sorry for stealing your time. damn. i felt a little bit like a hacker by manipulating pg_catalog. regards Janning ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PLPGSQL how to get transaction isolation level info
Am Freitag, 15. Juli 2005 19:19 schrieb Tom Lane:
Janning Vygen [EMAIL PROTECTED] writes:
How can a function determine in which isolation level it runs?
select current_setting('transaction_isolation');
Thank you for the hint.
I didn't find it myself because tab completion on SHOW doesn't show it.
But i read the docs again carfully and found SHOW all which shows
transaction_isolation. I guess it would be fine if the refernce page for
SHOW and current_setting() list all the possible configuration settings.
And it should be added in bin/psql/tab-complete.c but maybe it is already
done. i dont have the cvs repository source code at hand. Maybe someon can
pick it up.
kind regards,
janning
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[GENERAL] PLPGSQL how to get transaction isolation level info
i have a function which calculates some aggregates (like a materialized view). As my aggregation is made with a temp table and 5 SQL Queries, i need a consistent view of the database. Therefor i need transaction isolation level SERIALIZABLE, right? Otherwise the second query inside of the function could read data which differs from the first query (Nonrepeatable Read or Phantom Read) ok. so far so good. But know i would like my function to abort if it is not running inside ransaction isolation level SERIALIZABLE. How can a function determine in which isolation level it runs? I looked at the SHOW statement but didn't find anything. i dont wnat to know the default_transaction_isolation but the current one used. The reason is mainly for preventing some mistakes inside the caller app. Of course the app should know what it does and wrap the function call inside a serializable transaction, but to be sure that my materialized view is consistent with the rest of the data i would like to enforce it. Is it possible to get info about the current transaction isolation level? kind regards, janning ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] strange error with temp table: pg_type_typname_nsp_index
Am Mittwoch, 13. Juli 2005 16:04 schrieb Tom Lane: Janning Vygen [EMAIL PROTECTED] writes: I was just testing some configuration settings, especially increasing shared_buffers and setting fsync to false. And suddenly it happens 3 times out of ten that i get this error. Could you put together a complete example --- that is a script someone else could run to see this error from a standing start? i tried but the error mentioned above doesn't occur anymore. I dont know why. but i get another error which looks similar to me because both errors deal with temporary tables. 982 sfb69 ERROR: cache lookup failed for relation 14138243 CONTEXT: SQL statement SELECT n.nspname ,c.relname FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname like 'pg_temp_%' AND pg_catalog.pg_table_is_visible(c.oid) AND Upper(relname) = 'TEMP_GC' PL/pgSQL function tsptcache_update line 16 at perform SQL statement SELECT tsptcache_update( $1 , $2 , $3 ) PL/pgSQL function cache_update line 15 at perform i copied the query from a archive message but maybe it's not as robust as i thought and all stuff relates to this query. It should check if a given temp table is already created inside this session. if not it should be recreated: PERFORM n.nspname ,c.relname FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname like 'pg_temp_%' AND pg_catalog.pg_table_is_visible(c.oid) AND Upper(relname) = 'TEMP_GC' ; IF NOT FOUND THEN CREATE TEMP TABLE temp_gc ( mg_name text NOT NULL, gc_tsptpunkte int4 NOT NULL DEFAULT 0, gc_tsptsieg int4 NOT NULL DEFAULT 0, gc_gesamtsiege float NOT NULL DEFAULT 0.0, gc_bonuspunkte int4 NOT NULL DEFAULT 0, gc_gesamtpunkte int4 NOT NULL DEFAULT 0, gc_prev_rankint4 NOT NULL DEFAULT 9, gc_rank int4 NOT NULL DEFAULT 9 ) WITHOUT OIDS; ELSE TRUNCATE TABLE temp_gc; END IF; but as i looked at the system catalogs pg_temp it is like that every session can see the temporary tables of any other session. so the whole story about the query above is wrong. It checks if ANY session has a temporrary table gc_temp and not my own session. The error occured when i cancelled a query (strg-c) and quickly rerun it. I guess that the pg_catalog is not tidied up at that time, so the query results to true because the temp table is still inside another session. i guess my whole temporary table function ist buggy or i have to use EXECUTE all the time. hmm. i have to learn a lot more, i guess. kind regards, janning ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] getting the ranks out of items with SHARED
Am Mittwoch, 13. Juli 2005 15:35 schrieb Tom Lane: Janning Vygen [EMAIL PROTECTED] writes: this way it works: CREATE TEMP TABLE ranking AS *Q*; EXECUTE 'UPDATE temp_gc SET gc_rank = ranking.rank FROM ranking WHERE temp_gc.mg_name = ranking.mg_name;'; and this way it doesn't: UPDATE temp_gc SET gc_rank = ranking.rank FROM (*Q*) ranking WHERE temp_gc.mg_name = ranking.mg_name; It's difficult to be sure without looking at EXPLAIN output, but I would guess that the second query is being done with a plan that involves multiple scans of *Q*, and that's confusing your function. regards, tom lane here you are. both versions with explain output first version creates temp table (explain no 1) and updates afterwards (explain no. 2). second version combines both (explain no.3 ) [whats the best way to post explain output? My mailclient wraps the output. i hope it is still readable] no 1 * first create temp table * QUERY PLAN - Subquery Scan r1 (cost=58.54..70.99 rows=830 width=44) (actual time=0.186..0.403 rows=7 loops=1) - Sort (cost=58.54..60.62 rows=830 width=44) (actual time=0.106..0.137 rows=7 loops=1) Sort Key: gc_gesamtpunkte, gc_gesamtsiege, mg_name - Seq Scan on temp_gc (cost=0.00..18.30 rows=830 width=44) (actual time=0.015..0.052 rows=7 loops=1) Total runtime: 0.470 ms (5 Zeilen) no 2 update statement references temp table QUERY PLAN - Merge Join (cost=127.70..193.49 rows=4109 width=70) (actual time=0.221..0.404 rows=7 loops=1) Merge Cond: (outer.mg_name = inner.mg_name) - Sort (cost=58.54..60.62 rows=830 width=66) (actual time=0.110..0.142 rows=7 loops=1) Sort Key: temp_gc.mg_name - Seq Scan on temp_gc (cost=0.00..18.30 rows=830 width=66) (actual time=0.013..0.055 rows=7 loops=1) - Sort (cost=69.16..71.63 rows=990 width=36) (actual time=0.089..0.119 rows=7 loops=1) Sort Key: ranking.mg_name - Seq Scan on ranking (cost=0.00..19.90 rows=990 width=36) (actual time=0.006..0.042 rows=7 loops=1) Total runtime: 0.525 ms (9 Zeilen) no 3 combined update statement QUERY PLAN - Merge Join (cost=167.70..232.14 rows=3445 width=78) (actual time=0.455..0.774 rows=7 loops=1) Merge Cond: (outer.mg_name = inner.mg_name) - Sort (cost=58.54..60.62 rows=830 width=66) (actual time=0.111..0.142 rows=7 loops=1) Sort Key: temp_gc.mg_name - Seq Scan on temp_gc (cost=0.00..18.30 rows=830 width=66) (actual time=0.016..0.057 rows=7 loops=1) - Sort (cost=109.16..111.23 rows=830 width=44) (actual time=0.248..0.281 rows=7 loops=1) Sort Key: r1.mg_name - Subquery Scan r1 (cost=58.54..68.92 rows=830 width=44) (actual time=0.102..0.201 rows=7 loops=1) - Sort (cost=58.54..60.62 rows=830 width=44) (actual time=0.092..0.125 rows=7 loops=1) Sort Key: gc_gesamtpunkte, gc_gesamtsiege, mg_name - Seq Scan on temp_gc (cost=0.00..18.30 rows=830 width=44) (actual time=0.008..0.045 rows=7 loops=1) Total runtime: 0.886 ms (12 Zeilen) and this is the combined statement: UPDATE temp_gc SET gc_rank = ranking.rank FROM ( SELECT *, ranking(r1.gc_gesamtpunkte, r1.gc_gesamtsiege) AS rank FROM ( SELECT mg_name, gc_gesamtpunkte, gc_gesamtsiege FROM temp_gc ORDER BY gc_gesamtpunkte DESC, gc_gesamtsiege DESC, mg_name ASC ) AS r1 ) AS ranking WHERE temp_gc.mg_name = ranking.mg_name; to me it looks like the call to ranking() is moved from the subquery plan upwards. but i really can't interpret this explain output regarding to where the ranking funcion is called. my problem is that getting a rank out of items is very expensive with aggregate functions, so i try to do a trick here which is not very relational indeed and this of course can lead to trouble. Maybe i have to rethink the whole stuff. (because i get some temp table errors anyway as mentioned in the other thread) Kind regards, Janning Vygen ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] strange error with temp table: pg_type_typname_nsp_index
Hi,
[i am using Postgresql version 8.0.3]
yesterday i posted a mail regarding a function which calculates a ranking with
a plperl SHARED variable.
Today i ve got some problems with it:
FEHLER: duplizierter Schlüssel verletzt Unique-Constraint
»pg_type_typname_nsp_index«
CONTEXT: SQL-Anweisung »CREATE TEMP TABLE ranking AS SELECT *,
ranking(r1.gc_gesamtpunkte, r1.gc_gesamtsiege) AS rank FROM ( SELECT mg_name,
gc_gesamtpunkte, gc_gesamtsiege FROM temp_gc ORDER BY gc_gesamtpunkte DESC,
gc_gesamtsiege DESC, mg_name ASC ) AS r1«
[it meens: ERROR: duplicate key violates UNIQUE-Constraint]
I am running a stats collector function inside a transaction with isolation
level serializable.
the code which throws an error is the following:
snip
CREATE OR REPLACE function cacheresult(text) RETURNS boolean LANGUAGE 'plperl'
AS $$
[...]
PERFORM reset_ranking();
CREATE TEMP TABLE ranking AS
SELECT
*,
ranking(r1.gc_gesamtpunkte, r1.gc_gesamtsiege) AS rank
FROM (
SELECT
mg_name,
gc_gesamtpunkte,
gc_gesamtsiege
FROM temp_gc
ORDER BY gc_gesamtpunkte DESC, gc_gesamtsiege DESC, mg_name ASC
) AS r1
;
EXECUTE '
UPDATE temp_gc
SET gc_rank = ranking.rank
FROM ranking
WHERE temp_gc.mg_name = ranking.mg_name;
';
DROP TABLE ranking;
[...]
snip
and the ranking function is as follows:
CREATE OR REPLACE function ranking(int4, float) RETURNS int4 LANGUAGE 'plperl'
AS $$
my %this;
$this{'gesamtpunkte'} = shift;
$this{'sptsiege'} = shift;
$this{'ranking'} = $_SHARED{'prev'}{'ranking'};
$this{'count'}= $_SHARED{'prev'}{'count'} + 1;
$_SHARED{'prev'}{'gesamtpunkte'} = -1 if !defined $_SHARED{'prev'}
{'gesamtpunkte'};
$this{'ranking'} = $this{'count'} unless
$this{'gesamtpunkte'} == $_SHARED{'prev'}{'gesamtpunkte'}
and $this{'sptsiege'} == $_SHARED{'prev'}{'sptsiege'}
;
$_SHARED{'prev'} = \%this;
return $this{'ranking'};
$$;
snip
the function is called many times inside the same transaction.
Tom Lane wrote in a another thread regarding 7.4
[ http://archives.postgresql.org/pgsql-novice/2004-11/msg00246.php ]
It looks like the source of the problem is an
only-partially-deleted temp table left behind by some prior failure.
Specifically, the rowtype entry for the table is still there in
pg_type, though its pg_class entry must be gone or you'd have gotten
a different error message. This seems pretty odd, since the catalog
entries should have been deleted in a single transaction.
I was just testing some configuration settings, especially increasing
shared_buffers and setting fsync to false. And suddenly it happens 3 times
out of ten that i get this error.
It seems to me that setting fsync to false was not a good idea...
Is it a bug? I dont know. What can i do to prevent it? What might be the
reason for this error?
kind regards,
janning
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] getting the ranks out of items with SHARED
Hi, Am Mittwoch, 13. Juli 2005 00:03 schrieb Tom Lane: Janning Vygen [EMAIL PROTECTED] writes: I have a guess, what happens here: The order of the subselect statement is dropped by the optimizer because the optimizer doesn't see the side-effect of the ranking function. That guess is wrong. ah, and i already thought to be already on a higher level of understanding postgresql... I think the problem is that you are trying to update multiple rows in the same statement, which would require a reset ranking between each row, which this approach doesn't provide for. no thats not the point, i guess (which might be wrong again) but i still don't understand why it doesn't work: this is my important query named *Q* := SELECT *, ranking(r1.gc_gesamtpunkte, r1.gc_gesamtsiege) AS rank FROM ( SELECT mg_name, gc_gesamtpunkte, gc_gesamtsiege FROM temp_gc ORDER BY gc_gesamtpunkte DESC, gc_gesamtsiege DESC, mg_name ASC ) AS r1 this way it works: CREATE TEMP TABLE ranking AS *Q*; EXECUTE 'UPDATE temp_gc SET gc_rank = ranking.rank FROM ranking WHERE temp_gc.mg_name = ranking.mg_name;'; and this way it doesn't: UPDATE temp_gc SET gc_rank = ranking.rank FROM (*Q*) ranking WHERE temp_gc.mg_name = ranking.mg_name; ; i want to update multiple rows, but the all data in table temp_gc doesnt need a reset of the ranking. The whole thing looks mighty fragile in other ways; anything involving a single global variable isn't going to work nicely in very many cases. Consider casting your solution as an aggregate instead... I know that this is not the best solution but it is the fastest. A corrolated subquery with aggregates takes ages in opposite to the ranking function solution. But by the time of writing i have a new problem with my solution posted today with subject strange error with temp table: pg_type_typname_nsp_index kind regards, janning ---(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] getting the ranks out of items with SHARED
Hi,
in postgresql you have several possibilites to get the rank of items. A thread
earlier this year shows correlated subqueries (not very performant) and other
tricks and techniques to solve the ranking problem:
http://archives.postgresql.org/pgsql-general/2005-05/msg00157.php
The possibility to use a SHARED variable in plperl can be another nice way to
get a rank of items. ( good example showing SHARED in use is at
http://www.varlena.com/varlena/GeneralBits/114.php)
So i wrote two functions for my purpose:
CREATE OR REPLACE function ranking(int4) RETURNS int4 LANGUAGE 'plperl' AS $$
my %this;
$this{'punkte'} = shift;
$this{'ranking'} = $_SHARED{'prev'}{'ranking'}; # defaults to 0
$this{'count'} = $_SHARED{'prev'}{'count'} + 1; # defaults to 1
$this{'ranking'} = $this{'count'} unless
$this{'punkte'} == $_SHARED{'prev'}{'punkte'};
$_SHARED{'prev'} = \%this;
return $this{'ranking'};
$$;
CREATE OR REPLACE FUNCTION reset_ranking() RETURNS void LANGUAGE 'plperl' AS
$$
$_SHARED{'prev'} = undef;
$$;
Nice Thing: the function drops rankings which other ranking solutions in the
given thread can't. Like this:
rank | points
-
1| 10
2| 9
2| 9
4| 8
5| 7
It drops rank 3 because we have to entries for second rank.
It would be even nice if you can write a genric ranking() function which takes
anyarray as an argument, but as far as i know you can't pass an anyarray to
a plperl function, right?
Now i can do the following in plpsql Function which updates a caching table
for me and it works fine:
PERFORM reset_ranking();
CREATE TEMP TABLE ranking AS
SELECT
*,
ranking(r1.gc_gesamtpunkte) AS rank
FROM (
SELECT
mg_name,
gc_gesamtpunkte
FROM temp_gc
ORDER BY gc_gesamtpunkte DESC, mg_name ASC
) AS r1
;
EXECUTE '
UPDATE temp_gc
SET gc_rank = ranking.rank
FROM ranking
WHERE temp_gc.mg_name = ranking.mg_name;
';
DROP TABLE ranking;
Problems arrise when you try to do the select and update step together without
any temporary table in between:
PERFORM reset_ranking();
UPDATE temp_gc SET gc_rank = ranking.rank
FROM (
SELECT
*,
ranking(r1.gc_gesamtpunkte) AS rank
FROM (
SELECT
mg_name,
gc_gesamtpunkte
FROM temp_gc
ORDER BY gc_gesamtpunkte DESC, mg_name ASC
) AS r1
) AS ranking
WHERE temp_gc.mg_name = ranking.mg_name;
';
I have a guess, what happens here: The order of the subselect statement is
dropped by the optimizer because the optimizer doesn't see the side-effect
of the ranking function. that's ok because using such functions isn't SQLish,
i guess.
Is there a way to FORCE the optimizer to keep things orders like the sql
statement author wanted it?
kind regards,
janning
---(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] Finding points within 50 miles
Am Montag, 27. Juni 2005 01:40 schrieb CSN: If I have a table of items with latitude and longitude coordinates, is it possible to find all other items that are within, say, 50 miles of an item, using the geometric functions (http://www.postgresql.org/docs/8.0/interactive/functions-geometry.html)? If so, how? I did it without some special features and datatypes some time ago. feel free to modify and use for your own. It should give you an idea how to do it. SELECT c1.zip, c2.zip, 6378.388 * acos( sin(radians(c1.latitude)) * sin(radians(c2.latitude)) + cos(radians(c1.latitude)) * cos(radians(c2.latitude)) * cos(radians(c1.longitude - c2.longitude)) ) AS distance FROM coordinates AS c1 CROSS JOIN coordinates AS c2 I had some problems with the calculation inside acos() sometimes being greater than 1, which should not occur. Please use a CASE WHEN sin(...) 1 THEN 1 ELSE sin(...) END if you have the same problem. kind regards, janning ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] create rule ... as on insert
Am Freitag, 24. Juni 2005 17:05 schrieb Omachonu Ogali: I'm using PostgreSQL 7.4.2, and I'm having a little issue with rules. I created a rule to watch for any inserts to table XYZ, and registered a listener. But as I simply do a select on the table, I receive several notifications when nothing has been inserted into the table. [...] I'm the only one with full rights to this table, and the only one that is currently working on this database. There is another rule that I've created (and is currently active), that does inserts into xyz from another table, but as you can see below, there are no new inserts. db=# select * from xyz; ( 2 rows) there is no need that something is really inserted i guess. from the docs: Presently, if a rule action contains a NOTIFY command, the NOTIFY command will be executed unconditionally, that is, the NOTIFY will be issued even if there are not any rows that the rule should apply to. For example, in CREATE RULE notify_me AS ON UPDATE TO mytable DO NOTIFY mytable; UPDATE mytable SET name = 'foo' WHERE id = 42; [sql-createrule.html] So maybe your other rule is trying to insert nothing like the UPDATE command in the example above. kind regards, janning ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] One Sequence for all tables or one Sequence for each table?
Hi,
if you define a SERIAL column postgresql's default is to generate a sequence
for each SERIAL column (table_column_seq). But you can use one sequence for
the whole database like this:
CREATE dbsequence;
CREATE TABLE one (
id int4 NOT NULL DEFAULT nextval('dbseq')
);
CREATE TABLE two (
id int4 NOT NULL DEFAULT nextval('dbseq')
);
One drawback: You reach the internal end of a sequence faster if you use your
sequence for all tables. But this can be avoided if you use int8 datatype.
Are there other drawbacks/benfits using one Sequence for each table or one
sequence for all tables?
kind regards,
janning
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] One Sequence for all tables or one Sequence for each
Am Donnerstag, 2. Juni 2005 12:03 schrieb Martijn van Oosterhout: On Thu, Jun 02, 2005 at 12:58:33PM +0300, Kaloyan Iliev Iliev wrote: Hi, I suppose the paralel work will be a problem if you are using one sequence for all tables. I don't know about this. Sequences are designed to be very efficient, they don't rollback and can be cached by backends. In several of the databases I setup, I sometimes arranged for sequences to start at different points so when you setup a foreign key there was no chance you linked it to the wrong table. This especially in cases where there might be confusion about which table links where. Using one serial for everything does this even better. As for performance, I think disk I/O is going to be an issue before getting sequence numbers will be... I guess i will use one sequence for all tables if there are now drawbacks. BTW: OIDs are using the same conecpt, don't they? And for me it makes sense to use a sequence only for getting a unique identifier and nothing else. even better if this identifier is unique among all tables. Thanks a lot for your opinions! regards janning ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] User def. Functions for sysadmin tasks?
Hi, i like to use postgresql for managing my postfix mailserver via lookup tables. for each mailbox domain i have a system account to have quotas per domain. (i know there are other solutions like postfix-vda and so on) When i add a domain to the mailsystem i have to add a user account for this domain and afterwards do one INSERT statement to add the domain to the postfix tables. Does it make sense to write a C Function which triggers before insert of a domain and adds the appropiate user to the system? The C Function runs as user postgres, right?. So i have to add postgres to /etc/sudoers file to allow execution of /usr/sbin/adduser as root, right? The same way you could manage web accounts. The C Functions writes or updates httpd.conf files to disk. Or write .htpasswd files and so on. Most administration systems are running a cron job to do those tasks but it would be nicer to have the webserver running in the moment the transaction succeeds. And another benefit: you have all logic inside your database. I thought about any security reasons not to do it. One Thought: If you can become postgres you could add users to the system. But if you can become postgres you could delete all data files anyway which would be much more serious than adding or deleting users.!? Did anybody tried something like this before? Does it make sense? kind regards, janning ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] invalid input syntax for type bytea
Hi, i have a databse in postgresql 7.4 with some pdf files in a bytea column. everything works fine but sometimes when i dump and restore i get psql:../tmp/dump.sql:704022: ERROR: invalid input syntax for type bytea CONTEXT: COPY dk_dokument, line 127, column dk_content: %PDF-1.4\015%\342\343\317\323\015\01220 0 obj\015 \015/Linearized 1 \015/O 22 \015/H [ 660 209 ] \... how can things like this happen? Is it a problem with COPY command? I guess my input into bytea must be ok otherwise postgresql should report an error on insert statmenet, shouldn't it? kind regards, janning -- PLANWERK 6 websolutions Herzogstraße 85, 40215 Düsseldorf Tel.: 0211-6015919 Fax: 0211-6015917 http://www.planwerk6.de/ ---(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] invalid input syntax for type bytea
Am Montag, 4. April 2005 17:36 schrieb Tom Lane: Janning Vygen [EMAIL PROTECTED] writes: i have a databse in postgresql 7.4 with some pdf files in a bytea column. everything works fine but sometimes when i dump and restore i get psql:../tmp/dump.sql:704022: ERROR: invalid input syntax for type bytea CONTEXT: COPY dk_dokument, line 127, column dk_content: %PDF-1.4\015%\342\343\317\323\015\01220 0 obj\015 \015/Linearized 1 \015/O 22 \015/H [ 660 209 ] \... You might look into whether your handling of the dump file is perfectly clean --- is there any possibility of newline transformations getting applied to it, for example? AFAICS the above cannot happen if the identical data dumped out by pg_dump is reloaded. what i do is part of my nightly cronjob. On my DB Server i do: $ /usr/local/pgsql/bin/pg_dump -O myuser dump.sql On my Testserver i do: $ scp -q -C [EMAIL PROTECTED]:dump.sql ./dump.sql $ cp ./dump.sql ~/dump.sql $ psql -q -f ~/dump.sql dbname /dev/null I can't see any command which does newline translations or something else, but maybe my '-C' in scp makes the difference. I had problems with pg_dump when i used the compressed format with -Fc But both servers should be identical. But i compared gzip, ssh, and zlib and they are not: dbserver$ openssh-3.5p1-42 testserver$ openssh-3.5p1-107 The rest is identical. i am wondering about this minor package number making any difference. Ok. It seems to be my problem and not postgresql specific. Many thanks for setting me on the right track. kind regards, Janning Vygen ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] invalid input syntax for type bytea
Hi, i have a databse in postgresql 7.4 with some pdf files in a bytea column. everything works fine but sometimes when i dump and restore i get psql:../tmp/dump.sql:704022: ERROR: invalid input syntax for type bytea CONTEXT: COPY dk_dokument, line 127, column dk_content: %PDF-1.4\015%\342\343\317\323\015\01220 0 obj\015 \015/Linearized 1 \015/O 22 \015/H [ 660 209 ] \... how can things like this happen? Is it a problem with COPY command? I guess my input into bytea must be ok otherwise postgresql should report an error on insert statmenet, shouldn't it? kind regards, janning ---(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] pg_xlog disk full error, i need help
Am Dienstag, 29. März 2005 16:37 schrieb Tom Lane:
Janning Vygen [EMAIL PROTECTED] writes:
Am Montag, 28. März 2005 18:06 schrieb Tom Lane:
The only way for pg_xlog to bloat vastly beyond what it's supposed to be
(which is to say, about twice your checkpoint_segments setting) is if
checkpoints are somehow blocked from happening. The only mechanism I
know about for that is that in 7.4.* (maybe 7.3.* too) a very large
btree CREATE INDEX or REINDEX operation can block checkpoints until it
completes. Did you have something like that going on?
It looks like something/someone wrote so much data in my table that the
nightly clustering process just didn't succeed because of disk full
failure after writing too many pg_xlog files. The writing of so many
pg_xlog files now makes more sense to me when clustering 68 GByte of
data.
Yeah, CLUSTER proceeds by rewriting the table and then invoking REINDEX
on each index, so the checkpoint lockout problem will apply. Tell you
the truth, the best and perhaps only answer for you is to update to 8.0
where that problem is solved.
How do i get the tablename using this filenode? (restarting the database
is not an option)
Sure it is. pg_resetxlog will allow you to restart ... possibly you
will lose some transactions, but if the only thing going on was the
CLUSTER, nothing of value will be lost.
* I just can't restart it:
I zipped all my pg_xlog files in the crashed database to have enough space to
get my backup running.
As my database server is not in my LAN i can't download 100 GB of files and i
can't gunzip all the pg_xlog files again to start it on the same server.
So i could delete all files in my pg_xlog directory and then try to start the
database with another compiled instance of postgresql. But as this is a
production database, its not a good idea.
And i cant move 100 GB (or only 60 GB in the base directory) to another server
because no server has enough space nor is transfering 60 GB very cheap. I
have no idea how to get it running again.
* But what i really want to know is how to interpret the results of
pg_filedump. I didn't found any documentation besides the README. Most output
is easy to understand, others are not. example: what means Flags: USED in a
data item?
* anyway: i am still confused how my table could get this big over night.
I stop all database activities by shutting down apache in a nightly cronjob
and then my nightly job runs
pg_dump -Fc $DBNAME $BACKUP_FILE
psql -c 'SELECT update_tspt_aktuell();' $DBNAME
psql -c 'CLUSTER;' $DBNAME
a) The dump file ist just fine and the one table is not as big as 60 GB! The
whole base directory after reinstalling is 1.4 GB.
b) The Function is this:
CREATE OR REPLACE FUNCTION update_tspt_aktuell () RETURNS integer LANGUAGE
'plpgsql' AS '
DECLARE
var_count integer;
BEGIN
UPDATE Tippspieltage SET tspt_aktuell = false WHERE tspt_aktuell;
UPDATE Tippspieltage SET tspt_aktuell = true
FROM
(
SELECT DISTINCT ON (tspt2sp.tr_kurzname)
tspt2sp.tr_kurzname,
tspt2sp.tspt_sort,
MIN(abs(EXTRACT(epoch FROM date_trunc(''day'', sp.sp_termin) -
CURRENT_DATE))) AS timediff
FROM
Tippspieltage2Spiele AS tspt2sp
LEFT JOIN Spiele AS sp USING (sp_id)
GROUP BY
tspt2sp.tr_kurzname,
tspt2sp.tspt_sort
ORDER BY
tspt2sp.tr_kurzname,
timediff ASC,
tspt2sp.tspt_sort DESC
) as tspt_akt
WHERE
Tippspieltage.tr_kurzname = tspt_akt.tr_kurzname
AND Tippspieltage.tspt_sort = tspt_akt.tspt_sort
;
GET DIAGNOSTICS var_count = ROW_COUNT;
RETURN var_count;
END;
';
my cron mail reports success and modified rows:
update_tspt_aktuell
-
5872
(1 row)
So there is no reason i can see that this function produced 60 GB of data.
c) after this function cluster fails. The Clustering fails starting with
PANIC: could not write to file
/home/postgres/data/pg_xlog/xlogtemp.24223: No space left on device
the cron job took 2:15 hours to run and to report this failure. I guess the
clustering is somewhat broken (maybe because i use many multi-column natural
keys)
* conclusion: i think i will stop clustering every night until i upgraded to
8.0, but it leaves me very unsatisfied not to know the reason for this kind
of db failure (or human error or whatever it is)
What I would expect to be happening in a CLUSTER is that there would be
an old file plus a new file of similar size, for both the table
itself and each index that's been processed (up to the point of failure,
where you will have a partially-written new index). After restart with
this method, you will find only the old files listed in pg_class.
You'll want to manually delete the unreferenced new files.
Ok but the clustering should not scale the file from 500 MB to 64 GB
(separated on many files).
another strange thing: i looked at one of my
[GENERAL] pg_xlog disk full error, i need help
Hi, i do a nightly CLUSTER and VACUUM on one of my production databases. Yesterday in the morning the vacuum process was still running after 8 hours. That was very unusal and i didnt know exactly what to do. So i tried to stop the process. After it didnt work i killed -9 the Vacuum process. I restarted the database and everything worked fine again. I did know that this was NOT a good idea but i had to fined a quick solution and it did work at least. Tonight know something very strange did happen before or while the clustering did run: PANIC: could not write to file /home/postgres/data/pg_xlog/xlogtemp.6434: No space left on device server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. connection to server was lost WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. connection to server was lost My disk was running full with 100 GB (!) of data/pg_xlog/ files. I tried to delete some files on the same partition after which i had 3 GB free space again. the i tried to start the postmaster: the startup process logged this: LOG: database system shutdown was interrupted at 2005-03-28 09:33:15 CEST LOG: checkpoint record is at F/EE0F0010 LOG: redo record is at F/EC007900; undo record is at 0/0; shutdown FALSE LOG: next transaction ID: 46558173; next OID: 58970 LOG: database system was not properly shut down; automatic recovery in progress LOG: redo starts at F/EC007900 looks fine as it says automatic recovery in progress but there are no more log entries since startup and my process table says: 8495 pts/0S 0:00 /usr/local/pgsql/bin/postmaster -D /home/postgres/data 8498 pts/0S 0:00 postgres: stats buffer process 8499 pts/0S 0:00 postgres: stats collector process 8500 pts/0D 5:15 postgres: startup subprocess and top says 8500 postgres 15 0 131m 131m 131m D 18.9 6.5 5:18.26 postmaster so the postmaster is still working. How long will it work on this problem? Can i expect to have everything working correctly after this startup process or shoul di stop it and use a backup (which i hope is useful and not corrupt) I am kind of helpless in this situation as i dont know much of all the underlying storage, WAL and xlog things. Maybe i could just delete all files in this directory? Can anybody give me some hints what to do or how to ask? i am really desperate at the moment. kind regards, Janning please excuse bad english and typos. i am kind of nervous at the moment. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] pg_xlog disk full error, i need help
Am Montag, 28. Mrz 2005 13:46 schrieb Gustavo Franklin Nbrega - Planae: Hi Janning! You need to expand your pg_xlog partition. If you use reiserfs, you can do this with resize_reiserfs. If you use ext2/ext3 you may try reise2fs. This is not an option to me at the moment. because my disk is only 120 GB. With 100 GB of xlog files i dont have any space at all besides some small amount of disk space on other partitions like /usr etc. If you need to repartition your filesystem, by myself experience, I recommend to you to use LVM. With LVM, you can expand easily, add more disk to you volume group and grow your logical volume. This already saved me some hours of maintenance a time. Yes, i really need to take a look to LVM after cooling down again. kind regards, janning Atenciosamente, Gustavo Franklin Nbrega Infraestrutura e Banco de Dados Planae Tecnologia da Informao (+55) 14 2106-3514 http://www.planae.com.br - Original Message - From: Janning Vygen [EMAIL PROTECTED] To: pgsql-general@postgresql.org Sent: Monday, March 28, 2005 7:19 AM Subject: [GENERAL] pg_xlog disk full error, i need help PANIC: could not write to file /home/postgres/data/pg_xlog/xlogtemp.6434: No space left on device server closed the connection unexpectedly This probably means the server terminated abnormally before or while -- PLANWERK 6 websolutions Herzogstrae 85, 40215 Dsseldorf Tel.: 0211-6015919 Fax: 0211-6015917 http://www.planwerk6.de ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] pg_xlog disk full error, i need help
Am Montag, 28. März 2005 18:06 schrieb Tom Lane: Janning Vygen [EMAIL PROTECTED] writes: My disk was running full with 100 GB (!) of data/pg_xlog/ files. The only way for pg_xlog to bloat vastly beyond what it's supposed to be (which is to say, about twice your checkpoint_segments setting) is if checkpoints are somehow blocked from happening. The only mechanism I know about for that is that in 7.4.* (maybe 7.3.* too) a very large btree CREATE INDEX or REINDEX operation can block checkpoints until it completes. Did you have something like that going on? first of all i have 7.4 running. A CLUSTER was running which to me is somewhat similiar to REINDEX, isn't it? And the night before i killed -9 my nightly vacuum process which did not return after 6 hours or so. first i tried to stop the postmaster with the init.d script, which didnt worked at all. i think that killing this vacuum process was not a good idea. 24 hours after killing this process this ugly xlog thing happend while executing CLUSTER. And the pg_dump right before CLUSTER did work fine. Besides 100 GB of xlog, another strange thing that i had about 42 GB in directory data/base. And it should be about 4 GB and i vacuum an cluster every night. Anyway, replaying that much log is gonna take awhile :-(. I think you have only two choices: 1. Grin and bear it. i tried for several hours. 2. Kill the replay process, then use pg_resetxlog to throw away the xlog. Then pray you didn't lose anything critical by doing so. i killed the process and used a database backup from just before the error occurred. If you know that there was nothing going on except the supposed index build, then you can be pretty sure that #2 will lose nothing except the incomplete index, so it might be a workable alternative. When it comes to trouble with postgresql i always have the feeling of not knowing enough stuff which is NOT inside the docs. I had another ugly situation a year ago and when in trouble it's very difficult to act calm. Isnt' there more information about Troubleshooting than reading postgresql code and archives? I am not an expert DBA (i wouldn't call me a DBA at all besides the fact that i am actually doing the administration). But i am willing to learn. kind regards, janning -- PLANWERK 6 websolutions Herzogstraße 85, 40215 Düsseldorf Tel.: 0211-6015919 Fax: 0211-6015917 http://www.planwerk6.de ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] pg_restore: [custom archiver] could not uncompress data: incorrect data check
Hi, i dumped my database on server1 with pg_dump -Fc ..., copied the dump to server2, both same pgsql version 7.4.6 pg_restore says pg_restore: [custom archiver] could not uncompress data: incorrect data check But it seems that almost any data was restored. What does this error mean. I didn't found anything in the archives (searched google with 'pg_restore incorrect data check'). Just one unanswered message ( http://archives.postgresql.org/pgsql-general/2003-08/msg01035.php ) kind regards, janning -- PLANWERK 6 websolutions Herzogstraße 85, 40215 Düsseldorf Tel.: 0211-6015919 Fax: 0211-6015917 http://www.planwerk6.de/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] normal user dump gives error because of plpgsql
Am Donnerstag, 10. März 2005 18:17 schrieb Tom Lane: John Sidney-Woollett [EMAIL PROTECTED] writes: I'm pretty sure I had the same problem when using pg_restore. If pl/pgsql is installed in template1, then the restore fails. And I couldn't find any solution to this on the list either. You're supposed to restore into a database cloned from template0, not template1. Thanks! I just didn't read the manual careful enough. Sorry for stealing your time as it is 10 times worth than mine, i guess. Can pg_restore be made to ignore the error? It does, at least since 8.0. I guess, it's time to upgrade! kind regards, Janning ---(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] pg_restore: [custom archiver] could not uncompress data: incorrect data check
Hi, i dumped my database on server1 with pg_dump -Fc ..., copied the dump to server2, both same pgsql version 7.4.6 pg_restore says pg_restore: [custom archiver] could not uncompress data: incorrect data check But it seems that almost any data was restored. What does this error mean. I didn't found anything in the archives (searched google with 'pg_restore incorrect data check'). Just one unanswered message ( http://archives.postgresql.org/pgsql-general/2003-08/msg01035.php ) kind regards, janning ---(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] normal user dump gives error because of plpgsql
Hi, i have a normal user with rights to create a db. template1 contains language plpgsql. the user wants to - dump his db - drop his db - create it again - and use the dump file to fill it. it gives errors because of CREATE LANGUAGE statements inside the dump. How can i prevent that the dump contains CREATE LANGUAGE statements. They are not needed if template1 contains the language, right? I didnt found anythng in the archives even though i am sure not to be the first one having this problem. kind regards, janning here is what i did with 7.4.6: +++ AS DB SUPERUSER # createlang plpgsql template1 # createuser -Ad testuser CREATE USER # su testuser +++ AS TESTUSER $ createdb $ pg_dump -O -x /tmp/dump.sql $ dropdb $ createdb $ psql testuser /tmp/dump.sql SET SET SET ERROR: permission denied for language c ERROR: must be superuser to create procedural language ERROR: must be owner of schema public kind regards, janning -- PLANWERK 6 websolutions Herzogstraße 85, 40215 Düsseldorf Tel.: 0211-6015919 Fax: 0211-6015917 http://www.planwerk6.de/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] normal user dump gives error because of plpgsql
Hi, i have a normal user with rights to create a db. template1 contains language plpgsql. the user wants to - dump his db - drop his db - create it again - and use the dump file to fill it. it gives errors because of CREATE LANGUAGE statements inside the dump. How can i prevent that the dump contains CREATE LANGUAGE statements. They are not needed if template1 contains the language, right? I didnt found anythng in the archives even though i am sure not to be the first one having this problem. kind regards, janning here is what i did with 7.4.6: +++ AS DB SUPERUSER # createlang plpgsql template1 # createuser -Ad testuser CREATE USER # su testuser +++ AS TESTUSER $ createdb $ pg_dump -O -x /tmp/dump.sql $ dropdb $ createdb $ psql testuser /tmp/dump.sql SET SET SET ERROR: permission denied for language c ERROR: must be superuser to create procedural language ERROR: must be owner of schema public kind regards, janning -- PLANWERK 6 websolutions Herzogstraße 85, 40215 Düsseldorf Tel.: 0211-6015919 Fax: 0211-6015917 http://www.planwerk6.de/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] SQL query
Am Freitag, 11. Februar 2005 12:07 schrieb David Goodenough: I have an address table, with all the normal fields and a customer name field and an address type. There is a constraint that means that the combination of customer and type have to be unique. Normally the only record per customer will be of type 'default', but if for instance the customer wants a different billing address I would add in a second type='billing' address record. I then want to join this table to another table, say an invoice table, and I want to use the billing address if present, otherwise the default address. I do not want to create either two addresses or to put both addresses on the invoice. I could do this by doing a select * from addresses where customer = ? and type = 'billing', looking to see if there is a result row and if not repeating the query with type = 'default', but that seems inelegant to me. I thought of using an inner select for the join, and using limit 1 to get just the one, and forcing the order by to give me the billing address by preference, but I am then dependant on the sort order of the particular type values I am selecting from. don't think vertical (adresses in rows), think horizontal (adresses in columns), like this: SELECT c.*, COALESCE(a1.street, a2.street) AS street, COALESCE(a1.zip, a2.zip) AS zip, COALESCE(a1.town, a2.town) AS town FROM customer AS c LEFT JOIN adresses AS a1 USING (customer_id) LEFT JOIN adresses AS a2 USING (customer_id) WHERE a1.type = default AND a2.type = 'billing' i just type the and did not tested it. the trick is to join adresses multiple times and get the right data with COALESCE function which returns the first value which is NOT NULL. If you still have difficulties, please send your schema. kind regards, janning ---(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] SELECT from multiple tables (not join though)
Am Montag, 10. Januar 2005 18:22 schrieb Madison Kelly: Hi all, I have another question, I hope it isn't too basic. ^.^ I want to do a select from multiple tables but not join them. What I am trying to do is something like this (though this doesn't work as I need): SELECT a.file_name, b.file_name, c.file_name FROM file_info_1 a, file_info_2 b, file_info_3 c WHERE a.file_name='/' AND b.file_parent_dir='/' AND c.file_parent_dir='/'; That returns every combination of the results from the three tables which is a huge number. What I need is to return all of the matches in all of the tables in a single column. Once I have all the matches in one column the next trick is to sort the combined results (any tips there?). you want something like this SELECT a.file_name FROM file_info_1 a WHERE a.file_name='/' UNION SELECT b.file_name FROM file_info_2 b WHERE b.file_name='/' UNION SELECT c.file_name FROM file_info_3 c WHERE c.file_name='/' ORDER BY 1; for further documentation visit http://www.postgresql.org/docs/7.4/interactive/sql-select.html or your local postgresql documentation. kind regards, janning ---(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] table with sort_key without gaps
Am Samstag, 11. Dezember 2004 20:06 schrieb Bruno Wolff III:
On Thu, Dec 09, 2004 at 18:32:19 +0100,
Janning Vygen [EMAIL PROTECTED] wrote:
id should be positive
id should not have gaps within the same account
id should start counting by 1 for each account
i cant use sequences because they are producing gaps and doesn't start
counting by 1 for each account and i dont want to use postgresql array
type for various reasons.
for this model to function you need a lot of sophisticated plpgsql
function to insert, move or delete entries to keep
I doubt you want to use this model if you are going to be deleting records.
Sometimes i am going to delete records. Then i would call a trigger ON DELETE
which moves all other entries to the right place.
- did anyone implemented a table like this and wrote some custom
functions/triggers for inserting, deleting, moving and so on? If yes it
would be nice if he/she is willing to sahre the code with me.
If you aren't deleting records and you don't have a lot of concurrent
requests, you can lock the table and select the current max id for an
account and add 1 to get the next id for for that account.
Updates and deletes are very seldom, but i still dont want to lock the table.
- did anyone implemented a table like this and came to the conclusion
that this shouldn't be done for any reasons out of my sight? (i don't
bother about updating a primary key)
Why are you doing this? Normally uniqness of an ID is good enough. If you
don't need to worry about gaps, you could use one sequence for the entire
table to generate IDs.
maybe your are right. But with Sequences i thought to have problems when i do
inserts in the middle of the sorting array. I need to move all current rows
out of the way to insert a new one. Insert a row at id 3 i need to do
UPDATE mytable SET id = -(id + 1) WHERE id = 3;
UPDATE mytable SET id = -(id) WHERE id 0;
INSERT INTO mytable VALUES (3);
-- UPDATE mytable SET id = id + 1 WHERE id = 3;
-- doesnt work in pgsql if id is a primary key
but with sequences i just have to push my sequence counter up, too. Right?
SELECT nextval('mytable_id_seq');
ok, it should work with sequences, too. I will try it. but isn't there a ready
to use model which explains and avoids problems like the one with the update
statement above?
kind regards
janning
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] table with sort_key without gaps
Am Montag, 13. Dezember 2004 17:37 schrieb Bruno Wolff III:
On Mon, Dec 13, 2004 at 10:58:25 +0100,
Janning Vygen [EMAIL PROTECTED] wrote:
Am Samstag, 11. Dezember 2004 20:06 schrieb Bruno Wolff III:
maybe your are right. But with Sequences i thought to have problems when
i do inserts in the middle of the sorting array. I need to move all
current rows out of the way to insert a new one. Insert a row at id 3 i
need to do
UPDATE mytable SET id = -(id + 1) WHERE id = 3;
UPDATE mytable SET id = -(id) WHERE id 0;
INSERT INTO mytable VALUES (3);
-- UPDATE mytable SET id = id + 1 WHERE id = 3;
-- doesnt work in pgsql if id is a primary key
but with sequences i just have to push my sequence counter up, too.
Right?
Sequences should really only be used to obtain unique values. It is
dangerous to assume any other semantics other than that within a session
the values returned by nextval TO THAT SESSION will monotonically increase.
SELECT nextval('mytable_id_seq');
ok, it should work with sequences, too. I will try it. but isn't there a
ready to use model which explains and avoids problems like the one with
the update statement above?
You still haven't told us why you want to remove the gaps in the id.
Unless you have some business reason for doing that, you shouldn't be
doing that. If you told us what the business reason for doing that is,
then we may be able to give you some better suggestions.
ok, i have users which wants to manage their sporting competitions which
(simplified) has games and fixtures (in german Spieltage, i hope the word
fixtures is understandable). Like German Bundesliga has 9 games on
Spieltag 1, 7 on saturday and two on sunday.
So i have a table:
CREATE TABLE spieltage (
account text NOT NULL,
sort int4 NOT NULL,
name text NOT NULL
PRIMARY KEY (account, sort),
UNIQUE (account, name)
)
and another table (which is not interesting here) with games having a foreign
key referencing spieltage(account, sort). Of course every spieltag has a
unique name but needs more important a sort column.
I need to have sort as a primary key or at least a unique key (which is nearly
the same) because many other tables should reference the (primary or
candidate) key (account, sort) for the main reason that i can easily sort
other tables according to the sort column without the need to make a join.
updating/inserting/deleting to the table spieltage takes happen very seldom,
but it should be possible.
When i have three rows and i want to insert one row between sort 1 and sort
2 i have to move all columns by one.
sample data when using one sequence for sort column
account | sort
--
acc1| 1
acc1| 2
acc2| 3
acc2| 4
acc1| 5
now i insert VALUES ('acc1', 2) i need to move all existing rows out of the
way.
ah, as i am writing i understand my problem:
i CAN say:
SELECT nextval('spieltage_sort_seq'); -- i might move a column to currval
UPDATE spieltage SET sort = -(sort + 1) WHERE account = 'acc1' and sort = 2;
UPDATE spieltage SET sort = -(sort) WHERE account = 'acc1' and sort 0;
INSERT INTO spieltage VALUES ('acc1', 3);
right?
because the duplicate sort column value '3' after moving isnt a problem
because of the two-column primary key which only enforces uniquness of
(account, sort)
the other reason why i wanted gapless sequences was that i would love to use
the id in an URL. But this is easy to manage to translate a positional id in
an URL to the database id.
ok. I think i am going to use sequences. But after all i am wondering to find
so little stuff for this common problem. Lots of people have tables which
have a sort column (example: top ten lists) but i guess normally the sort
column is NOT the primary key.
kind regards
janning
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[GENERAL] table with sort_key without gaps
Hi, i have a table like this: create table array ( account text NOT NULL, id int4 NOT NULL, value text NOT NULL, PRIMARY KEY (account, id) ); values like this: acc1,1,'hi' acc1,2,'ho' acc1,3,'ha' acc2,1,'ho' acc3,1,'he' acc3,2,'hu' id should be positive id should not have gaps within the same account id should start counting by 1 for each account i cant use sequences because they are producing gaps and doesn't start counting by 1 for each account and i dont want to use postgresql array type for various reasons. for this model to function you need a lot of sophisticated plpgsql function to insert, move or delete entries to keep - did anyone implemented a table like this and wrote some custom functions/triggers for inserting, deleting, moving and so on? If yes it would be nice if he/she is willing to sahre the code with me. - did anyone implemented a table like this and came to the conclusion that this shouldn't be done for any reasons out of my sight? (i don't bother about updating a primary key) kind regards, janning ---(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] How to clear linux file cache?
Hi, i am testing a few queries in my postgresql DB. The first query after reboot is always slower because of an empty OS page/file cache. I want to test my queries without any files in the linux kernel cache, just to know what would be the worst execution time. At the moment i stop postgresql and do something like find / -name foo for a few minutes and start postgresql afterwards. I search manuals with apropos page cache and found sync but it doesnt clear the cache, just writes dirty pages back to disk. I googled for linux clear page cache but did not found what i need. (of course i searched the archives, too) So how do i easily empty all page/file caches on linux (2.4.24)? Sorry for beeing a little bit OT, but i think my question still relates to postgresql. kind regards, janning ---(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] How to clear linux file cache?
Am Dienstag, 16. November 2004 16:39 schrieb Doug McNaught: Janning Vygen [EMAIL PROTECTED] writes: So how do i easily empty all page/file caches on linux (2.4.24)? Probably the closest you can easily get is to put the Postgres data files on their own partition, and unmount/remount that partition before running yuour tests. Unmounting will sync and throw away all cached file data for that partition. very good idea. i will do it like this. thanks. janning ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Avoiding sequential scans with OR join condition
Am Samstag, 16. Oktober 2004 07:23 schrieb Mike Mascari: Hello. I have a query like: SELECT big_table.* FROM little_table, big_table WHERE little_table.x = 10 AND little_table.y IN (big_table.y1, big_table.y2); I have indexes on both big_table.y1 and big_table.y2 and on little_table.x and little_table.y. The result is a sequential scan of big_table. In order to prevent this, Maybe the postgres planner decided to choose a seq scan because the planner thinks it is faster, and often it is right. Did you vacuum analyze before? try: VACCUM ANALYZE; SET enable_seq_scan to off; EXPLAIN ANALYZE your query SET enable_seq_scan to on; EXPLAIN ANALYZE your query you will see why postgres planner did choose a seq scan and if it was right to do so (but never disable seq scan on production environment, not even for one query. you do not want it.) (i hope syntax is correct otherwise consult the manual) I've rewritten the query as: SELECT big_table.* FROM little_table, big_table WHERE little_table.x = 10 AND little_table.y = big_table.y1 UNION SELECT big_table.* FROM little_table, big_table WHERE little_table.x = 10 AND little_table.y = big_table.y2 which does allow an index scan, but suffers from two separate queries along with a unique sort, which, from the data, represents 90% of the tuples returned by both queries. this is the reason it seems why postgres choose a seq scan in the first query. if it has to scan 90% of data anyway, it is faster than doing two index lookups before. Is there any way to write the first query such that indexes will be used? i do not know your db design but it looks queer to me to have a big_table with two columns y1 and y2 which seems to have the same meaning (some value which is compared to another value of little_table). why dont you put just one column y in your big_table? kind regards, janning Mike Mascari ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] more than one instance of pgpool for a backend?
Hi, pgpool seems to be very nice. I will use it in production environment as soon as possible, but have a question regarding pgpool: I have four different databases/user combinations which should have different numbers of possible connection. let my db have 80 concurrent connections and i want to divide them like this: [EMAIL PROTECTED] 10 [EMAIL PROTECTED] 40 [EMAIL PROTECTED] 5 [EMAIL PROTECTED] 25 At the moment i run four different instances af apache with PHP and connect via pg_pconnect. My MaxClients directives are set to the values above, so if all connections are busy you can't even connect to apache and maybe you get a timeout. That's not nice, but i keep my database from overloading and still have enough resources for different databse/user combination. (OT: I would love to here someone running successful PerChildMPM on apache2, at the moment i need four apaches on four different ports to configure MaxClient) How can i achive it with pgpool? Is it possible to run four pgpools for an backend. running pgpool on port 9000-9004 each configured to the values above and have one database cluster handle the pgpool connections? Can i still use synchronous replication and so on. My first guess is: it should work as pgpool handles all connections via independent preforked childs and it should not matter wheather a child is forked from one parent or another. But as i dont know alle the internals, i would like to here an expert opinion. kind regards, janning ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] i'm really desperate: invalid memory alloc request
Am Freitag, 1. Oktober 2004 10:56 schrieb Richard Huxton: Janning Vygen wrote: tonight my database got corruppted. before it worked fine. in the morning some sql queries failed. it seems only one table was affected. i stopped all web access and tried to backup the current database: pg_dump: ERROR: invalid memory alloc request size 0 pg_dump: SQL command to dump the contents of table fragentipps failed: PQendcopy() failed. pg_dump: Error message from server: ERROR: invalid memory alloc request size 0 pg_dump: The command was: COPY public.fragentipps (tr_kurzname, mg_name, fr_id, aw_antworttext) TO stdout; Does it do this consistently at the same place? Yes. It is in one table if i select a certain row. How can stuff like this can happen? i tried to recover from backup which was made just before clustering but i got ERROR: index row requires 77768 bytes, maximum size is 8191 There are a few steps - you've already done the first 1. Stop PG and take a full copy of the data/ directory 2. Check your installation - make sure you don't have multiple versions of pg_dump/libraries/etc installed 3. Try dumping individual tables (pg_dump -t table1 ...) 4. Reindex/repair files 5. Check hardware to make sure it doesn't happen again. Once you've dumped as many individual tables as you can, you can even try selecting data to a file avoiding certain rows if they are causing the problem. Ok, i can recreate most of the data. My main question is now: - Why does things like this can happen? - how often do they happen? There's more you can do after that, but let's see how that works out. PS - your next mail mentions sig11 which usually implies hardware problems, so don't forget to test the machine thoroughly once this is over. first i ran the long smart selftest: * === START OF READ SMART DATA SECTION === SMART Self-test log structure revision number 1 Num Test_DescriptionStatus Remaining LifeTime(hours) LBA_of_first_error # 1 Extended off-line Completed without error 00% 4097 - * AND * # smartctl -Hc /dev/hda smartctl version 5.1-18 Copyright (C) 2002-3 Bruce Allen Home page is http://smartmontools.sourceforge.net/ === START OF READ SMART DATA SECTION === SMART overall-health self-assessment test result: PASSED [...] * so SMART tells me that everything is fine. but in my messages * Oct 2 14:50:45 p15154389 smartd[11205]: Device: /dev/hda, SMART Prefailure Attribute: 1 Raw_Read_Error_Rate changed from 62 to 61 Oct 2 14:50:45 p15154389 smartd[11205]: Device: /dev/hda, SMART Usage Attribute: 195 Hardware_ECC_Recovered changed from 62 to 61 Oct 2 14:59:00 p15154389 /USR/SBIN/CRON[11428]: (root) CMD ( rm -f /var/spool/cron/lastrun/cron.hourly) Oct 2 15:19:55 p15154389 -- MARK -- Oct 2 15:20:46 p15154389 smartd[11205]: Device: /dev/hda, SMART Prefailure Attribute: 1 Raw_Read_Error_Rate changed from 61 to 63 Oct 2 15:20:46 p15154389 smartd[11205]: Device: /dev/hda, SMART Usage Attribute: 195 Hardware_ECC_Recovered changed from 61 to 63 Oct 2 15:31:22 p15154389 su: pam_unix2: session finished for user root, service su Oct 2 15:50:45 p15154389 smartd[11205]: Device: /dev/hda, SMART Prefailure Attribute: 1 Raw_Read_Error_Rate changed from 63 to 61 Oct 2 15:50:45 p15154389 smartd[11205]: Device: /dev/hda, SMART Usage Attribute: 195 Hardware_ECC_Recovered changed from 63 to 61 * don't know what it means. after that i run memtest via a serial console for hours and hours but no errors where found! Its a little bit strange. It would feel much nicer if harddisk oder memory were damaged. so what could be the reason for SIG11?? is it save to use this machine again after testing memory and hardware? kind regards janning ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] i'm really desperate: invalid memory alloc request size 0
Hi, tonight my database got corruppted. before it worked fine. since two days i do the following tasks every night psql -c 'CLUSTER;' $DBNAME psql -c 'VACUUM FULL ANALYZE;' $DBNAME before these opertaions i stop all web access. The last months i only did a VACUUM ANALYZE each night and didn't get any failures. in the morning some sql queries failed. it seems only one table was affected. i stopped all web access and tried to backup the current database: pg_dump: ERROR: invalid memory alloc request size 0 pg_dump: SQL command to dump the contents of table fragentipps failed: PQendcopy() failed. pg_dump: Error message from server: ERROR: invalid memory alloc request size 0 pg_dump: The command was: COPY public.fragentipps (tr_kurzname, mg_name, fr_id, aw_antworttext) TO stdout; so i did: /etc/init.d/postgresql stop cp -rp /home/postgres/data /home/postgres/datafailure and i tried to recover from backup which was made just before clustering but i got ERROR: index row requires 77768 bytes, maximum size is 8191 is there any chance to get my database keep going again? pg version is 7.4.2 kind regards janning ---(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] i'm really desperate: invalid memory alloc request size 0
Am Freitag, 1. Oktober 2004 09:49 schrieben Sie: Hi, tonight my database got corruppted. before it worked fine. since two days i do the following tasks every night psql -c 'CLUSTER;' $DBNAME psql -c 'VACUUM FULL ANALYZE;' $DBNAME before these opertaions i stop all web access. The last months i only did a VACUUM ANALYZE each night and didn't get any failures. in the morning some sql queries failed. it seems only one table was affected. i stopped all web access and tried to backup the current database: pg_dump: ERROR: invalid memory alloc request size 0 pg_dump: SQL command to dump the contents of table fragentipps failed: PQendcopy() failed. pg_dump: Error message from server: ERROR: invalid memory alloc request size 0 pg_dump: The command was: COPY public.fragentipps (tr_kurzname, mg_name, fr_id, aw_antworttext) TO stdout; so i did: /etc/init.d/postgresql stop cp -rp /home/postgres/data /home/postgres/datafailure and i tried to recover from backup which was made just before clustering but i got ERROR: index row requires 77768 bytes, maximum size is 8191 is there any chance to get my database keep going again? pg version is 7.4.2 kind regards janning some additional information from my log files: LOG: server process (PID 24227) was terminated by signal 11 LOG: terminating any other active server processes WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another ser ver process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. [...] LOG: all server processes terminated; reinitializing LOG: database system was interrupted at 2004-10-01 04:59:46 CEST LOG: checkpoint record is at 6A/A9142BB4 LOG: redo record is at 6A/A9016D68; undo record is at 0/0; shutdown FALSE LOG: next transaction ID: 202721693; next OID: 352799 LOG: database system was not properly shut down; automatic recovery in progress LOG: redo starts at 6A/A9016D68 LOG: record with zero length at 6A/A931CEE0 LOG: redo done at 6A/A931CEBC LOG: recycled transaction log file 006A00A6 LOG: recycled transaction log file 006A00A7 LOG: removing transaction log file 006A00A8 LOG: database system is ready LOG: server process (PID 24235) was terminated by signal 11 LOG: terminating any other active server processes WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another ser ver process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another ser ver process exited abnormally and possibly corrupted shared memory. please help me... janning ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] i'm really desperate: invalid memory alloc request size 0
Hi Richard, i feared all db gurus are asleep at the moment. Am Freitag, 1. Oktober 2004 10:56 schrieb Richard Huxton: PS - your next mail mentions sig11 which usually implies hardware problems, so don't forget to test the machine thoroughly once this is over. You saved my life!! Nothing less! This was a great help cause i never thought that it could be a hardware problem. I took a dump from last night and tried to recover on the original machine. it didnt work as i wrote. but when i tried to install it on another machine it just worked fine. So everything is up and running. I still have all the corrupt files in place and now i try to determine what went wrong. As it is obviously a hardware problem, my question is now: how can i check my hardware (disk)? How can i get informed next time when things are going wrong? Ok i will come up with a lot of questions as soon as i had another coffee because i never want to feel so helpless again. There really should be a section in the manual like desaster recovery which shows some tricks and methods. pg version is 7.4.2 Download 7.4.5 - that's got the latest bugfixes in it. i will as soon as my nerves are cooling down again :-) kind regard, janning -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] ALTER TABLE - add several columns
Am Donnerstag, 26. August 2004 08:30 schrieb Fuchs Clemens: Hi, I just want to add several columns to an existing table. Do I have to call a statements like below for each new column, or is there a possibility to do it at once? - existing table: test - columns to add: col1 (FK), col2 ALTER TABLE test ADD col1 INT2 NULL; ALTER TABLE test ADD CONSTRAINT col1_fk FOREIGN KEY (col1) REFERENCES xy(col1); ALTER TABLE test ADD col2 INT2 NULL; I 'd rather like to execute a statement like this: ALTER TABLE test ADD ( col1 INT2 NULL, col2 INT2 NULL, FOREIGN KEY (col1) REFERENCES xy(col1) ); Take a look at http://www.postgresql.org/docs/7.4/static/sql-altertable.html You can only add one column per statement, but of course you can place your statements into a transaction. kind regards janning ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Constraints to Guarantee unique across tables with foreign key?
Am Donnerstag, 26. August 2004 04:43 schrieb Benjamin Smith: I have two tables like following: create table attendancereport ( id serial unique not null, staff_id integer not null references staff(id), schoolyear varchar not null references schoolyear(year), students_id integer not null references students(id) ); // schoolyear.year in format 2003 - 2004. Create table attendancerecords ( attendancereport_id integer not null references attendancereport(id), schoolday integer not null references schooldays(day), attended bool not null ); // schoolday.day in formation MMDD as in 200301222 for dec 22, 2003. What I'm looking for is a way to create a unique( ) across tables via the foriegn key, something like Alter table attendancerecords ADD unique (schoolday, attendancereport.students_id); You need mutliple column foreign keys like this (didnt test it just typed and its early in the morning, havn't got any coffee yet): CREATE TABLE attendancereport ( students_id integer NOT NULL REFERENCES students(id), schoolyear varchar NOT NULL REFERENCES schoolyear(year), staff_idinteger NOT NULL REFERENCES staff(id), CONSTRAINT pk_arep PRIMARY KEY (students_id, schoolyear) ); CREATE TABLE attendancerecords ( students_id integer NOT NULL, schoolyear varchar NOT NULL, schoolday integer NOT NULL REFERENCES schooldays(day), attendedboolean NOT NULL, CONSTRAINT pk_arec PRIMARY KEY (students_id, schoolyear, schoolday), CONSTRAINT fk_students_id FOREIGN KEY (students_id, schoolyear) REFERENCES attendancereport(students_id, schoolyear) ); this way you can have only ONE unique record for each student on each day of any schoolyear. The Uniqueness is guranteed by the Primary key (which is in theory nothing else like a uniquey key which is NOT NULL) I dropped the serial columns because i dont know what those surrogate keys are for, but you can add them again, if you want to select records by number within your application. [Maybe you could even place the staff_id field into your students table and drop the table attendancereport.] kind regards, janning ---(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] How to use as Functional Index to be used as Primary KEY
Am Samstag, 31. Juli 2004 17:13 schrieb Tom Lane: Janning Vygen [EMAIL PROTECTED] writes: So here is my question: How can i define a functional index to be used with a primary key (using postgreSQL 7.4.3)? You can't. The SQL spec says that primary keys are columns or lists of columns. I don't see any particular difference between a primary key and a unique index, anyway, except that the primary key is the default target for foreign-key references. Thanks to Tom and Peter for your answers. I will design my table without a primary key and use my unique index instead of a primary key. As this unique key is the same as a primary key i dont see the reason why postgresql should't extend the specs and allow functional primary key indizes. kind regards, janning ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] EXPLAIN on DELETE statements
Hi, EXPLAIN on delete stamements works, but doesn't show me all the subsequent deletes or checks which has to be done because of foreign keys cascading/restricting. Is there a solution to show up which tables are checked and which scans the planner is going to use to check these related tables? kind regards, janning ---(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] About table schema
Am Mittwoch, 26. Mai 2004 00:36 schrieb Wei Shi: Hi, does anyone know how to get the schema information of a table. More specifically, I would like to know 1. which field(s) are primary keys? 2. the data type of each field of a table? 3. If a field is a foreign key, what field/table it is referring to. connect to your database with 'psql' and type \d tablename which gives you all information you asked for. Type \? for a list of other commands within psql and take a look at 'man psql' kind regards, janning ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] convert result to uppercase
Am Dienstag, 13. April 2004 14:17 schrieb Victor Spng Arthursson:
Hi!
How do i convert a result to upper/lowercase?
This is a question
SELECT UPPER(lang) from languages;
and this is the answer. It works exactly like this:
SELECT UPPER('dk');
results in 'DK'
kind regards,
Janning
---(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
