[GENERAL] From: Bricklen Anderson
Hi http://forum.myways.su/felt.php?drive=bhankyuytv3630es brick...@gmail.com
Re: [GENERAL] Count
Bob Pawley wrote: I have a table with four columns that will either be null or hold the value 'true'. I want to obtain the count of these columns, within a particular row, that have 'true' as a value (0 to 4). I have attempted the Select count method but it seems that I need something more. If anyone has any thoughts it would be much appreciated. Bob Something like this? create table t (id int, w bool, x bool, y bool, z bool); insert into t values (1,null,null,'t','t'), (1,null,'t','t',null), (2,'t',null,'t',null), (2,'t',null,'t',null), (3,null,'t','t','t'), (4,'t','t','t','t'); select id, sum(case when w is null then 0 else 1 end) as w, sum(case when x is null then 0 else 1 end) as x, sum(case when y is null then 0 else 1 end) as y, sum(case when z is null then 0 else 1 end) as z from t group by id order by id; id | w | x | y | z +---+---+---+--- 1 | 0 | 1 | 2 | 1 2 | 2 | 0 | 2 | 0 3 | 0 | 1 | 1 | 1 4 | 1 | 1 | 1 | 1 ? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] 7.4.6 to 8.2.5 - ' changes to $_$
Steve Clark wrote: function from 7.4.x postgres CREATE FUNCTION update_dns(text, text) RETURNS integer AS 'UPDATE domain_details SET domain = $2 WHERE domain = $1; DELETE from domains where domain = $1; SELECT 1 AS ignore;' LANGUAGE sql; I load it into 8.2.5 - then dump it out and it is changed to CREATE FUNCTION update_dns(text, text) RETURNS integer AS $_$UPDATE domain_details SET domain = $2 WHERE domain = $1; DELETE from domains where domain = $1; SELECT 1 AS ignore;$_$ LANGUAGE sql; notice $_$ where the single ' use to be. Is there some way to keep this from happening? The reason is we have systems in the field that have configuration information stored in 7.4.x. We want to upload that db info load it into an 8.2.5 db massage it then send it back to the unit in the field. I realize there are things I am going to have to fix up in the 8.2.5 dump to be able to load it back into the 7.4.x db but I want to minimize that as much as possible. We have some units in the field running 8.1.3 and it does not change the ' to $_$. Thanks, Steve I think --disable-dollar-quoting will work. (pg_dump --help) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] 8.2.4 serious slowdown
I don't an answer to your question, but an obvious difference is that the slow query contains many more loops. (this may already have been noted, I didn't see it posted however). (showing just the loops with more than one loop) - Index Scan using assemblies_pkey on assemblies a (cost=0.00..0.31 rows=1 width=38) (actual time=0.007..0.009 rows=1 loops=3685) - Function Scan on stockperowner_lead_ab c (cost=0.00..15.00 rows=5 width=20) (actual time=0.012..3.162 rows=1694 loops=3685) - Index Scan using idx_u_assidpartid on partsassembly b (cost=0.00..0.27 rows=1 width=16) (actual time=0.010..0.012 rows=1 loops=3685) - Hash Join (cost=97.69..531.29 rows=1250 width=21) (actual time=2.395..78.855 rows=3851 loops=3684) - Seq Scan on allocatedassemblies b (cost=0.00..349.62 rows=19062 width=12) (actual time=0.009..35.493 rows=19062 loops=3684) - Hash Join (cost=621.63..1206.10 rows=3854 width=28) (actual time=0.074..71.265 rows=593 loops=3684) - Hash Join (cost=337.11..839.61 rows=3860 width=32) (actual time=0.057..68.467 rows=593 loops=3684) - Seq Scan on poparts e (cost=0.00..379.60 rows=16860 width=32) (actual time=0.008..34.510 rows=16860 loops=3684) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Getting process id of a connection?
Webb Sprague wrote: Hi all, Is there a way to determine the pid of a database connection from within that connection? As a hypothetical example, I would like to be able to do the following: $ps x PID TTY STAT TIME COMMAND 11674 ?S 0:00 sshd: [EMAIL PROTECTED]/1 11675 pts/1Ss 0:00 -bash 11682 pts/1T 0:00 psql 11685 pts/1R+ 0:00 ps x psql=# select CURRENT_PID; 11682 I want this so that I can log the psycopg2 connection pid, and kill it to test reconnection code. Thanks! -W I think select pg_backend_pid(); will do that. ---(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] Anyone know a good opensource CRM that actually installs with Posgtres?
Bradley Kieser wrote: I hope that someone has cracked this one because I have run into a brick wall the entire week and after 3 all-nighters with bad installations, I would appreciate hearing from others! I am looking for a decent OpenSource CRM system that will run with Postgres. SugarCRM seems to be the most popular but it's MySQL-centric and its opensource parts are very restricted. snip So if anyone has actually cracked this, please let me know! I really need a good CRM. It has to be OpenSource, not just out of principle, but we need to integrate it into an existing business with established inhouse software so we need to be able to customise the code. Thanks, Brad A coworker of mine (Ryley Breiddal) did some coding and testing for the SugarCRM PostgreSQL port, in conjunction with Jason Felice. We have been running it for a few months with no major problems. http://eraserhead.net/sugarsuite-pgpatch/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Function in psql to Compare two numbers and return the bigger value
Emi Lu wrote: HEllo, I am looking for a psql method to get the bigger value of two numbers. For example, methodName(12.6, 3.8) select greatest(12.6,3.8); ---(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] Newbie Constraint ?
Jeanna Geier wrote: Hello List! OK, so I'm new to SQL and Postgres and am working on taking over this DB work, and ran across a command that I'm not sure of and am wondering if you can help me with... Probably a basic SQL question, but thought more than one person on here would be able to point me in the right direction!! In on of the tables in the DB, there is a constraint defined that I'm not familiar with: CONSTRAINT Relationship182 FOREIGN KEY (elementid) REFERENCES element (elementid) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE Not quite sure what the: CONSTRAINT Relationship182 is exactly... can anyone help me with this one? Haven't seen this one yet... That's the constraint name. More detailed here: http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] plperl exception catching
Marc Evans wrote:
OK, I must be missing something obvious:
c3i= CREATE OR REPLACE FUNCTION foo_trigger() RETURNS TRIGGER AS $$
c3i$ eval { spi_exec_query('INSERT INTO FOO_BAR VALUES(1)'); } ||
c3i$ spi_exec_query('SELECT 1 as foo');
c3i$ $$ LANGUAGE plperl;
ERROR: creation of Perl function failed: 'eval string' trapped by
operation mask at line 2.
Try as plperlu
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Vote for your favorite database
Scott Marlowe wrote: On Thu, 2006-12-07 at 11:59, Tony Caduto wrote: http://linux.inet.hr/poll_favorite_database.html So far Firebird is in the lead :-( Somebody just told their list earlier than anyone told us... or mysql's list. http://archives.postgresql.org/pgsql-general/2006-11/msg00072.php ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] porting time calcs to PG
[EMAIL PROTECTED] wrote: I'm trying to port an MS statement that's a bit involved with timestamps, and I don't see anything in the docs to lead me forward. It's basically a select statement, looking for records with a timestamp within a certain range, where that range is calculated with one of the fields. The WHERE clause that I use in SQL Server is: getdate() + ((2100 + 5 + (9*Points)) / 86400) = DueTime Where the numbers are actually parameters passed in to the function. Other than changine getdate() to now(), I'm not sure how to change the + interval to be effective. All the docs I see use something like interval '1 hour' - not sure how to put a calculated value in the quotes. Is this possible? For the interval part, you can try interval '1 minute' * some number ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Dollaw sign quoting disabled
snappingturtle wrote: It appears that in my installation of Postgres that dollaw sign quoting is disabled. For example, the following command returns an error: snip I didn't do anything (that I know of) to disable dollar quoting. Any advice on how to enable dollar sign quoting? Are you on postgresql version 8+? ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] pg_dump
Bob Pawley wrote: This is the example found in the documentation to dump a database. Examples To dump a database: $ pg_dump mydb db.out The following - $ pg_dump aurel aurel.out - gives me ERROR: syntax error at or near $ at character 1 What am I missing??? Bob The $ is part of the OS prompt. Windows would look something like c:\pg_dump mydb db.out. Omit the $ and it should be fine. ---(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] Pushing the Limits
Cabbar Duzayak wrote: Hi, We have huge amount of data, and we are planning to use logical partitioning to divide it over multiple machines instances. We are planning to use Intel based machines and there is not much updates but mostly selects. The main table that constitutes this much of data has about 5 columns, and rows are about 50 bytes in size, and 3 columns in this table need to be indexed. So, what I wanted to learn is how much can we push it to the limits on a single machine with about 2 gig rams? Do you think PostGres can handle ~ 700-800 gigabyte on a single machine? And, is it OK to put this much data in a single table, or should we divide it over multiple tables? If that is the case, what would be the limit for a single table? Any help/input on this is greatly appreciated. Thanks. Yeah, pg can handle that much data. As an example, we have some databases in that size range on commodity hardware with comparable ram etc. However, all of the large tables are partitioned into manageable sizes (daily tables, in this case). ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Stripping empty space from all fields in a table?
J B wrote:
Guys,
I have a table that has various fields that have whitespace in the
values. I'd like to roll through and strip the left and right whitespace
out of all fields that contain strings.
Is there any easy way to do this?
Thanks!
JB
trim will strip the whitespace from both sides. ltrim and rtrim are
front/back specific.
select '-'||trim(' asdf ')||'-';
?column?
--
-asdf-
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Ubuntu Help
kbajwa wrote: Hello: I am going to install Ubuntu OS under their LAMP installation. This LAMP installation installs mySQL. Is there any way I can install postgreSQL instead of mySQL? Has anybody created a LAMP to install Ubuntu, Apache, postgreSQL PHP? Kirt I don't know about a preconfigured LAMP installation, but it's pretty easy to do using aptitude/synaptic/adept/package manager here. Look for the postgresql-xxx packages etc. Same goes for the other aspects of that stack. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] function for current date-time
Harpreet Dhaliwal wrote: Hi, I have a timestamp field in my table and want to set a default value of current date/time for it. What should i enter as its default value? is there any function like now() in postgres? Thanks, ~Harpreet http://www.postgresql.org/docs/8.1/interactive/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] trouble with setof record return
brian wrote: Can anybody spot the problem with this function? Or, how i'm calling it? (it's not clear to me which it is) CREATE TABLE member ( ... first_name character varying(64), last_name character varying(64), organisation character varying(128), email character varying(128), ... ); CREATE OR REPLACE FUNCTION fetcOnlineContacts() RETURNS SETOF record AS $$ DECLARE member_contact record; BEGIN FOR member_contact IN EXECUTE 'SELECT DISTINCT ON (m.email) m.first_name || '' '' || m.last_name AS name, m.organisation, m.email AS address FROM member AS m WHERE m.email IS NOT NULL ORDER BY m.email, m.last_name, m.organisation ASC' LOOP RETURN NEXT member_contact; END LOOP; RETURN; END; $$ LANGUAGE plpgsql IMMUTABLE; test=# SELECT * FROM fetchOnlineContacts() AS (name text, organisation text, address text); ERROR: wrong record type supplied in RETURN NEXT CONTEXT: PL/pgSQL function fetchonlinecontacts line 15 at return next test=# SELECT * FROM fetchOnlineContacts() AS (name varchar(129), organisation varchar(128), address varchar(128)); ERROR: wrong record type supplied in RETURN NEXT CONTEXT: PL/pgSQL function fetchonlinecontacts line 15 at return next Normally, i declare a type, but this will be a single-use one so a record seemed to be sufficient. b Try casting your query cols as TEXT, eg. (m.first_name || '' '' || m.last_name)::TEXT AS name,(m.organisation)::TEXT, (m.email)::TEXT AS address ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Selecting from two unrelated tables
CSN wrote: I have two tables: items: id, title, added, ... news: id, headline, datetime, ... I'd like to select the latest 25 combined records from both tables. Is there a way to do this using just select? Thanks, csn Maybe something like this? select id,title_headline,dt from ( select id,title as title_headline,added as dt from ... union all select id,headline as title_headline,datetime as dt from ...) as a order by dt limit 25; ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] [pgsql-advocacy] Thought provoking piece on NetBSD
Anton de Wet wrote: One problem I see the postresql at the moment (and I'm porbably touching a can of worms here) is the lack of some sort of certification. One thing linux (or Red Hat) is doing well is supplying the things that corporates are looking for. And the first thing they look for when they seriously start looking at a new technology is training. When they look at training, they go for certifications (as we see all the time with the RHCE). We have a number of large corporate clients here in South Africa, including some of the biggest banks, of which a few are asking for training at the moment. It would be really nice to have some form of certification available that we could present that had some international credentials. Anton Training I agree with, but certifications can go either way. A good example of where certifications are generally NOT going to work in your favour is the fiasco that Oracle has created with their OCP certification over the past 6 or so years. So many people were pushed through these OCP mills that their certifications have become worthless. HR types were finding that these Oracle-certified dba/developers are of dubious quality at best -- even though they have a piece of paper stating that they are officially trained. I know that when we look at prospective employees, that designation is totally ignored. It is their experience and ability to do the job properly that count more than anything. my two bits. ---(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] Computing transitive closure of a table
There was a thread last November entitled Transitive closure of a directed graph on the [HACKERS] list. There may be some information of use there. ---(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] ERROR: for SELECT DISTINCT, ORDER BY expressions must
sergey kapustin wrote: Hi all! can anybody say me what's wrong with this query. I just try to take unique values from table column and print them in random order select distinct num from (select 1 as num union select 2 as num union select 1 as num union select 3) t order by random(); ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list thank you select num from (select distinct num from (select 1 as num union select 2 as num union select 1 as num union select 3 as num) a) t order by random(); ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: RES: [GENERAL] Add column and specify the column position in
Emi Lu wrote: No. It is not for select. I have tens of tables with very clean structure. For example, username, application_code, last_modified_by, etc in specific orders. Since the business model is changed, I have to add some columns to serveral tables. I prefer columns orders following my other tables. In any case, there's extensive discussion about this in the -hackers archives. IIRC, there is consensus that this would be nice to have but no one has cared enough to actually make it happen. There are some non-trivial issues since this would mean either completely re-writing the table when you do an ALTER or you'd have to be able to divorce the catalog representation of a table with the on-disk representation. Though there are other advantages to doing the later, it's non-trivial. If it does not support, I will recreate my tables. Thanks. Have you considered creating a view over the tables in question? You can order the attributes in any fashion you like that way. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] frustrated by plpgsql procedure
Dino Vliet wrote: Hi guys, I trying for days to get this simple plpgsql procedure to run but I keep getting this error: psql:pgsql_procedure.txt:15: ERROR: syntax error at or near at character 17 QUERY: copy cancel TO $1 with delimiter as ',' null as '.' CONTEXT: SQL statement in PL/PgSQL function doedit near line 12 psql:pgsql_procedure.txt:15: LINE 1: copy cancel TO $1 with delimiter as ',' null as '.' psql:pgsql_procedure.txt:15: The source code of this plpgsql procedure is: create or replace function doedit() returns void AS $$ /* Procedure to create textfile from database table. */ DECLARE i integer := 340; start date :='2004-08-06'; eind date :='2004-08-12'; location varchar(30) :='/usr/Data/plpgtrainin'; BEGIN create table cancel as (SOME QUERY); location := location || i || '.txt' ::varchar(30); raise notice 'location is here %', location; copy cancel TO location with delimiter as ',' null as '.' ; END $$ Language plpgsql; Missing semi-colon after END? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] frustrated by plpgsql procedure
Dino Vliet wrote: Nope:-( I added it just now and still the same error message!! Have you tried it with your copy command executed dynamically? eg. execute 'copy cancel to location ...'; ---(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] Syntax error, but where?
Michael Trausch wrote: Hey guys, I'm having a slight problem with this database that I'm trying to setup on PostgreSQL 8.1.3... What I've got is a stored procedure that refuses to get itself into the system, and I'm not sure why. It is throwing a syntax error on DECLARE, but I don't see it. I looked at the documentation, and as far as I can tell, my CREATE FUNCTION line looks just as it should in structure, as does the CREATE TYPE line that is immediately before it. Any ideas? The code that is failing is: CREATE TYPE app_global.city_list AS (zip_code CHAR(5), city_name VARCHAR(40), state_abbr CHAR(2), distance_miles NUMERIC(6,3)); -- -- Stored Procedures in app_global -- CREATE FUNCTION app_global.get_zip_codes_range(zip_code CHAR(5), range_miles NUMERIC(4,1)) RETURNS SETOF city_list AS $$FUNC_BODY$$ Is this actually part of the function: $$FUNC_BODY$$ ? If so, try it as $FUNC_BODY$ (single dollar signs around identifier). ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] User tables
Hrishikesh Deshmukh wrote: Hello All, Suppose there 3 users red, green, blue. How can the user green know what tables he has created?! From psql command line \dt lists every table in the DB!!! Thanks in advance. Hrishi If you mean that the owner of the table(s) is the user green, then try select * from pg_tables where tableowner='green'; ---(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] Dumping functions
Steve Crawford wrote: How can I dump a function definition with pg_dump? Background: We often need to create objects that are all relevant to only a specific project. Sometimes it is a single table. Other times there are many tables, indexes, views, rules, triggers and functions. All the objects share a unique substring that identifies the project so automatically creating the list is easy. When I use pg_dump to dump a table I will by default also get the associated indexes, rules and triggers. Views can be dumped just like tables. So all I need to do to archive the whole mess is to automate the dump of the functions. Ideas? Cheers, Steve This thread has a few options available http://archives.postgresql.org/pgsql-general/2005-10/msg01589.php ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Can't get the field = ANY(array) clause to work...
[EMAIL PROTECTED] wrote: The problem was fixed by initializing the array before giving it a value. Not surprising Postges isnt as popular as it should be. I was by luck that I found this out - the manual says nothing about init arrays. Instead of flippant comments like that, submit docs a patch if you feel it's necessary. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Stack Depth
Bob Pawley wrote: Hi Folks I have three triggers and associated functions that fire on one insert and moves row ID information to five different tables.. I am getting an error message stack depth limit exceeded. Is this normal for, what I consider, a small amount of information transfer? If so, how do I change the stack depth limit? I can't find anything in the documentation that describes this task. I understand the default stack depth is about 2 meg. How do I determine what the stack depth should be so that I avoid this error? Bob Pawley check max_stack_depth in your postgresql.conf file ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Shared Database across multiple servers using OCFS2
Kleynhans, Hendrik wrote: snip ___ “The information contained in this e-mail is confidential and may contain proprietary information. It is meant solely for the intended recipient. Access to this e-mail by anyone else is unauthorised. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted in reliance on this, is prohibited and may be unlawful .No liability or responsibility is accepted if information or data is, for whatever reason corrupted or does not reach its intended recipient. No warranty is given that this e-mail is free of viruses. The views expressed in this e-mail are, unless otherwise stated, those of the author and not those of FirstRand Bank Limited or its management. FirstRand Bank Limited reserves the right to monitor, intercept and block e-mails addressed to its users or take any other action in accordance with its e-mail use policy. Licensed divisions of FirstRand Bank Limited are authorised financial service providers in terms of the Financial Advisory and Intermediary Services Act 37 of 2002.” ___ That has got to be one of the longest disclaimers I've ever seen in an email... ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Page-Level Encryption
Jim C. Nasby wrote: I would highly recommend taking a look at how Oracle is handling encryption in the database in 10.2 (or whatever they're calling it). They've done a good job of thinking out how to handle things like managing the keys. I know that Oracle magazine did an article on it recently; you should be able to find that online somewhere. This link? http://www.oracle.com/technology/oramag/oracle/05-sep/o55security.html ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Data loading from a flat file...
Pandurangan R S wrote: To get rid of ^M characters you could use cat file | tr -d ^M you need to type ^V before you type ^M in the preceeding command. But ^V will not be displayed on the screen. Or you can use dos2unix/unix2dos, if installed. I believe they are in the sysutils package. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Simple Accumulating Number Loop?
Ubence Quevedo wrote: A friend of mine has created this simple accumulating loop query for MS SQL 2k5 Express Edition. I am trying to reproduce the same results with PostgreSQL 8.1, but am not able to find much useful help on how to properly set up a variable of both int and char. The PostgreSQL documentation is great if you have an idea of what you are doing, but I'm still really new to PostgreSQL. If someone can just point out some hints or clarifications as to wether to use the SET command or the psql \set command. Below is the query in question. Many thanx to any that can help. -Ubence declare @variableint int declare @desc char (50) set @variableint = 0 create table counter (countid int , description varchar(50)) while @variableint 500 begin set @variableint = @variableint + 1 set @desc = 'The Counter is Now' +' '+ cast (@variableint as char(50)) insert into counter values (@variableint,@desc) end select * from counter look for FOR or WHILE loops http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Question
Peter Futaro wrote: Dear PSQL, I need to make a documentation for my database. The documentation I want is almost exactly like the result of \d command. I want to make the report using a database manager application, and it requires me to make my own report by typing the SQL command in it. Can you please provide me the command behind \d or perhaps tell me what attributes do you use to build that \d report ? Thank you, Peter Futaro Start psql with -E to see the internal commands. psql --help for more details -- ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. ___ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PL/pgSQL: how to round a number up?
Script Head wrote: In PL/pgSQL the round() function seem to round a number down all the time. Is there something like ceil() that would round it up? ScriptHead Yup, it's called ceil(). Oh! You just said that ;) Can also use ceiling() -- ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. ___ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Is it databases in general, SQL or Postgresql?
Bob Pawley wrote: Hope someone can help me learn. I highly suggest getting an entry level book on SQL and reading that, then going through the PostgreSQL documentation. This will better equip you to solve these problems, and no doubt get you much further ahead in a shorter period of time. -- ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. ___ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] how to emit line number in a function?
Jerry Sievers wrote:
Bricklen Anderson [EMAIL PROTECTED] writes:
I couldn't find any useful references in the docs or archives for emitting the
line number of a plpgsql function (in a RAISE statement). I'd like to use it
for
debugging some complex functions.
Does anyone have any tips on where to look, or an example of this?
Have a look at the m4 macro processor
changequote({,})dnl
define({func_body},{$$begin
raise exception 'I barfed on line #__line__';
end$$})dnl
create function some_func()
returns whatever
as func_body
language plpgsql;
This can be useful sometimes... but may ADD to your debugging
headaches if not used artfully!
HTH
I'll look into that, thanks for the suggestion.
Cheers,
Bricklen
--
___
This e-mail may be privileged and/or confidential, and the sender does
not waive any related rights and obligations. Any distribution, use or
copying of this e-mail or the information it contains by other than an
intended recipient is unauthorized. If you received this e-mail in
error, please advise me (by return e-mail or otherwise) immediately.
___
---(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] how to emit line number in a function?
I couldn't find any useful references in the docs or archives for emitting the line number of a plpgsql function (in a RAISE statement). I'd like to use it for debugging some complex functions. Does anyone have any tips on where to look, or an example of this? Cheers, Bricklen -- ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. ___ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] how to emit line number in a function?
Richard Huxton wrote: Bricklen Anderson wrote: I couldn't find any useful references in the docs or archives for emitting the line number of a plpgsql function (in a RAISE statement). I'd like to use it for debugging some complex functions. Does anyone have any tips on where to look, or an example of this? I don't think you can do so yourself. However, recent versions of PG should display the line number of a RAISE NOTICE or similar if you have the error verbosity high enough. Okay, thanks for the head's up. Is there any other way to get the current line number, for example, an inline function call to elog(?) or something? It's not a huge deal, but it would be useful for debugging and timings. -- ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. ___ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] how to emit line number in a function?
Jim C. Nasby wrote: This seems to be something useful to have... can we get a TODO? Unless maybe Bricklen wants to submit a patch... :) I can barely even spell C ... -- ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. ___ ---(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] mysql replace in postgreSQL?
blackwater dev wrote: In MySQL, I can use the replace statement which either updates the data there or inserts it. Is there a comporable syntax to use in postgreSQL? I need to do an insert and don't want to have to worry about if the data is already there or not...so don't want to see if it there, if so do update if not insert...etc. Thanks. In Oracle this is called the MERGE statement, but it not yet in pg. It is on the TODO list, though. ---(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] Dump only functions...
Tino Wildenhain wrote:
Am Dienstag, den 18.10.2005, 15:31 -0600 schrieb Cristian Prieto:
Any of you knows is there is any way in pg_dump or anything to dump
just the functions from a database?
pg_dump -Fc -v -f temp.dump yourdatabase
pg_restore -l temp.dump | grep FUNCTION functionlist
pg_restore -L functionlist temp.dump yourfunctions.sql
of course you can just use your regular dump and so
skip the first part.
HTH
Tino
Another way of dumping only the functions, based off the view pga_functions:
-- view definition
create or replace view pga_functions as
select
l.lanname as language,
n.nspname||'.'||p.proname||'('
||pg_catalog.oidvectortypes(p.proargtypes)||')' as name,
t.typname as returntype,
'\n\n'||'CREATE OR REPLACE FUNCTION
'||n.nspname||'.'||p.proname||'('||pg_catalog.oidvectortypes(p.proargtypes)||')\n'||
' RETURNS '||t.typname||' AS'||'\n''\n' ||(select case when lanname 'c'
then replace(prosrc,'\'','\\\'') else replace(prosrc,'\'','\\\'')||'.so'
end)||'\n''\n'||' LANGUAGE ''' || l.lanname || ''' VOLATILE;\n' as source
from pg_proc p, pg_type t, pg_namespace n, pg_language l
where p.prorettype = t.oid and p.pronamespace = n.oid
and p.prolang = l.oid;
dev=# select source from pga_functions where name like 'public%'
dev-# \o dump_all_functions.sql;
This will dump public's functions to a file. You may want to play with the
settings of view etc to get the formatted results you want.
Note: I did not create this view, I found it in the archives a while back, along
with pga_objects, pga_columns, and pga_views.
Cheers,
Bricklen
--
___
This e-mail may be privileged and/or confidential, and the sender does
not waive any related rights and obligations. Any distribution, use or
copying of this e-mail or the information it contains by other than an
intended recipient is unauthorized. If you received this e-mail in
error, please advise me (by return e-mail or otherwise) immediately.
___
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Postgresql 8
Bob Pawley wrote: I am running version 8 on Windows. Why do I get error messages stating that functions and/or tables do not exist when these tables and functions are visible, accessible and very much do exist, as called? Bob Pawley It would probably help if you supplied some more details, such as how you called them, and how you tested that they are visible etc. Did you create them enclosed in double quotes? eg. dev=# create table Foo (x date); CREATE TABLE dev=# select * from Foo; ERROR: relation foo does not exist dev=# select * from Foo; x --- (0 rows) -- ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. ___ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Why database is corrupted after re-booting
snacktime wrote: I remember a few months back when someone hit the emergency power switch to the whole floor where we host at Internap. Subsequently the backup power system had a cascading failure. Livejournal, who also hosts there, was up all night and into the next day restoring their mysql databases after a bunch of them were corrupted. I believe they had write cache turned on. Of course our postgresql servers on scsi drives came right back up. If it wasn't for a couple of servers that won't reboot automatically if the power goes out I wouldn't have even had to go down to the data center. Chris I remember reading a detailed account on Livejournal about the hoops they had to jump through to get up and running again after that incident. Bit of a nightmare for them. -- ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. ___ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] storage sync failed on magnetic disk: Input/output error
Noticed this in one of my pg logs last Friday: LOG: could not fsync segment 0 of relation 1663/16387/22359: Input/output error ERROR: storage sync failed on magnetic disk: Input/output error This relation corresponds to a table, which receives thousands of inserts via COPY every couple of minutes. I've not noticed that message before now, nor since then. # select version(); PostgreSQL 8.1beta3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.5 (Debian 1:3.3.5-13) uname -a Linux dev12 2.6.11-1-686-smp #1 SMP Mon Jun 20 20:18:45 MDT 2005 i686 GNU/Linux 6 disk SCSI, hardware RAID 5 Any ideas on what could have caused this etc? -- ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. ___ ---(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] fine tuned database dump/reload?
Dan Armbrust wrote: Does postgresql have any facility to dump anything more fine grained than a database to a text file? For example, to mention a bad word, MySQL's dump command allows you to specify individual tables to dump snip PostgreSQL's pg_dump command seems rather limited in its abilities. Maybe I'm missing the command I'm looking for. Thanks, Dan pg_dump --help ... -n, --schema=SCHEMA dump the named schema only -s, --schema-onlydump only the schema, no data -t, --table=TABLEdump the named table only ... -- ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. ___ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] fine tuned database dump/reload?
Dan Armbrust wrote: Now I'm just filling the mailing list with mis-information. It actually ignores all but the last -t flag - so this only allows me to specify one table at a time, rather than several tables. I need to write up my use case so the maintainers can see why I want to be able to dump things in such a specific way - its not for backup purposes - so I'm kind of misusing the intent of the tool. More info in a bit. Dan Disregard my last reply, yours hadn't arrived yet. -- ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. ___ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Problems with group by ... order by
John D. Burger wrote: I can't figure out why the following doesn't work: select (case when count1 300 then 'Other' else country1 end) as country2, sum(count1) as count2 from (select coalesce(country, 'None') as country1, count(*) as count1 from userProfiles group by country1) as counts1 group by country2 order by (country2 = 'Other'), count2 desc Do either of these work for you? Note, completely untested, and just off the top of my head. select (case when count1 300 then 'Other' else country1 end) as country2, sum(count1) as count2 from (select coalesce(country, 'None') as country1, count(*) as count1 from userProfiles group by country1) as counts1 group by country2 order by (1 = 'Other'), count2 desc select (case when count1 300 then null else country1 end) as country2, sum(count1) as count2 from (select coalesce(country, 'None') as country1, count(*) as count1 from userProfiles group by country1) as counts1 group by country2 order by (1 is null), count2 desc -- ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. ___ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Get all table names that have a specific column
Emi Lu wrote: Greetings, I am not very familiar with the system views/tables in postgreSQL. I'd like to get all table names that have a column let's say named col1. For example, t1 (... col1 varchar(3) ... ) t2 (... col1 varchar(3) ... ) t3 (... ...) After querying the system tables/views, I can get the result something like : tables contain column col1 - t1 t2 (2 rows) Thanks a lot, Emi Check this posting: http://archives.postgresql.org/pgsql-admin/2005-03/msg00011.php Query the pga_columns view for the matches that you are looking for. eg: select tablename from pga_columns where columnname='col1'; -- ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. ___ ---(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] ERROR: bogus varno
8.1beta1, linux If I issue a query from a view with a WHERE condition, w/ EXPLAIN ANALYZE, I am receiving an error: dev#EXPLAIN ANALYZE select sum(bytes) from user_bw where id=33897; ERROR: bogus varno: 205 (user_bw is a VIEW) If I omit the WHERE condition, or the EXPLAIN ANALYZE, or query directly from a table, it runs fine. A bug perhaps? -- ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. ___ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] ERROR: bogus varno
Tom Lane wrote: Bricklen Anderson [EMAIL PROTECTED] writes: dev#EXPLAIN ANALYZE select sum(bytes) from user_bw where id=33897; ERROR: bogus varno: 205 Known bug, fixed a week or two back. regards, tom lane Thanks, I'll have to grab the more recent version. Cheers, Bricklen -- ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. ___ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] ERROR: bogus varno
Tom Lane wrote: Bricklen Anderson [EMAIL PROTECTED] writes: 8.1beta1, linux dev#EXPLAIN ANALYZE select sum(bytes) from user_bw where id=33897; ERROR: bogus varno: 205 Known bug, fixed a week or two back. regards, tom lane Yep, that fixed it. -- ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. ___ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] 8.1beta timezone question
I may have missed it in the docs, but were certain timestamp abbreviations phased out between 8.0.3 and 8.1 beta1? eg. (8.0.3) #SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'PST'; timezone - 16/02/2001 20:38:40 PST (8.1beta1) #SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'PST'; ERROR: time zone PST not recognised The tzname still works fine, though: SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'Canada/Pacific'; timezone - 16/02/2001 20:38:40 PST Or maybe there is a setting that I neglected to adjust to make this work on 8.1beta1? The same error occurs with several other timezone abbreviations that I tried. Cheers, Bricklen -- ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. ___ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] How to write jobs in postgresql
Douglas McNaught wrote: Is it possible to write jobs in postgresql if possible how should I write .please help me. What does write jobs mean? I'm assuming this person has an Oracle background, if so, jobs are Oracle's equivalent to a built-in cron scheduler. This has been discussed extensively in these lists in the past - whether or not to implement an pg version of Oracle's job mechanism, etc. -- ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. ___ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] problem inserting with sequence
germ germ wrote: Thank you all for your help. I got it working, once. Right after I made the change and tested it, everything worked perfect. Then I had a freak nose bleed- (This has to be my 3rd nose bleed in my life ever). I frantically starting closing windows and shells. While in my frantic state, I deleted my php script I had spent about 30+ hours working on. I don't back anything up on my test server so it's gone forever now. My stupidity for not backing up the test server, so my loss- lesson learned and will not make again. Well? Inquiring minds want to know... Did it work? Did closing windows and shells stop your nosebleed? :) -- ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. ___ ---(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] How to optimize select count(*)..group by?
David Fetter wrote: On Thu, Jul 28, 2005 at 09:19:49AM -0700, Bryan Field-Elliot wrote: We have this simple query: select status, count(*) from customer group by status; There is already a btree index on status, but, the customer table is huge, and this query must be executed very frequently... an explain on this query shows that it is quite costly (and we notice it runs slowly)... Can someone recommend the best technique to optimize this? We can create new indices, we can re-write this query.. But we'd rather not add new tables or columns if possible (not just to solve this problem). You're pretty much stuck with either writing triggers that modify a cache table or having your performance the way it is now. Cheers, D How about the new bitmap index? I wonder if that'll result in better performance for that type of query? -- ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. ___ ---(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]
wayne schlemitz wrote: How do I remove my self from this mail list I have tried in the past with no luck. Please sent specific instructions. Wayne From the web, you could try here: http://www.postgresql.org/community/lists/subscribe and click the unsubscribe action. ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. ___ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Statistics and Indexes
[EMAIL PROTECTED] wrote: Hi I am from a MSSQL background and am trying to understand something about statistics in PostgreSQL. Question 1: In MSSQL, if you create an index (and you are using MSSQL's default settings) the Server will automatically create appropriate statistics for you. Does this happen in PostgreSQL? Or should I explicitly create statistics for every one of my indexes? Q2 sort of answers Q1. Question 2: I believe ANALYZE keeps indexes and statistics up to date. How often should this be run (assume that my DB has 200,000 new records daily)? Try out a VACUUM ANALYZE every hour or three. -- ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. ___ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Extremely slow performance with 'select *' after insert
Collin Peters wrote: The table in question is a simple users table. The details are at the bottom of this message. The performance on this table was fine during testing with less than 100 users. Then we inserted about 37,000 records into the table. Now a 'SELECT * FROM pp_users' takes over 40 seconds!!. 37,000 records is not much at all so I am wondering why the slow execution time. Here are some stats and log output files. Running the query 'SELECT * FROM pp_users' -- On LAN connection (using pgadmin): Total query runtime: 14547 ms. Data retrieval runtime: 10453 ms. 37326 rows retrieved. On Internet connection (using pgadmin): Total query runtime: 32703 ms. Data retrieval runtime: 16109 ms. 37326 rows retrieved. On db server using psql (somewhat better but still slow for 37000 rows): devel=# select * from pp_users; Time: 912.779 ms Running the query 'EXPLAIN ANALYZE SELECT * FROM pp_users' --- Seq Scan on pp_users (cost=0.00..1597.26 rows=37326 width=1102) (actual time=0.029..33.043 rows=37326 loops=1) Total runtime: 44.344 ms (same stats when run on all computers (lan/internet/localhost) Anybody know what would cause things to be so slow? Seems kind of absurd really. Indexes shouldn't play a role since a 'select *' does a sequential scan. Even so there will be an index on the primary key (user_id) which is proved with the query: EXPLAIN ANALYZE SELECT * FROM pp_users WHERE user_id 100 Index Scan using pp_users_pkey on pp_users (cost=0.00..7.80 rows=4 width=1102) (actual time=0.080..0.246 rows=54 loops=1) Index Cond: (user_id 100) Let me know if any more information would help. This is postgresql 7.4.7 (also a unicode database). Regards, Collin Is that the time spent displaying the data on the screen etc? How long does this take: select count(*) from (SELECT * FROM pp_users) as t; Also, IIRC, this topic may have come up a few weeks ago about timings being off from pgadmin. -- ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. ___ ---(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] Execution shell commands from Function
[EMAIL PROTECTED] wrote: Hi, I made some tests of plsh with Postgresql 8.0.2 but it seems that it doesn't work. What's the easiest way to execute shell commands from a PostgreSQL function (afraid not possible from pgsql function...). Regards, Patrick Easiest way? No idea. Another way to do it is to use plperlu. -- ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. ___ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Newbie question
Hugo wrote: hi, is it possible to schedule the execution of an sql stored procedure in postgress on linux? thanks Hugo cron job: eg. Sat 2:30am 30 2 * * Sat psql -d dbname -c select your_func() -- ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. ___ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Changing column data type on an existing table
Joe Audette wrote: Hi, I have an app that I released with a particular field as varchar 255. Can someone give me a script example I can use to make an upgrade script to change it to text or at least to larger varchar without losing existing data? I support 3 different dbs in my app, Postgre is the newest and least familiar to me but I am trying to learn. Any help much appreciated. Joe Audette In v8 at least, you can issue: alter TABLE tablename ALTER column_name TYPE text; -- ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. ___ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] plpgsql no longer exists
Tom Lane wrote:
Bricklen Anderson [EMAIL PROTECTED] writes:
Once I recompile the function, I no longer get that message. Is there
anything else that I can check or do to make this stop happening? Or is
this a sign of things to come (possible corruption, etc?)
Well, the original error sounds like a disk drive lossage ... you might
want to think about replacing that drive sometime soon, before it drops
data from someplace more critical than an index.
In the meantime, look through the pg_proc.prolang column for entries
that don't match the OID of any row in pg_language. Probably you
could just UPDATE the ones that are wrong to make them match the OIDs
of the new rows. I'd suggest a dump and restore of the database
after you think you have it right, just to make sure everything
is sane and to get pg_depend back in sync.
regards, tom lane
Hi Tom,
Thanks for the tips. I tried to match up pg_proc.prolang and _any_ OID in the
pg_language table. There were NO matches at all. This seemed a bit odd, so I
checked an other db and that one had virtually the same numbers (and they didn't
match either).
=# select distinct prolang from pg_proc;
prolang
-
12
13
14
17813
63209
63212
63213
63214
(8 rows)
=# select * from pg_language ;
lanname | lanispl | lanpltrusted | lanplcallfoid | lanvalidator |lanacl
--+-+--+---+--+---
plpgsql | t | t| 17811 |17812 |
internal | f | f| 0 | 2246 |
c| f | f| 0 | 2247 |
plperlu | t | f| 17808 |0 |
sql | f | t| 0 | 2248 |
{=U/postgres}
plperl | t | t| 17808 |0 |
(6 rows)
Is it possible that I am misreading the output or your suggestion?
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] plpgsql no longer exists
Tom Lane wrote: =# select distinct prolang from pg_proc; prolang - 12 13 14 17813 63209 63212 63213 63214 (8 rows) That looks fine ... =# select * from pg_language ; Try select oid,lanname from pg_language. regards, tom lane Sorry, I see that I forgot to specify the oid. Thanks again ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] plpgsql no longer exists
Hi listers! I'll start with some details: select version(); PostgreSQL 8.0.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.5 (Debian 1:3.3.3-5) Upon compiling a new function that I was working on, I came across an error: could not read block 0 of relation 1663/17239/16709: Bad address which was being triggered by my trying to compile my function. The relation in question turned out to be pg_language. I reindexed that table and the could not read block... error went away, then I started to get the ...language plpgsql does not exist... (as shown after the following function): -- note, this is only a test function, but it yields the same error: dev=# create function text() returns void as $$ dev=# begin dev=# return; dev=# end; dev=# $$ language plpgsql; ERROR: language plpgsql does not exist HINT: You need to use createlang to load the language into the database. I then issued a createlang plpgsql this_db -- checked the pg_language table at this point (which I probably should have done before I went and ran the createlang command) dev=# select lanname,lanplcallfoid,lanvalidator from pg_language; lanname | lanplcallfoid | lanvalidator --- plpgsql | 17811 | 17812 plpgsql | 17811 | 17812 internal | 0 | 2246 c| 0 | 2247 plperlu | 17808 | 0 plperl | 17808 | 0 sql | 0 | 2248 Apparently plpgsql does exist. It also now had duplicate entries for plpgsql. I replaced the contents of the table with the all of the same values, minus one of the duplicates and reindexed it. I restarted my postmaster, and the missing language error went away. Now when I am running any function, I am getting: ERROR: cache lookup failed for language 17813 (or occasionally, 17810 or 17809). Once I recompile the function, I no longer get that message. Is there anything else that I can check or do to make this stop happening? Or is this a sign of things to come (possible corruption, etc?) Thanks for any help! Cheers, Bricklen ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] plpgsql no longer exists
Bricklen Anderson wrote: Hi listers! I'll start with some details: select version(); PostgreSQL 8.0.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.5 (Debian 1:3.3.3-5) Upon compiling a new function that I was working on, I came across an error: could not read block 0 of relation 1663/17239/16709: Bad address which was being triggered by my trying to compile my function. The relation in question turned out to be pg_language. I reindexed that table and the could not read block... error went away, then I started to get the ...language plpgsql does not exist... (as shown after the following function): -- note, this is only a test function, but it yields the same error: dev=# create function text() returns void as $$ dev=# begin dev=# return; dev=# end; dev=# $$ language plpgsql; ERROR: language plpgsql does not exist HINT: You need to use createlang to load the language into the database. I then issued a createlang plpgsql this_db -- checked the pg_language table at this point (which I probably should have done before I went and ran the createlang command) dev=# select lanname,lanplcallfoid,lanvalidator from pg_language; lanname | lanplcallfoid | lanvalidator --- plpgsql | 17811 | 17812 plpgsql | 17811 | 17812 internal | 0 | 2246 c| 0 | 2247 plperlu | 17808 | 0 plperl | 17808 | 0 sql | 0 | 2248 Apparently plpgsql does exist. It also now had duplicate entries for plpgsql. I replaced the contents of the table with the all of the same values, minus one of the duplicates and reindexed it. I restarted my postmaster, and the missing language error went away. Now when I am running any function, I am getting: ERROR: cache lookup failed for language 17813 (or occasionally, 17810 or 17809). Once I recompile the function, I no longer get that message. Is there anything else that I can check or do to make this stop happening? Or is this a sign of things to come (possible corruption, etc?) I'm also seeing one of my functions fail with ERROR: cache lookup failed for function 0 -- although this function is using language sql rather than plpgsql. This particular function is aggregating data from a view (which currently has no data), so should just finish without doing anything like it always has in the past. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] PostgreSQL still for Linux only?
tony wrote: Excuse me dear sir. There seems to be about 97% of the world that runs Windows that does not give you permission to be rude to a tiny minority who just happen to have written an insanely great database that runs quite nicely on their hobby OSs as well as the crap you call home. If you aren't pleased with the postgresql support on Windows don't use it!!! That is your freedom. Ours is to think (maybe wrongly) that it is much better running it on the BSDs and Linux of our choice. That is our freedom. There is nothing egoist about developing a great database server on an OS with a tiny user base. The egoists are elsewhere dear sir, far from the free software developers, in the closed source world. The code is there, it is free - go and improve it. Maybe you need a dictionary to look up the word egoist? Please go and troll over at MySQL. They have a Windows version too and maybe a lot more time and patience for rude people such as yourself. Tony This thread is getting a bit carried away, don't you think? If this keeps up, these fora run the risk of turning into the gong show that the c.d.oracle.* newsgroup frequently becomes. If you think it's a legitimate flame, why not ignore it, instead of adding to the noise? ---(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] Novice Question
Sean Davis wrote: On Mar 1, 2005, at 4:23 PM, Michael Romagnoli wrote: I am new to postgresql, having previously worked with mysql mostly. What kind of command would I run if I wanted to copy an entire table (along with renaming it, and, of course, all data from the first table - some of which is binary)? Thanks, -Mike ---(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 select * into table new_table from old_table; That's it. Sean you sure about that syntax? How about: create table new_table as select * from old_table; -- ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. ___ ---(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] to_char bug?
Ben Trewern wrote: Is there any reason why : SELECT char_length(to_char(1, '000')); Gives a result char_length - 4 (1 row) It seems that to_char(1, '000') gives a string 001 with a space in front. Is this a bug? Regards, Ben Try formatting the result: SELECT char_length(to_char(1, 'fm000')); char_length - 3 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Invalid headers and xlog flush failures
Alban Hertroys wrote: Bricklen Anderson wrote: Any ideas on what I should try next? Considering that this db is not in production yet, I _do_ have the liberty to rebuild the database if necessary. Do you have any further recommendations? I recall reading something in this ML about problems with the way that Ext3 FS recovers a dirty file system, could it be related? I really have no idea, but we _are_ running an ext3 fs on this particular server. -- ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. ___ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Invalid headers and xlog flush failures
Tom Lane wrote: Bricklen Anderson [EMAIL PROTECTED] writes: Tom Lane wrote: But anyway, the evidence seems pretty clear that in fact end of WAL is in the 73 range, and so those page LSNs with 972 and 973 have to be bogus. I'm back to thinking about dropped bits in RAM or on disk. memtest86+ ran for over 15 hours with no errors reported. e2fsck -c completed with no errors reported. Hmm ... that's not proof your hardware is ok, but it at least puts the ball back in play. Any ideas on what I should try next? Considering that this db is not in production yet, I _do_ have the liberty to rebuild the database if necessary. Do you have any further recommendations? If the database isn't too large, I'd suggest saving aside a physical copy (eg, cp or tar dump taken with postmaster stopped) for forensic purposes, and then rebuilding so you can get on with your own work. One bit of investigation that might be worth doing is to look at every single 8K page in the database files and collect information about the LSN fields, which are the first 8 bytes of each page. Do you mean this line from pg_filedump's results: LSN: logid 56 recoff 0x3f4be440 Special 8176 (0x1ff0) If so, I've set up a shell script that looped all of the files and emitted that line. It's not particularly elegant, but it worked. Again, that's assuming that it was the correct line. I'll write a perl script to parse out the LSN values to see if any are greater than 116 (which I believe is the hex of 74?). In case anyone wants the script that I ran to get the LSN: #!/bin/sh for FILE in /var/postgres/data/base/17235/*; do i=0 echo $FILE test_file; while [ 1==1 ]; do str=`pg_filedump -R $i $FILE | grep LSN`; if [ $? -eq 1 ]; then break fi echo $FILE: $str LSN_out; i=$((i+1)); done done In a non-broken database all of these should be less than or equal to the current ending WAL offset (which you can get with pg_controldata if the postmaster is stopped). We know there are at least two bad pages, but are there more? Is there any pattern to the bad LSN values? Also it would be useful to look at each bad page in some detail to see if there's any evidence of corruption extending beyond the LSN value. regards, tom lane NB. I've recreated the database, and saved off the old directory (all 350 gigs of it) so I can dig into it further. Thanks again for you help, Tom. Cheers, Bricklen ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Questions about functionality
Karl O. Pinc wrote: 4. Can I query an object in another database, like in Oracle's dblink? I'm no expert. I don't believe so. You can query across scheams in the same database but not across databases. You could do something (anything!) by writing an external function in C or whatever, but I couldn't say how much work that would take. Karl [EMAIL PROTECTED] Free Software: You don't pay back, you pay forward. -- Robert A. Heinlein Look in /contrib directory for dblink. That will enable you to query other pg databases. ---(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] Invalid headers and xlog flush failures
Bricklen Anderson wrote: Tom Lane wrote: Bricklen Anderson [EMAIL PROTECTED] writes: Tom Lane wrote: I would have suggested that maybe this represented on-disk data corruption, but the appearance of two different but not-too-far-apart WAL offsets in two different pages suggests that indeed the end of WAL was up around segment 972 or 973 at one time. Nope, never touched pg_resetxlog. My pg_xlog list ranges from 000100730041 to 0001007300FE, with no breaks. There are also these: 00010074 to 00010074000B That seems like rather a lot of files; do you have checkpoint_segments set to a large value, like 100? The pg_controldata dump shows that the latest checkpoint record is in the 73/41 file, so presumably the active end of WAL isn't exceedingly far past that. You've got 200 segments prepared for future activity, which is a bit over the top IMHO. But anyway, the evidence seems pretty clear that in fact end of WAL is in the 73 range, and so those page LSNs with 972 and 973 have to be bogus. I'm back to thinking about dropped bits in RAM or on disk. IIRC these numbers are all hex, so the extra 9 could come from just two bits getting turned on that should not be. Might be time to run memtest86 and/or badblocks. regards, tom lane Yes, checkpoint_segments is set to 100, although I can set that lower if you feel that that is more appropriate. Currently, the system receives around 5-8 million inserts per day (across 3 primary tables), so I was leaning towards the more is better philosophy. We ran e2fsck with badblocks option last week and didn't turn anything up, along with a couple of passes with memtest. I will run a full-scale memtest and post any interesting results. I've also read that kill -9 postmaster is not a good thing. I honestly can't vouch for whether or not this may or may not have occurred around the time of the initial creation of this database. It's possible, since this db started it's life as a development db at 8r3 then was bumped to 8r5, then on to 8 final where it has become a dev-final db. Assuming that the memtest passes cleanly, as does another run of badblocks, do you have any more suggestions on how I should proceed? Should I run for a while with zero_damaged_pages set to true and accpet the data loss, or just recreate the whole db from scratch? memtest86+ ran for over 15 hours with no errors reported. e2fsck -c completed with no errors reported. Any ideas on what I should try next? Considering that this db is not in production yet, I _do_ have the liberty to rebuild the database if necessary. Do you have any further recommendations? thanks again, Bricklen ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Invalid headers and xlog flush failures
Hi all, I recently came across some apparent corruption in one of our databases around a month ago. version: postgresql 8 (originally 8r3, now at 8.0.1), debian box The messages that we were originally getting in our syslog were about invalid page headers. After googling around, then dumping the page with pg_filedump, I decided to drop and recreate the affected table. This seemed to work for a while, until this message cropped up in the syslog during a heavy load: Feb 1 11:17:49 dev94 postgres[4959]: [470-2] 2005-02-01 11:17:49 PST CONTEXT: writing block 47272 of relation 1663/17235/57800 Feb 1 11:17:49 dev94 postgres[4959]: [471-1] 2005-02-01 11:17:49 PST WARNING: could not write block 47272 of 1663/17235/57800 Feb 1 11:17:49 dev94 postgres[4959]: [471-2] 2005-02-01 11:17:49 PST DETAIL: Multiple failures --- write error may be permanent. Feb 1 11:17:50 dev94 postgres[4959]: [472-1] 2005-02-01 11:17:50 PST ERROR: xlog flush request 972/FC932854 is not satisfied --- flushed only to 73/86D2640 This maps to an index. I reindexed it (and several other tables), and a 3 hours later, restarted my load process. Shortly after that, the same thing happened again (with different numbers this time): Feb 1 14:36:05 dev94 postgres[12887]: [626-2] 2005-02-01 14:36:05 PST CONTEXT: writing block 7502 of relation 1663/17235/151565 Feb 1 14:36:05 dev94 postgres[12887]: [627-1] 2005-02-01 14:36:05 PST WARNING: could not write block 7502 of 1663/17235/151565 Feb 1 14:36:05 dev94 postgres[12887]: [627-2] 2005-02-01 14:36:05 PST DETAIL: Multiple failures --- write error may be permanent. Feb 1 14:36:06 dev94 postgres[12887]: [628-1] 2005-02-01 14:36:06 PST ERROR: xlog flush request 973/3EF36C2C is not satisfied --- flushed only to 73/419878B4 Both sets are repeated continuously through the syslog. I pursued some references to XID wraparound, but that didn't seem likely from what I could see (unless I'm misreading the numbers) SELECT datname, age(datfrozenxid) FROM pg_database where datname='dev17'; datname |age -+ dev17 | 1074008776 Here is a pg_filedump of 151565: $pg_filedump -i -f -R 7502 /var/postgres/data/base/17235/151565 *** * PostgreSQL File/Block Formatted Dump Utility - Version 3.0 * * File: /var/postgres/data/base/17235/151565 * Options used: -i -f -R 7502 * * Dump created on: Tue Feb 1 14:34:14 2005 *** Block 7502 Header - Block Offset: 0x03a9c000 Offsets: Lower 988 (0x03dc) Block: Size 8192 Version2Upper3336 (0x0d08) LSN: logid115 recoff 0x39e855f4 Special 8176 (0x1ff0) Items: 242 Free Space: 2348 Length (including item array): 992 Error: Invalid header information. : 7300 f455e839 0100 dc03080d sU.9 0010: f01f0220 cc912800 e0912800 f4912800 ... ..(...(...(. 0020: 08922800 1c922800 30922800 44922800 ..(...(.0.(.D.(. snipped Data -- Item 1 -- Length: 20 Offset: 4556 (0x11cc) Flags: USED Block Id: 9016 linp Index: 2 Size: 20 Has Nulls: 0 Has Varwidths: 16384 11cc: 3823 02001440 0b00 022000cf [EMAIL PROTECTED] .. 11dc: 66f06500 f.e. Item 2 -- Length: 20 Offset: 4576 (0x11e0) Flags: USED Block Id: 9571 linp Index: 8 Size: 20 Has Nulls: 0 Has Varwidths: 16384 11e0: 6325 08001440 0b00 022000cf [EMAIL PROTECTED] .. 11f0: 66f06400 f.d. Item 3 -- Length: 20 Offset: 4596 (0x11f4) Flags: USED Block Id: 9571 linp Index: 3 Size: 20 Has Nulls: 0 Has Varwidths: 16384 11f4: 6325 03001440 0b00 022000cf [EMAIL PROTECTED] .. 1204: 66f06400 f.d. snipped Special Section - BTree Index Section: Flags: 0x0001 (LEAF) Blocks: Previous (1314) Next (1958) Level (0) 1ff0: 2205 a607 0100 ... *** End of Requested Range Encountered. Last Block Read: 7502 *** Can anyone suggest what I should try next, or if you need more information, I'll happily supply what I can. Inline are the changes I made to pg_filedump to get it to compile and work, as such I can't attest to pg_filedump's accuracy (which could be the source of those invalid header messages) # --- pg_filedump.c.old 2004-02-23 12:58:58.0 -0800 +++ ../pg_filedump-3.0/pg_filedump.c.new 2005-01-31 09:24:36.0 -0800 @@ -742,8 +742,8 @@ printf ( XID: min (%u) CMIN|XMAX: %u CMAX|XVAC: %u\n Block Id: %u linp Index: %u Attributes: %d Size: %d\n, - htup-t_xmin, htup-t_field2.t_cmin, - htup-t_field3.t_cmax, + htup-t_choice.t_heap.t_xmin,htup-t_choice.t_heap.t_cmin, +
Re: [GENERAL] Invalid headers and xlog flush failures
Tom Lane wrote: Bricklen Anderson [EMAIL PROTECTED] writes: Feb 1 11:17:50 dev94 postgres[4959]: [472-1] 2005-02-01 11:17:50 PST ERROR: xlog flush request 972/FC932854 is not satisfied --- flushed only to 73/86D2640 Hmm, have you perhaps played any games with pg_resetxlog in this database? I would have suggested that maybe this represented on-disk data corruption, but the appearance of two different but not-too-far-apart WAL offsets in two different pages suggests that indeed the end of WAL was up around segment 972 or 973 at one time. And now it's evidently ending at 73. Not good. What file names do you see in pg_xlog/, and what does pg_controldata show? regards, tom lane Hi Tom, Nope, never touched pg_resetxlog. My pg_xlog list ranges from 000100730041 to 0001007300FE, with no breaks. There are also these: 00010074 to 00010074000B $ pg_controldata pg_control version number:74 Catalog version number: 200411041 Database system identifier: 4738750823096876774 Database cluster state: in production pg_control last modified: Wed 02 Feb 2005 12:38:22 AM PST Current log file ID: 115 Next log file segment:66 Latest checkpoint location: 73/419A4BDC Prior checkpoint location:73/419A4B80 Latest checkpoint's REDO location:73/419A4BDC Latest checkpoint's UNDO location:0/0 Latest checkpoint's TimeLineID: 1 Latest checkpoint's NextXID: 4161807 Latest checkpoint's NextOID: 176864 Time of latest checkpoint:Wed 02 Feb 2005 12:38:22 AM PST Database block size: 8192 Blocks per segment of large relation: 131072 Bytes per WAL segment:16777216 Maximum length of identifiers:64 Maximum number of function arguments: 32 Date/time type storage: floating-point numbers Maximum length of locale name:128 LC_COLLATE: en_CA LC_CTYPE: en_CA ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Invalid headers and xlog flush failures
Tom Lane wrote: Bricklen Anderson [EMAIL PROTECTED] writes: Tom Lane wrote: I would have suggested that maybe this represented on-disk data corruption, but the appearance of two different but not-too-far-apart WAL offsets in two different pages suggests that indeed the end of WAL was up around segment 972 or 973 at one time. Nope, never touched pg_resetxlog. My pg_xlog list ranges from 000100730041 to 0001007300FE, with no breaks. There are also these: 00010074 to 00010074000B That seems like rather a lot of files; do you have checkpoint_segments set to a large value, like 100? The pg_controldata dump shows that the latest checkpoint record is in the 73/41 file, so presumably the active end of WAL isn't exceedingly far past that. You've got 200 segments prepared for future activity, which is a bit over the top IMHO. But anyway, the evidence seems pretty clear that in fact end of WAL is in the 73 range, and so those page LSNs with 972 and 973 have to be bogus. I'm back to thinking about dropped bits in RAM or on disk. IIRC these numbers are all hex, so the extra 9 could come from just two bits getting turned on that should not be. Might be time to run memtest86 and/or badblocks. regards, tom lane Yes, checkpoint_segments is set to 100, although I can set that lower if you feel that that is more appropriate. Currently, the system receives around 5-8 million inserts per day (across 3 primary tables), so I was leaning towards the more is better philosophy. We ran e2fsck with badblocks option last week and didn't turn anything up, along with a couple of passes with memtest. I will run a full-scale memtest and post any interesting results. I've also read that kill -9 postmaster is not a good thing. I honestly can't vouch for whether or not this may or may not have occurred around the time of the initial creation of this database. It's possible, since this db started it's life as a development db at 8r3 then was bumped to 8r5, then on to 8 final where it has become a dev-final db. Assuming that the memtest passes cleanly, as does another run of badblocks, do you have any more suggestions on how I should proceed? Should I run for a while with zero_damaged_pages set to true and accpet the data loss, or just recreate the whole db from scratch? Thanks again for your help. Cheers, Bricklen ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Oracle and PostgreSQL
Bruno Almeida do Lago wrote: OK! I've got to run now, but will search more about it tomorrow. Could you give me more details / references? You don't want to do it automatically. You want to do it by hand but it isn't that hard. Automatically? How? About the link between the two databases, where can I find how to do it?? in the /contrib/dblink directory of your installation Alternatively, you can use a function written in plperl and use DBI/DBD to pull from the Oracle database. -- ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. ___ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Best Linux Distribution
Joshua D. Drake wrote: Martijn van Oosterhout wrote: No difference whatsoever from PostgreSQL's point of view. Use whichever distribution is easiest for you to administer. After all, there's no point installing Postgres on a machine you don't know how to maintain or tune :) Actually there is a difference from PostgreSQL's point of view :) Namely in filesystems. The default filesystem on whitebox, RHEL and Fedora is EXT3 which really isn't that great. Sincerely, Joshua D. Drake Out of curiousity, which fs would you recommend for a ~terabyte oltp db? -- ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. ___ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
