[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 ther

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:

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, 'Joz

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 > >>

[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

[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

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 : > > hi, > > > > http://archives.postgresql.org/pgsql-general/2009-03/msg00581.php > > > > This was my suggestion about introducing a statment to g

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 project

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 > system

Re: [GENERAL] suggestion: log_statement = sample

On Tuesday 21 July 2009 15:49:36 Tom Lane wrote: > Janning Vygen 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 ca

Re: [GENERAL] suggestion: log_statement = sample

On Tuesday 21 July 2009 18:09:57 you wrote: > Janning Vygen 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 loggi

[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 : 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

[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 Upd

[GENERAL] Anonymized database dumps

ly 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 Vyge

Re: [GENERAL] Anonymized database dumps

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 sen

Re: [GENERAL] Anonymized database dumps

Am 19.03.2012 um 13:22 schrieb Bill Moran : > In response to Janning Vygen : >> >> 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

[GENERAL] PANIC: corrupted item pointer

ing). 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 Handelsregis

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 > &g

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

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 -

[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 ge

[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; d

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 wrote: > > Hi, > > > > Why does default_statistic_target defaults to 10? > > > > I suggest to setting it to 100 by default: > > Already done in 8.4 GREA

[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 0

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 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 CE

Re: [GENERAL] Problem with invalid byte sequence and log_min_error_statement

On Wednesday 15 April 2009 19:21:03 you wrote: > Janning Vygen 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

[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 con

[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 c

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 e

[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

[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

[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 res

Re: [GENERAL] pg_xlog disk full error, i need help

aestrutura e Banco de Dados > Planae Tecnologia da Informação > (+55) 14 2106-3514 > http://www.planae.com.br > ----- Original Message - > From: "Janning Vygen" <[EMAIL PROTECTED]> > To: > Sent: Monday, March 28, 2005 7:19 AM > Subject: [GENERAL] pg_xlog d

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 sa

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 twic

[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

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:

[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\34

[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

[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

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 designe

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 n

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-geometr

[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

[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-Anweis

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-effe

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

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

[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 func

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 m

[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

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

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_constrai

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) > >

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

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

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

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_da

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 th

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

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 t

[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 co

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

Re: [GENERAL] ERROR: type "temp_gc" already exists

> Janning Vygen 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 det

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 li

[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

Re: [GENERAL] ERROR: type "temp_gc" already exists

le 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

Re: [GENERAL] ERROR: type "temp_gc" already exists

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 som

[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 particu

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" >

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

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

[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

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 partit

[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 g

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 > > "

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 Sequence

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.

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 cust

[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 al

[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 th

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

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.

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 tabl

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 update

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 som

[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 possi

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? > > w

Re: [GENERAL] Excel, OpenOffice and Postgres

Am Donnerstag, 18. Dezember 2003 00:15 schrieb Matthew: > Try this link to OpenOffice and its postgres-sdbc-driver. > > http://dba.openoffice.org/drivers/postgresql/ > > I have Installed OO on RedHat and tested that I can connect > postgres - which it does without any dramas, but I havent done much

Re: [GENERAL] Accented chars in several apps

Am Freitag, 9. April 2004 22:32 schrieb G. Brannon Smith: > Answering my own post. I went ahead and bit the bullent and dumped the > whole thing over to ENCODING = 'UNICODE'. Wasn't nearly as painful as I > thought it would be... although I guess the DB has now effectively > doubled in size. Isn't

Re: [GENERAL] convert result to uppercase

Am Dienstag, 13. April 2004 14:17 schrieb Victor Spång 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 -

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

[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

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 prim

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:

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 nu

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_tab

[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'

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. Th

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 les

  1   2   >