[GENERAL] plpgsql and arrays
I'm trying to write function which accept array and insert each element
in table:
=# SELECT * FROM add_user('[EMAIL PROTECTED], [EMAIL PROTECTED]');
ERROR: column email is of type character varying but expression is
of type record
HINT: You will need to rewrite or cast the expression.
table is with values (uid, email):
uid - smallint - not null default nextval('users2_uid_seq'::regclass)
email - character varying(128) -
function is like this:
CREATE OR REPLACE FUNCTION add_user( VARCHAR(128)[] ) RETURNS SMALLINT AS $$
DECLARE
v_rec RECORD;
v_uid SMALLINT;
v_email VARCHAR(128);
empty BOOLEAN := TRUE;
BEGIN
FOR v_rec IN SELECT $1[i] FROM generate_series(array_lower($1,1),
array_upper($1,1)) s(i)
LOOP
IF ( empty = TRUE ) THEN
INSERT INTO users2 VALUES (DEFAULT, v_rec);
empty = FALSE;
SELECT INTO v_uid currval('users2_uid_seq');
ELSE
INSERT INTO users2 VALUES ((SELECT currval('users2_uid_seq')),
v_rec);
END IF;
END LOOP;
RETURN v_uid;
END;
$$ LANGUAGE 'plpgsql';
I'm trying to cast it to varchar, but it doesn't help.
---(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] plpgsql and arrays
Artis Caune wrote:
I'm trying to write function which accept array and insert each element
in table:
=# SELECT * FROM add_user('[EMAIL PROTECTED], [EMAIL PROTECTED]');
ERROR: column email is of type character varying but expression is
of type record
HINT: You will need to rewrite or cast the expression.
function is like this:
FOR v_rec IN SELECT $1[i] FROM generate_series(array_lower($1,1),
array_upper($1,1)) s(i)
...
INSERT INTO users2 VALUES ((SELECT currval('users2_uid_seq')),
v_rec);
Well, v_rec is a RECORD, just like the error says. You want the first
attribute. Try this:
FOR v_rec IN SELECT $1[i] AS username FROM ...
INSERT INTO users2 VALUES (..., v_rec.username)
However, I'd also just have a normal loop counting over the array
upper/lower bounds. Then you could just use:
INSERT INTO users2 VALUES (..., $1[i])
HTH
--
Richard Huxton
Archonet Ltd
---(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] Need setup help for Postgresql 8.1.3 on Solaris 10
Hello all, I need some hints how to setup Postgresql on a brand new testsystem. Due to heavy load on the current database servers, my boss decided to test a big server as a replacement for 5 other servers. ;-) The system is used in a extranet environment - over 50 percent of the load is produced from an online catalog. I doubt, that one system could handle the queries of 5 vehement used 3ghz-double-processor systems, so I would select another db scenario, but it worth to try. We have such a testsystem now, 4 x AMD Opteron (double core techn.) 885 2.6ghz , running with 32 GB Ram and fast 10.000rpm SAS discs, build-in in a nice sun case ;-) Sounds nice, but it doesn't perform like a thought it should. Maybe this is a misconfiguration of PostgreSQL on Solaris 10, it's my first time on this platform, maybe it is a problem with the hardware. Reading , e.g. dumping a database, seems to run at expected performance, so I am going to test this system with read-querys from the live system on monday, to see how it performs with hundrets of parallel queries in a minute. But my concerns are here: Restoring a 800mb database dump, produced with pg_dump from this system, really takes long. On the old linux RHEL 3.2ghz systems the restore takes 10 minutes (while serving extranets additional), but on the new system this takes nearly 40 minutes. What happens while restoring ? : - Creating the tables is fast. - While loading the data into the DB i do not see significant load, a postgres process is running with 2-3 % usage in the background, main of the time the top command claims that the postgres processes are sleeping. System load is 0.1. - While creating indexes the postgres daemon behaves like expected, nearly 12.5% usage, system load nearly 1 . Trying to store the sqldump on another partition than the harddisc did not help. Copying local on the harddisc is fast (serial read/write, okay). Why is the loading process so slow, what could block the write process ? Creating indizes is writing, too, so why is this normal fast ? I do not have configured autovacuum (would slow things down), I do not have moved databases or tables to different partitions (could be a speed improvement). Even with the current configuration, it should perform much better. Details to postgresql.conf , these are the values I changed (against defaults): lc_messages = 'de.UTF-8' lc_monetary = 'de_DE.UTF-8' lc_numeric = 'de_DE.UTF-8' lc_time = 'C' listen_addresses = '*' maintenance_work_mem = 32768 # 32 MB for vacuumdb etc... max_connections = 2000 max_fsm_pages = 30 max_fsm_relations = 5000 shared_buffers = 45 # min 16 or max_connections*2, 8KB each #// default 1000 stats_block_level = off stats_command_string = off stats_reset_on_server_start = off stats_row_level = off stats_start_collector = on temp_buffers = 10 work_mem = 102400 # min 64, size in KB #// 1024 Details to Semaphores and shared memory: prctl -n project.max-sem-ids -i task 330 task: 330 NAMEPRIVILEGE VALUEFLAG ACTION RECIPIENT project.max-sem-ids privileged512 - deny - prctl -n project.max-shm-memory -i task 330 task: 330 NAMEPRIVILEGE VALUEFLAG ACTION RECIPIENT project.max-shm-memory privileged 12.0GB - deny - Maybe somebody has an idea, thanx in advance, Andre Gellert
Re: [GENERAL] Need setup help for Postgresql 8.1.3 on Solaris 10
Gellert, Andre wrote: Hello all, I need some hints how to setup Postgresql on a brand new testsystem. Due to heavy load on the current database servers, my boss decided to test a big server as a replacement for 5 other servers. ;-) The system is used in a extranet environment - over 50 percent of the load is produced from an online catalog. I doubt, that one system could handle the queries of 5 vehement used 3ghz-double-processor systems, so I would select another db scenario, but it worth to try. We have such a testsystem now, 4 x AMD Opteron (double core techn.) 885 2.6ghz , running with 32 GB Ram and fast 10.000rpm SAS discs, How many disks? What RAID? build-in in a nice sun case ;-) Sounds nice, but it doesn't perform like a thought it should. Maybe this is a misconfiguration of PostgreSQL on Solaris 10, it's my first time on this platform, maybe it is a problem with the hardware. Reading , e.g. dumping a database, seems to run at expected performance, so I am going to test this system with read-querys from the live system on monday, to see how it performs with hundrets of parallel queries in a minute. But my concerns are here: Restoring a 800mb database dump, produced with pg_dump from this system, really takes long. On the old linux RHEL 3.2ghz systems the restore takes 10 minutes (while serving extranets additional), but on the new system this takes nearly 40 minutes. What happens while restoring ? : - Creating the tables is fast. - While loading the data into the DB i do not see significant load, a postgres process is running with 2-3 % usage in the background, main of the time the top command claims that the postgres processes are sleeping. System load is 0.1. Is your disk I/O saturating? Try vmstat/iostat (or whatever the Solaris equivalent is). - While creating indexes the postgres daemon behaves like expected, nearly 12.5% usage, system load nearly 1 . Trying to store the sqldump on another partition than the harddisc did not help. Copying local on the harddisc is fast (serial read/write, okay). Why is the loading process so slow, what could block the write process ? Creating indizes is writing, too, so why is this normal fast ? I do not have configured autovacuum (would slow things down), I do not have moved databases or tables to different partitions (could be a speed improvement). Even with the current configuration, it should perform much better. Details to postgresql.conf , these are the values I changed (against defaults): maintenance_work_mem = 32768 # 32 MB for vacuumdb etc... work_mem = 102400 # min 64, size in KB #// 1024 I think your work_mem is too large (100MB) and maintenance_work_mem too small, for a restore with 32GB of RAM anyway. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Optimize expresiions.
On Thursday 11 January 2007 15:12, Alban Hertroys wrote: Maybe your functions can be marked STABLE or even IMMUTABLE? That should help the planner evaluate them less often. They are immutable, nevertheless they will have to be computed for each record. I'd just like to have some influence on the order in case of lazy evaluation. Han Holl ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Optimize expresiions.
On Thursday 11 January 2007 15:03, Adam Rich wrote: How about this? select item, very_expensive_function(item) as exp, cheap from ( Select item, cheap_function(item) as cheap From atable where cheap_function(item) 0 ) sub where very_expensive_function(item) 0 Well, there is nothing wrong with the much simpler where cheap_function(item) 0 and where very_expensive_function(item) 0. It's all about the order of evaluation. Thanks, Han Holl ---(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] PG compilation
On Fri, Jan 12, 2007 at 11:50:19AM +0100, Albe Laurenz wrote: gmake[3]: Entering directory `/home/km/postgresql8.2.1/postgresql-8.2.1/src/pl/plpython' gcc -fPIC -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -g -fpic -shared -Wl,-soname,libplpython.so.0 plpython.o -L/usr/local/lib/python2.5/config -L../../../src/port -lpython2.5 -lpthread -ldl -lutil -lm -Wl,-rpath,'/usr/local/lib/python2.5/config' -o libplpython.so.0.0 /usr/bin/ld: /usr/local/lib/python2.5/config/libpython2.5.a(abstract.o): relocation R_X86_64_32 against `a local symbol' can not be used when making a shared object; recompile with -fPIC /usr/local/lib/python2.5/config/libpython2.5.a: could not read symbols: Bad value collect2: ld returned 1 exit status gmake[3]: *** [libplpython.so.0.0] Error 1 gmake[3]: Leaving directory `/home/km/postgresql8.2.1/postgresql-8.2.1/src/pl/plpython' I am on a x86_64 platform. any ideas whats going on here ? Not really. You should CC the list, because somebody there may know. You can try the following: ar -t /usr/local/lib/python2.5/config/libpython2.5.a abstract.o file abstract.o ya extracted the object file. What does the file command tell you? ya it says : abstract.o: ELF 64-bit LSB relocatable, AMD x86-64, version 1 (SYSV), not stripped Did you build Python yourself or did you install a package? I had installed python2.5 myself from source package. regards, KM ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] plpgsql and arrays
Thanks a lot!
Normal upper/lower loop works fine:
CREATE OR REPLACE FUNCTION add_user( VARCHAR(128)[] ) RETURNS SMALLINT AS $$
DECLARE
v_uid SMALLINT;
v_low SMALLINT;
v_upp SMALLINT;
empty BOOLEAN := TRUE;
BEGIN
SELECT INTO v_low array_lower($1,1);
SELECT INTO v_upp array_upper($1,1);
FOR i IN v_low .. v_upp LOOP
IF ( empty = TRUE ) THEN
INSERT INTO users2 VALUES (DEFAULT, $1[i]);
empty = FALSE;
SELECT INTO v_uid currval('users2_uid_seq');
ELSE
INSERT INTO users2 VALUES ((SELECT currval('users2_uid_seq')),
$1[i]);
END IF;
END LOOP;
RETURN v_uid;
END;
$$ LANGUAGE 'plpgsql';
Richard Huxton wrote:
Artis Caune wrote:
I'm trying to write function which accept array and insert each
element in table:
=# SELECT * FROM add_user('[EMAIL PROTECTED], [EMAIL PROTECTED]');
ERROR: column email is of type character varying but expression is
of type record
HINT: You will need to rewrite or cast the expression.
function is like this:
FOR v_rec IN SELECT $1[i] FROM generate_series(array_lower($1,1),
array_upper($1,1)) s(i)
...
INSERT INTO users2 VALUES ((SELECT currval('users2_uid_seq')),
v_rec);
Well, v_rec is a RECORD, just like the error says. You want the first
attribute. Try this:
FOR v_rec IN SELECT $1[i] AS username FROM ...
INSERT INTO users2 VALUES (..., v_rec.username)
However, I'd also just have a normal loop counting over the array
upper/lower bounds. Then you could just use:
INSERT INTO users2 VALUES (..., $1[i])
HTH
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] Corrupt database? 8.1/FreeBSD6.0
Tom Lane [EMAIL PROTECTED] wrote:So apparently there's some row in template0 that didn't get marked committed before the pg_clog segment for it went away. Given 8.1's rather schizophrenic view of whether it can modify template0 or not, this is not too surprising, but I thought we'd put in some defense against this happening. Alvaro? Jeff, had you changed your autovac settings recently? Given that autovac seems to be trying to hit template0 every few minutes, it's hard to see how the tuple got missed for long enough to be a problem. Sure enough I did make autovacuum more agressive about 30-45 days ago (have to check the logs to find the exact date). Was originally whatever default settings that came out of the box with 8.1. Naptime is currently set to 60 seconds. Am I to assume that this probably isn't the result of some operating system or filesystem misfeasance and that corrupt data in my 'real' databases is probably not an issue? - Now that's room service! Choose from over 150,000 hotels in 45,000 destinations on Yahoo! Travel to find your fit.
Re: [GENERAL] plpgsql and arrays
Artis Caune wrote: Thanks a lot! Normal upper/lower loop works fine: CREATE OR REPLACE FUNCTION add_user( VARCHAR(128)[] ) RETURNS SMALLINT I'd rename the function add_users() AS $$ DECLARE v_uid SMALLINT; v_low SMALLINT; v_upp SMALLINT; empty BOOLEAN := TRUE; BEGIN SELECT INTO v_low array_lower($1,1); SELECT INTO v_upp array_upper($1,1); You can also write this: v_low := array_lower($1,1); -- Richard Huxton Archonet Ltd ---(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] Corrupt database? 8.1/FreeBSD6.0
Jeff Amiel wrote: Sure enough I did make autovacuum more agressive about 30-45 days ago (have to check the logs to find the exact date). Was originally whatever default settings that came out of the box with 8.1. Naptime is currently set to 60 seconds. Am I to assume that this probably isn't the result of some operating system or filesystem misfeasance and that corrupt data in my 'real' databases is probably not an issue? Did you perchance connect to template0 (probably marking it as connectable in the process), made some modification there, and then mark it as non-connectable again, without executing VACUUM FREEZE on it? AFAICS we only execute VACUUM FREEZE on it, so we shouldn't leave any unfrozen tuples. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Need setup help for Postgresql 8.1.3 on Solaris 10
Hello Richard, We have such a testsystem now, 4 x AMD Opteron (double core techn.) 885 2.6ghz , running with 32 GB Ram and fast 10.000rpm SAS discs, How many disks? What RAID? 4 Disks, RAID1. Is your disk I/O saturating? Try vmstat/iostat (or whatever the Solaris equivalent is). Hard to say, due to account restrictions I cannot control more than this vmstat output gives me: This is during loading Data, 98% idle: -bash-3.00$ vmstat 5 kthr memorypagedisk faults cpu r b w swap free re mf pi po fr de sr m1 m1 m1 m2 in sy cs us sy id 0 0 0 31865080 27723492 248 43 138 118 79 0 5 1 1 1 0 6873 4235 1481 2 1 98 0 0 0 31675384 27518448 603 14 0 0 0 0 0 0 0 0 0 15808 1695 1243 1 1 98 0 0 0 31675384 27518436 609 0 0 0 0 0 0 0 0 0 0 16027 1703 1266 1 1 98 0 0 0 31675372 27518408 602 0 0 0 0 0 0 0 0 0 0 15808 1682 1231 1 1 98 0 0 0 31675356 27513660 1280 0 0 0 0 0 0 0 0 0 0 44234 1908 861 1 1 98 0 0 0 31675356 27511444 369 0 0 0 0 0 0 0 0 0 0 13969 1270 952 1 1 99 0 0 0 31675356 27510328 343 0 0 0 0 0 0 4 4 4 0 13053 1192 908 1 1 99 0 0 0 31675344 27509688 426 1 0 0 0 0 0 0 0 0 0 14353 1371 1028 1 1 98 0 0 0 31675332 27513672 552 0 0 0 0 0 0 0 0 0 0 17503 1675 1243 1 1 98 0 0 0 31675332 27518468 570 0 0 0 0 0 0 0 0 0 0 17269 1737 1276 1 1 98 0 0 0 31675320 27519528 561 0 0 0 0 0 0 0 0 0 0 15942 1709 1276 1 1 98 0 0 0 31675320 27519440 598 169 0 0 0 0 0 0 0 0 0 15553 1738 1248 1 1 98 0 0 0 31674428 27527404 3311 222 0 120 120 0 0 0 0 0 0 44244 3384 1259 1 2 97 0 0 0 31675320 27539800 1665 0 0 0 0 0 0 0 0 0 0 37718 2151 1022 1 1 98 0 0 0 31674644 27520952 408 148 0 80 80 0 0 0 0 0 0 13916 1459 1066 1 1 99 0 0 0 31674196 27512960 403 146 0 40 40 0 0 0 0 0 0 13958 1425 1031 1 1 99 0 0 0 31675320 27510932 437 222 0 80 80 0 0 0 0 0 0 17203 1550 1097 1 1 98 0 0 0 31675320 27513352 402 75 0 0 0 0 0 0 0 0 0 16961 1425 1039 1 1 99 kthr memorypagedisk faults cpu r b w swap free re mf pi po fr de sr m1 m1 m1 m2 in sy cs us sy id 0 0 0 31674644 27516248 354 148 0 40 40 0 0 0 0 0 0 15408 1283 953 1 1 99 0 0 0 31674644 27519036 345 296 0 80 80 0 0 0 0 0 0 14854 1297 915 1 1 99 0 0 0 31675320 27519880 357 0 0 0 0 0 0 0 0 0 0 14000 1254 957 1 1 99 0 0 0 31675320 27518796 335 0 0 0 0 0 0 0 0 0 0 28471 1575 886 1 1 98 0 0 0 31675288 27515356 252 2 0 6 6 0 0 0 0 0 0 15763 1650 1245 1 1 98 0 0 0 31675288 27517308 265 0 0 0 0 0 0 0 0 0 0 16911 1741 1285 1 1 98 0 0 0 31675288 27519112 281 0 0 0 0 0 0 0 0 0 0 28401 1858 1167 1 1 98 0 0 0 31674396 27518676 391 217 0 0 0 0 0 0 0 0 0 14281 1334 950 1 1 99 0 0 0 31675236 27519480 386 3 0 80 80 0 0 0 0 0 0 14389 1305 971 1 1 99 0 0 0 31675160 27517528 522 0 0 0 0 0 0 0 0 0 0 14958 1559 1142 1 1 99 0 0 0 31674968 27515712 522 0 0 0 0 0 0 0 0 0 0 15553 1568 1159 1 1 98 0 0 0 31674924 27518104 533 0 0 0 0 0 0 0 0 0 0 16687 1595 1173 1 1 99 0 0 0 31674924 27517816 529 0 0 0 0 0 0 0 0 0 0 15607 1581 1174 1 1 99 0 0 0 31674520 27516436 570 0 0 0 0 0 0 0 0 0 0 15876 1612 1172 1 1 99 0 0 0 31674520 27516280 588 0 0 0 0 0 0 0 0 0 0 15998 1612 1192 1 1 98 0 0 0 31674412 27515228 1312 0 0 0 0 0 0 0 0 0 0 46397 2165 1031 1 1 98 0 0 0 31673996 27510360 318 0 0 0 0 0 0 0 0 0 0 12950 1082 849 0 1 99 0 0 0 31673996 27508960 408 203 0 40 40 0 0 0 0 0 0 14094 1318 883 0 1 99 0 0 0 31673996 27507568 443 0 0 0 0 0 0 0 0 0 0 14330 1373 1052 1 1 98 and so on. During the whole import of data it keeps mainly 99% idle. This is during working on indexes creation: -bash-3.00$ vmstat 5 kthr memorypagedisk faults cpu r b w swap free re mf pi po fr de sr m1 m1 m1 m2 in sy cs us sy id 0 0 0 31851400 27708172 252 44 129 111 74 0 5 1 0 0 0 7169 4006 1419 2 1 98 0 0 0 31598656 27425288 50 14 0 0 0 0 0 0 0 0 0 1608 72 156 13 0 87 0 0 0 31598656 27425224 6973 0 0 0 0 0 0 0 0 0 0 147690 3516 161 12 4 84 0 0 0 31598656 27424696 13194 0 0 3 3 0 0 0 0 0 0 279048 6620 147 12 7 81 0 0 0 31598656 27424116 14528 0 0 0 0 0 0 0 0 0 0 307360 7285 153 12 8 80 0 0 0 31598656 27423852 13503 0 0 0 0 0 0 0 0 0 0 285909 6767 149 12 7 81 0 0 0 31598656 27424584 3258 0 0 326 326 0 0 0 0 0 0 69522 1682 197 12 2 85 0 0 0 31598652 27425244 0 0 0 0 0 0 0 0 0 0 0 480 37 184 13 0 87 0 0 0 31598636 27425228 0 0 0 0 0 0 0 0 0 0 0 420 33 156 13 0 87 0 0 0 31598636 27425228 0 0 0 0 0 0 0 0 0 0 0 418 32 152 13 0 87 0 0 0 31598636 27425228 0 0 0 0 0 0 0 0 0 0 0 433 34 166 13 0 87 0 0 0 31598636 27425228 0 0 0 0 0 0 0 0 0 0 0 423 35 156 13 0 87 0 0 0 31598616 27425208 0 0 0 0 0 0 0 0 0 0 0 413 33 150 13
[GENERAL] error in open cursor
Hello all, I am getting a syntax error near open cursor, can anybody please point me what is missing. i am using Postgresql 8.2 CREATE OR REPLACE FUNCTION sp_gen_foreign_keys_tables(OUT par_result integer, OUT par_childtables text, IN par_colname character varying , IN par_colvalue integer) AS $BODY$ DECLARE tablename varchar(300); columnname varchar(300); countno integer; curfroeign cursor (columnname varchar(300)) is select distinct(fk_relation),fk_column from foreign_keys where pk_relation in (select pk_relation from foreign_keys where pk_column=columnname); var_str1 varchar(300); BEGIN open curforeign(par_colname); FETCH curforeign INTO tablename,columnname; var_str1:='select count(*) into'||countno||'from' ||tablename || 'where' ||columnname||'='||parcolvalue; execute var_str1; if countno 0 then par_childtables:=par_childtables || ',' ||tablename; endif ; close curfroeign ; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; Need a quick answer? Get one in minutes from people who know. Ask your question on www.Answers.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] WAL question
Hello. Lets's imagine a sequence in time: (1) Some transactions commit data (simple table inserts/updates). (2) I get data from a table using select. (3) A CHECKPOINT occurs, data from pg_xlog is copied to actual table data files. Questions: Where does PostgreSQL read data during (2) process? For example, I've got some old data and recently committed data there(but not passed CHECKPOINT). Would it scan both pg_xlog(for committed data that dind't pass CHECKPOINT) and table data files? What happens if an index could be used? Evgeny. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Corrupt database? 8.1/FreeBSD6.0
Alvaro Herrera [EMAIL PROTECTED] wrote: Did you perchance connect to template0 (probably marking it as connectable in the process), made some modification there, and then mark it as non-connectable again, without executing VACUUM FREEZE on it? AFAICS we only execute VACUUM FREEZE on it, so we shouldn't leave any unfrozen tuples. Nope. Have never touched template0...haven't made any changes to pg_hba.conf (which is how I assume you would make template0 connectable) until last night when we determined that template0 might be at fault and we were going to run pg_filedump on it to see if we could find anything useful. I assume this is similar/identical to this issue: http://archives.postgresql.org/pgsql-hackers/2006-03/msg01294.php Any ideas on how should I move forward? - Finding fabulous fares is fun. Let Yahoo! FareChase search your favorite travel sites to find flight and hotel bargains.
Re: [GENERAL] WAL question
Evgeny Gridasov wrote: Hello. Lets's imagine a sequence in time: (1) Some transactions commit data (simple table inserts/updates). (2) I get data from a table using select. (3) A CHECKPOINT occurs, data from pg_xlog is copied to actual table data files. Questions: Where does PostgreSQL read data during (2) process? Shared buffers, which means that data that's not in memory is read from disk, and data that is in memory is read from there. A checkpoint evicts stuff from shared buffers to disk. Would it scan both pg_xlog(for committed data that dind't pass CHECKPOINT) and table data files? pg_xlog is not read except in crash recovery. What happens if an index could be used? Same thing. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] error in open cursor
Ashish Karalkar wrote: Hello all, I am getting a syntax error near open cursor, can anybody please point me what is missing. i am using Postgresql 8.2 CREATE OR REPLACE FUNCTION sp_gen_foreign_keys_tables(OUT par_result integer, OUT par_childtables text, IN par_colname character varying , IN par_colvalue integer) AS $BODY$ DECLARE tablename varchar(300); columnname varchar(300); countno integer; curfroeign cursor (columnname varchar(300)) is select distinct(fk_relation),fk_column from foreign_keys where pk_relation in (select pk_relation from foreign_keys where pk_column=columnname); var_str1 varchar(300); BEGIN open curforeign(par_colname); Postgres doesn't know what curforeign is, it does however know what curfroeign is ;) -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Corrupt database? 8.1/FreeBSD6.0
Tom Lane wrote: Jeff Amiel [EMAIL PROTECTED] writes: We've downloaded/compiled pg_filedump, but are stumped as to what relation (or even what database) to start with. Turn up log_min_messages to DEBUG2 and you'll be able to see which table autovac is failing at. If I had to bet I'd bet on template0.pg_statistic ... Hmm, that would mean an ANALYZE got done on template0, no? ... but AFAICT process_whole_db() always sets analyze=false. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Corrupt database? 8.1/FreeBSD6.0
Alvaro Herrera [EMAIL PROTECTED] writes: Hmm, that would mean an ANALYZE got done on template0, no? ... but AFAICT process_whole_db() always sets analyze=false. The thing that's bothering me is that I don't see any certainty that template0 is only processed via the process_whole_db() path. In the 8.1 code, the existence of a stats-collector DB entry causes a DB to enter the normal round-robin processing path ... and I'm wondering whether the mere act of autovac connecting due to process_whole_db() doesn't cause such an entry to come into existence. IOW once template0 has been vacuumed once for anti-wraparound, it starts receiving normal vacuums, which will cause their own XIDs to appear in pg_statistic at least, and then you're at risk: if autovacuuming stops for awhile, those XIDs will fall below the pg_clog horizon because template0 is not considered when determining the cutoff. 8.2's approach is saner but I think we need some sort of band-aid in the 8.1 branch... regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Corrupt database? 8.1/FreeBSD6.0
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Hmm, that would mean an ANALYZE got done on template0, no? ... but AFAICT process_whole_db() always sets analyze=false. The thing that's bothering me is that I don't see any certainty that template0 is only processed via the process_whole_db() path. In the 8.1 code, the existence of a stats-collector DB entry causes a DB to enter the normal round-robin processing path ... and I'm wondering whether the mere act of autovac connecting due to process_whole_db() doesn't cause such an entry to come into existence. Hmm, as far as I can tell, the database entry would not be created merely by a vacuum. The only way to create a database entry in pgstat is by calling pgstat_recv_tabstat(); and pgstat_report_tabstat is only called in postgres.c (not invoked via autovacuum) and in pgstat_beshutdown_hook (not sure if this one is). So I agree that there is a risk if the user connects to template0 and the database pgstat entry gets created -- but that doesn't seem to be the case here. Confirmation on which table is causing the trouble would be good. 8.2's approach is saner but I think we need some sort of band-aid in the 8.1 branch... Maybe we could forcibly activate the freeze mode on a template database? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Corrupt database? 8.1/FreeBSD6.0
Alvaro Herrera [EMAIL PROTECTED] writes: Hmm, as far as I can tell, the database entry would not be created merely by a vacuum. The only way to create a database entry in pgstat is by calling pgstat_recv_tabstat(); and pgstat_report_tabstat is only called in postgres.c (not invoked via autovacuum) and in pgstat_beshutdown_hook (not sure if this one is). The shutdown hook surely is called, and the scenario for sending stats seems pretty obvious: stats_block_level = ON. Maybe we could forcibly activate the freeze mode on a template database? Might not be a bad idea. And even more to the point, forcibly disable analyze. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Corrupt database? 8.1/FreeBSD6.0
Jeff Amiel [EMAIL PROTECTED] writes: Any ideas on how should I move forward? Well, if the problem is indeed in pg_statistic, it'll be easy to repair (think TRUNCATE...). Have you turned up the logging level to find out? BTW, please don't do anything to try to correct the problem until we're pretty sure we understand how this happened --- we might ask you for more info. AFAICS this isn't having any bad effects except for bleats in your log file, so you can wait. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Need setup help for Postgresql 8.1.3 on Solaris
Gellert, Andre wrote: Hello Richard, We have such a testsystem now, 4 x AMD Opteron (double core techn.) 885 2.6ghz , running with 32 GB Ram and fast 10.000rpm SAS discs, How many disks? What RAID? 4 Disks, RAID1. Is your disk I/O saturating? Try vmstat/iostat (or whatever the Solaris equivalent is). Hard to say, due to account restrictions I cannot control more than this vmstat output gives me: This is during loading Data, 98% idle: -bash-3.00$ vmstat 5 kthr memorypagedisk faults cpu r b w swap free re mf pi po fr de sr m1 m1 m1 m2 in sy cs us sy id 0 0 0 31865080 27723492 248 43 138 118 79 0 5 1 1 1 0 6873 4235 1481 2 1 98 0 0 0 31675384 27518448 603 14 0 0 0 0 0 0 0 0 0 15808 1695 1243 1 1 98 0 0 0 31675384 27518436 609 0 0 0 0 0 0 0 0 0 0 16027 1703 1266 1 1 98 0 0 0 31675372 27518408 602 0 0 0 0 0 0 0 0 0 0 15808 1682 1231 1 1 98 0 0 0 31675356 27513660 1280 0 0 0 0 0 0 0 0 0 0 44234 1908 861 1 1 98 More interestingly your disks appear to be doing nothing. Which can't be right. They're not all zeroes though, so I'm not sure why you're seeing no disk activity. Nothing's blocking on disk I/O though... That also seems like a large number of interrupts for an idle machine. 0 0 0 31673996 27510360 318 0 0 0 0 0 0 0 0 0 0 12950 1082 849 0 1 99 0 0 0 31673996 27508960 408 203 0 40 40 0 0 0 0 0 0 14094 1318 883 0 1 99 0 0 0 31673996 27507568 443 0 0 0 0 0 0 0 0 0 0 14330 1373 1052 1 1 98 and so on. During the whole import of data it keeps mainly 99% idle. This is during working on indexes creation: -bash-3.00$ vmstat 5 kthr memorypagedisk faults cpu r b w swap free re mf pi po fr de sr m1 m1 m1 m2 in sy cs us sy id 0 0 0 31851400 27708172 252 44 129 111 74 0 5 1 0 0 0 7169 4006 1419 2 1 98 0 0 0 31598656 27425288 50 14 0 0 0 0 0 0 0 0 0 1608 72 156 13 0 87 0 0 0 31598656 27425224 6973 0 0 0 0 0 0 0 0 0 0 147690 3516 161 12 4 84 0 0 0 31598656 27424696 13194 0 0 3 3 0 0 0 0 0 0 279048 6620 147 12 7 81 0 0 0 31598656 27424116 14528 0 0 0 0 0 0 0 0 0 0 307360 7285 153 12 8 80 0 0 0 31598656 27423852 13503 0 0 0 0 0 0 0 0 0 0 285909 6767 149 12 7 81 Now that's a *huge* number of interrupts, unless I'm mis-reading this. I'm afraid I don't know enough about Solaris to say for sure, but there's something strange going on here. Looks fine, doesn't it? I think your work_mem is too large (100MB) and maintenance_work_mem too small, for a restore with 32GB of RAM anyway. I decreased the import time from 40 minutes to 32 minutes with changing the parameters to: shared_buffers = 45 # kept same temp_buffers = 10# kept same work_mem = 32768 # now 32mb , before: 102400 = 100mb maintenance_work_mem = 2048000 # now 2.048mb, before: 32768 = 32mb Should I encrease these parameters? Nothing that should cause these problems. Anything unusual about your installation? Any oddities from ./configure or similar? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Corrupt database? 8.1/FreeBSD6.0
Tom Lane [EMAIL PROTECTED] wrote: Well, if the problem is indeed in pg_statistic, it'll be easy to repair (think TRUNCATE...). Have you turned up the logging level to find out? BTW, please don't do anything to try to correct the problem until we're pretty sure we understand how this happened --- we might ask you for more info. AFAICS this isn't having any bad effects except for bleats in your log file, so you can wait. We turned up the logging this morning.(debug2) and are awaiting the issue to re-occur. Will report back then. Thanks. - It's here! Your new message! Get new email alerts with the free Yahoo! Toolbar.
Re: [GENERAL] RESTORE Error
Thanks for the reply, Tom. Yes, I had copied some data from a VIEW into a TABLE (named temp_measurement), then deleted the VIEW and renamed the TABLE, but must've created my script before performing the rename... And, yes, it was a Data-only restore. So, after recreating my scripts and creating my database and loading the data, it's FINALLY working (yeah)!! =) And, just for the record we're running version 8.0.8 of Postgres.. C:\Program Files\PostgreSQL\8.0\binpsql --version psql (PostgreSQL) 8.0.8 Thanks for everyone's help throughtout the week working through the issues that I've had; you guys ROCK and the help is greatly appreciated! -Jeanna -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Tom Lane Sent: Thursday, January 11, 2007 9:28 PM To: Jeanna Geier Cc: pgsql-general Subject: Re: [GENERAL] RESTORE Error Jeanna Geier [EMAIL PROTECTED] writes: And log file shows the following (what I posted before was through pgAdmin): 2007-01-11 15:38:47 ERROR: relation temp_measurement does not exist 2007-01-11 15:38:47 ERROR: syntax error at or near 7094982 at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near 67459 at character 1 OK, that makes it come a bit into focus. I think what pg_restore tried to send was COPY temp_measurement FROM STDIN; 7094982 ... rest of row 1 ... 67459 ... rest of row 2 ... and the COPY command failed because of the stated reason, and then the backend found itself chewing on a lot of data that was being sent as SQL commands (because back then pg_restore depended on feedback from the backend to tell whether an archive entry was COPY data or SQL commands). So the bottom line here is you're trying to restore into a table that's not there. How come? I suppose you were trying a data-only restore, else this is a pg_dump or pg_restore bug. Another point is that AFAICT 8.0 pg_restore does know enough to stop if the COPY command fails --- least the case seems to work for me. [ digs in CVS logs... ] Oh, it seems we fixed that in 8.0.7: 2006-02-05 15:59 tgl * src/bin/pg_dump/: pg_backup_archiver.c, pg_backup_archiver.h, pg_backup_db.c (REL8_0_STABLE): Fix pg_restore to properly discard COPY data when trying to continue after an error in a COPY statement. Formerly it thought the COPY data was SQL commands, and got quite confused. Stephen Frost So I suppose you're running some earlier 8.0.x subrelease, in which case an update would be an extremely good idea. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] WAL question
Hello. So, what happens if I set checkpoint_segments and checkpoint_timeout very high, and, shared_buffers to a very low value? Will PostgreSQL force a checkpoint when it has not enough memory in shared_buffers for some new un-checkpointed data ? On Fri, 12 Jan 2007 10:49:04 -0300 Alvaro Herrera [EMAIL PROTECTED] wrote: Evgeny Gridasov wrote: Hello. Lets's imagine a sequence in time: (1) Some transactions commit data (simple table inserts/updates). (2) I get data from a table using select. (3) A CHECKPOINT occurs, data from pg_xlog is copied to actual table data files. Questions: Where does PostgreSQL read data during (2) process? Shared buffers, which means that data that's not in memory is read from disk, and data that is in memory is read from there. A checkpoint evicts stuff from shared buffers to disk. Would it scan both pg_xlog(for committed data that dind't pass CHECKPOINT) and table data files? pg_xlog is not read except in crash recovery. What happens if an index could be used? Same thing. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] WAL question
Evgeny Gridasov wrote: Hello. So, what happens if I set checkpoint_segments and checkpoint_timeout very high, and, shared_buffers to a very low value? Will PostgreSQL force a checkpoint when it has not enough memory in shared_buffers for some new un-checkpointed data ? It won't force a checkpoint -- it will release memory by causing individual buffer evictions. (What checkpoint does is evicting all buffers in one go, but backends can cause a buffer to be evicted if they need it for something else). -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Version 8.2 for HP-UX
Does anyone know where to get postgreSQL 8.2 binaries or depot for HP-UX? We have a problem because of non-conforming backslash handling, and I understand that's been fixed in 8.2. -- Al Balmer Sun City, AZ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] FK Constraint on index not PK
Dear community members, I'm having a quite strange behaviour while trying to drop some index. We have some tables with two indexes on a primary key. The first one was automatically created by the primary constraint. The second one was manually created on the same column. Don't know why, but I would now want to suppress it. The first index is : foo_pkey The second one : i_foo_pk The constraint on table bar is fk_bar_foo references foo(id) But, when trying to drop the second index I get the following message : NOTICE: constraint fk_bar_foo on table t_foo depends on index i_foo_pk The database server is 7.4.5 . Having dumped database and restored it on a 8.2 server, I could drop the second index without any problem. The fact is I could do that as I indded what to migrate all databases from 7.4 to 8.2. But I would prefer not to recreate every index before dropping the non necessary one. And duplicate indexes are surely unnecessary... I have read in some thread that these troubles are known and have been corrected in versions 7.4.5. But, droping them before migrating is an option I'd prefer to use. So I wonder if ther is a way to indicate my foreign key it has to use the right primarry key constraint and not an arbitrary index on that primary key. (Almost 10 databases and 300 tables to migrate with something like 130 indexes badly created). So I'd alse prefer not to drop every fk constraint before dropping index and recreating constraint... Thanks by advance Stéphane Schildknecht ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Version 8.2 for HP-UX
AFAIK, PostgreSQL is only supplied in Win32 and Fedora/Redhat flavors. Debian, Gentoo, and FreeBSD maintain their own binary packages themselves. For HP-UX, you need to build from source. http://www.postgresql.org/docs/faqs.FAQ_HPUX.html -- Brandon Aiken CS/IT Systems Engineer -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Al Balmer Sent: Friday, January 12, 2007 11:20 AM To: pgsql-general Subject: [GENERAL] Version 8.2 for HP-UX Does anyone know where to get postgreSQL 8.2 binaries or depot for HP-UX? We have a problem because of non-conforming backslash handling, and I understand that's been fixed in 8.2. -- Al Balmer Sun City, AZ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] FK Constraint on index not PK
On Fri, 2007-01-12 at 17:50 +0100, Stéphane Schildknecht wrote: Dear community members, I'm having a quite strange behaviour while trying to drop some index. We have some tables with two indexes on a primary key. The first one was automatically created by the primary constraint. The second one was manually created on the same column. Don't know why, but I would now want to suppress it. Drop the second index. It is redundant. Joshua D. Drake ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate ---(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] Checkpoint request failed on version 8.2.1.
Comments? Note when it happens, and if it doesn't succeed for some value of too long, at least escalate to ERROR message, possibly fail. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(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] FK Constraint on index not PK
On Fri, 2007-01-12 at 10:50, Stéphane Schildknecht wrote: Dear community members, I'm having a quite strange behaviour while trying to drop some index. We have some tables with two indexes on a primary key. The first one was automatically created by the primary constraint. The second one was manually created on the same column. Don't know why, but I would now want to suppress it. The first index is : foo_pkey The second one : i_foo_pk The constraint on table bar is fk_bar_foo references foo(id) But, when trying to drop the second index I get the following message : NOTICE: constraint fk_bar_foo on table t_foo depends on index i_foo_pk The database server is 7.4.5 . Having dumped database and restored it on a 8.2 server, I could drop the second index without any problem. The fact is I could do that as I indded what to migrate all databases from 7.4 to 8.2. But I would prefer not to recreate every index before dropping the non necessary one. And duplicate indexes are surely unnecessary... I have read in some thread that these troubles are known and have been corrected in versions 7.4.5. But, droping them before migrating is an option I'd prefer to use. Simple. Backup your db just in case, then upgrade in place to the latest 7.4 (7.4.15 or so) then you should be able to drop the indexes in place. patch upgrades do NOT require dump / reload, so 7.4.5 - 7.4.15 should only require a brief period where you shut down the db while you install the new version. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Checkpoint request failed on version 8.2.1.
Scott Ribe [EMAIL PROTECTED] writes: Note when it happens, and if it doesn't succeed for some value of too long, at least escalate to ERROR message, possibly fail. ERROR and fail are the same thing. We could do this, and it wouldn't even be much code, but it doesn't seem to address the problem real well: 1. It's still the case that if we let the checkpoint complete, and then the system crashes before the file's actually been flushed to disk, you'd lose data. Putting a limit on the time we let this state persist doesn't seem to me to accomplish a lot, mainly because it's hardly credible that the system wouldn't have sync'd the data of its own accord before the timeout. (The place where we have this problem is *not* trying to write data out to the kernel; it's trying to be sure that the kernel has pushed previously-written data down to disk. I do not know Windows' exact policies for that, but I seriously doubt they let dirty disk buffers sit around indefinitely.) 2. It'd certainly be useful if we could alert the DBA that something is interfering with access to our disk files, but having the bgwriter log a message in the postmaster log that says ERROR rather than only LOG is probably not going to draw the average Windows user's attention :-( regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Version 8.2 for HP-UX
Right, but I assumed you checked with HP before coming here. I should have said for PG 8.2.1 on HP-UX, you will need to build from source. -- Brandon Aiken CS/IT Systems Engineer -Original Message- From: Al Balmer [mailto:[EMAIL PROTECTED] Sent: Friday, January 12, 2007 12:39 PM To: Brandon Aiken Subject: Re: [GENERAL] Version 8.2 for HP-UX On Fri, 12 Jan 2007 12:23:38 -0500, Brandon Aiken [EMAIL PROTECTED] wrote: AFAIK, PostgreSQL is only supplied in Win32 and Fedora/Redhat flavors. Debian, Gentoo, and FreeBSD maintain their own binary packages themselves. For HP-UX, you need to build from source. http://www.postgresql.org/docs/faqs.FAQ_HPUX.html Actually, HP supplies a depot as part of the Internet Express package. Unfortunately, it's back at 8.0, and I don't know when they'll update it. Ideally, HP will release an update, then we can tell our customers it's HP-supported. -- Brandon Aiken CS/IT Systems Engineer -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Al Balmer Sent: Friday, January 12, 2007 11:20 AM To: pgsql-general Subject: [GENERAL] Version 8.2 for HP-UX Does anyone know where to get postgreSQL 8.2 binaries or depot for HP-UX? We have a problem because of non-conforming backslash handling, and I understand that's been fixed in 8.2. -- Al Balmer Sun City, AZ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] ORDER BY col is NULL in UNION causes error?
Alvaro Herrera wrote: Bruce Momjian wrote: Here the column result is an expression, and you reference that. Updated wording: test= select * from test union select * from test order by x is null; ERROR: A UNION/INTERSECT/EXCEPT ORDER BY cannot use expressions or functions, only result column names This seems at odds with our message style guidelines. I'd suggest putting the actual error message in errmsg() and the rest as a errhint, maybe (errmsg(invalid UNION/INTERSECT/EXCEPT ORDER BY clause), errhint(You may specify only output result column names in the ORDER BY clauses.)) or something like that. Updated wording: test= SELECT * FROM test UNION SELECT * FROM test ORDER BY X IS NULL; ERROR: invalid UNION/INTERSECT/EXCEPT ORDER BY clause DETAIL: Only result column names can be used, not expressions or functions. HINT: Add the expression/function to every SELECT, or move the UNION into a FROM clause. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] FK Constraint on index not PK
Read the release notes. 7.4.8 and 7.4.11 require special considerations. By all means upgrade, but it's not quite seamless. -- Brandon Aiken CS/IT Systems Engineer -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Scott Marlowe Sent: Friday, January 12, 2007 12:59 PM To: Stéphane Schildknecht Cc: pgsql general Subject: Re: [GENERAL] FK Constraint on index not PK On Fri, 2007-01-12 at 10:50, Stéphane Schildknecht wrote: Dear community members, I'm having a quite strange behaviour while trying to drop some index. We have some tables with two indexes on a primary key. The first one was automatically created by the primary constraint. The second one was manually created on the same column. Don't know why, but I would now want to suppress it. The first index is : foo_pkey The second one : i_foo_pk The constraint on table bar is fk_bar_foo references foo(id) But, when trying to drop the second index I get the following message : NOTICE: constraint fk_bar_foo on table t_foo depends on index i_foo_pk The database server is 7.4.5 . Having dumped database and restored it on a 8.2 server, I could drop the second index without any problem. The fact is I could do that as I indded what to migrate all databases from 7.4 to 8.2. But I would prefer not to recreate every index before dropping the non necessary one. And duplicate indexes are surely unnecessary... I have read in some thread that these troubles are known and have been corrected in versions 7.4.5. But, droping them before migrating is an option I'd prefer to use. Simple. Backup your db just in case, then upgrade in place to the latest 7.4 (7.4.15 or so) then you should be able to drop the indexes in place. patch upgrades do NOT require dump / reload, so 7.4.5 - 7.4.15 should only require a brief period where you shut down the db while you install the new version. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Remove diacritical marks in SQL
Hello,
I would like to remove diacritical marks from a string in a SQL query.
I tried to convert a UTF8 string to ASCII but it doesn't work for me.
SELECT convert('ěščřžýáíé','UTF8','SQL_ASCII')
array(1) {
[convert]=
string(18) ěščřžýáíé
}
Thanks for any advice,
J.N.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] Remove diacritical marks in SQL
On Fri, Jan 12, 2007 at 10:16:22PM +0100, Ji?í N?mec wrote:
Hello,
I would like to remove diacritical marks from a string in a SQL query.
I tried to convert a UTF8 string to ASCII but it doesn't work for me.
SELECT convert('?ýáíé','UTF8','SQL_ASCII')
I don't think postgres has any stuff builtin for that, but other
languages (like perl) have modules to do this kind of thing. The method
is to decompose the string to normal form D, strip the diacritics, and
recompose what's left.
Hope this helps,
--
Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/
From each according to his ability. To each according to his ability to
litigate.
signature.asc
Description: Digital signature
Re: [GENERAL] Question - Query based on WHERE OR
On mið, 2007-01-10 at 17:38 -0800, Mike Poe wrote: I'm a rank newbie to Postgres am having a hard time getting my arms around this. I'm trying to construct a query to be run in a PHP script. I have an HTML form were someone can enter either a last name or a social security number then query the database based on what they entered. My query looks like this: SELECT foo, baz, bar FROM public.table WHERE lastname ~* '$lastname' OR ssn='$ssn' I need to leave the last name a wildcard in case someone enters a partial name, lower case / upper case, etc. note that you really should not be using values directly from user input for $lastname and $ssn without doing some sanity checks on them. consider for example the user submitting a string containing a quote character. most languages provide functions to make such input safe. gnari ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] I've compiled and installed -- what next?
Hi, I've compiled and installed v8.2.1 to use in conjunction with the Xythos document management system. My problem is getting from home to first base. I normally live in a MySql world and the first thing I do after installing the DBMS is set the root or superuser password. The PDF documentation talks about the superuser and database owner(s). How do I define the superuser and then secure that user with a password? Thanks, Rob -- Rob Tanner UNIX Services Manager Linfield College, McMinnville OR smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] I've compiled and installed -- what next?
Rob Tanner wrote: Hi, I've compiled and installed v8.2.1 to use in conjunction with the Xythos document management system. My problem is getting from home to first base. I normally live in a MySql world and the first thing I do after installing the DBMS is set the root or superuser password. The PDF documentation talks about the superuser and database owner(s). How do I define the superuser and then secure that user with a password? Hail fellow user from Oregon :) The default superuser for PostgreSQL is going to be the linux/unix user that you used the command initdb with. By default when you initdb in PostgreSQL (when you compile from source) it will allow any unix user to connect. Thus you can do this: psql -U postgres user template1 That will get you into your database (assuming it is running). Once there you can use CREATE ROLE and ALTER ROLE to set respective passwords. To get help use the following: \h CREATE ROLE \h ALTER ROLE Sincerely, Joshua D. Drake Thanks, Rob -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(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] Backup and restore UTF8 data
Hi, I am trying to backup an installation (8.0.3) and restore it to a new (8.2.1) installation. I am am running the following command the backup the data. pg_dumpall outfile Then on the new machine I am simply running: ENCODING=UTF8 psql -f outfile postgres I get the following error for some of the records. psql:outfile:151275: ERROR: invalid byte sequence for encoding UTF8: 0x92 Does anybody know what I can do to fix this? Benjamin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Backup and restore UTF8 data
On Fri, 2007-01-12 at 16:02 -0800, Benjamin Arai wrote: psql:outfile:151275: ERROR: invalid byte sequence for encoding UTF8: 0x92 Does anybody know what I can do to fix this? Here is a copy-paste from 8.1 release notes: == Some users are having problems loading UTF-8 data into 8.1.X. This is because previous versions allowed invalid UTF-8 byte sequences to be entered into the database, and this release properly accepts only valid UTF-8 sequences. One way to correct a dumpfile is to run the command iconv -c -f UTF-8 -t UTF-8 -o cleanfile.sql dumpfile.sql. The -c option removes invalid character sequences. A diff of the two files will show the sequences that are invalid. iconv reads the entire input file into memory so it might be necessary to use split to break up the dump into multiple smaller files for processing. == This is also valid for your problem. Regards, -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/ signature.asc Description: This is a digitally signed message part
Re: [GENERAL] FK Constraint on index not PK
Being quite familiar with both of those issues from the past, I can't imagine either one causing a problem with an update prior to dumping so he can then upgrade to 8.2. Seriously. Hungarian collation, plerl can no longer change locale and corrupt indexes, and a minor security update. And none of them need to be applied to do the pg_dump and then import to 8.2 Now, if he were gonna keep the 7.4 machine up and running, then I'd definitely recommend he look into the points made in the release notes for those versions. But all the OP seemed to be in search of was dropping those extra indexes before dumping / migrating to 8.2. On Fri, 2007-01-12 at 14:39, Brandon Aiken wrote: Read the release notes. 7.4.8 and 7.4.11 require special considerations. By all means upgrade, but it's not quite seamless. -- Brandon Aiken CS/IT Systems Engineer -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Scott Marlowe Sent: Friday, January 12, 2007 12:59 PM To: Stéphane Schildknecht Cc: pgsql general Subject: Re: [GENERAL] FK Constraint on index not PK On Fri, 2007-01-12 at 10:50, Stéphane Schildknecht wrote: Dear community members, I'm having a quite strange behaviour while trying to drop some index. We have some tables with two indexes on a primary key. The first one was automatically created by the primary constraint. The second one was manually created on the same column. Don't know why, but I would now want to suppress it. The first index is : foo_pkey The second one : i_foo_pk The constraint on table bar is fk_bar_foo references foo(id) But, when trying to drop the second index I get the following message : NOTICE: constraint fk_bar_foo on table t_foo depends on index i_foo_pk The database server is 7.4.5 . Having dumped database and restored it on a 8.2 server, I could drop the second index without any problem. The fact is I could do that as I indded what to migrate all databases from 7.4 to 8.2. But I would prefer not to recreate every index before dropping the non necessary one. And duplicate indexes are surely unnecessary... I have read in some thread that these troubles are known and have been corrected in versions 7.4.5. But, droping them before migrating is an option I'd prefer to use. Simple. Backup your db just in case, then upgrade in place to the latest 7.4 (7.4.15 or so) then you should be able to drop the indexes in place. patch upgrades do NOT require dump / reload, so 7.4.5 - 7.4.15 should only require a brief period where you shut down the db while you install the new version. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] I've compiled and installed -- what next?
Joshua D. Drake [EMAIL PROTECTED] writes: Rob Tanner wrote: ... I normally live in a MySql world and the first thing I do after installing the DBMS is set the root or superuser password. The PDF documentation talks about the superuser and database owner(s). How do I define the superuser and then secure that user with a password? The default superuser for PostgreSQL is going to be the linux/unix user that you used the command initdb with. By default when you initdb in PostgreSQL (when you compile from source) it will allow any unix user to connect. Thus you can do this: IIRC, initdb has some options to set the initial password and also to make the initial authentication mode not be trust. Good things to use if you are on a system with other users you don't trust. You'll want to at least skim the manual's discussion of client authentication pretty soon. Password-based auth is not necessarily the best choice. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] installing 8.2 on solaris 10?
I've searched google and did not find any tutorials on installing postgresql 8.2 on solaris 10. I would like to test performance of solaris vs. linux which I'm much more familiar with. the gentoo ebuild and fedora rpm do things like create startup scripts / users etc. Is there a prebuilt package available for solaris 10 somewhere or should I just follow the instructions here: http://www.postgresql.org/docs/8.2/interactive/install-procedure.html ? thanks, gene ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] installing 8.2 on solaris 10?
Gene wrote: I've searched google and did not find any tutorials on installing postgresql 8.2 on solaris 10. I would like to test performance of solaris vs. linux which I'm much more familiar with. the gentoo ebuild and fedora rpm do things like create startup scripts / users etc. Is there a prebuilt package available for solaris 10 somewhere or should I just follow the instructions here: http://www.postgresql.org/docs/8.2/interactive/install-procedure.html ? I have only seen up to 8.1.4. I would follow the source procedures. thanks, gene ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] installing 8.2 on solaris 10?
On 1/12/07, Gene [EMAIL PROTECTED] wrote: I've searched google and did not find any tutorials on installing postgresql 8.2 on solaris 10. I would like to test performance of solaris vs. linux which I'm much more familiar with. the gentoo ebuild and fedora rpm do things like create startup scripts / users etc. Is there a prebuilt package available for solaris 10 somewhere or should I just follow the instructions here: http://www.postgresql.org/docs/8.2/interactive/install-procedure.html ? i think sun give postgres support to all his solaris customers... maybe they can help you? if you want to do it yourself then you should get the sources and compile... -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] installing 8.2 on solaris 10?
thanks for the advice, i would buy the support from sun but I don't think they would support version 8.2 of postgresql. i've been able to find some sample smf files for postgresql on solaris which should help. On 1/13/07, Jaime Casanova [EMAIL PROTECTED] wrote: On 1/12/07, Gene [EMAIL PROTECTED] wrote: I've searched google and did not find any tutorials on installing postgresql 8.2 on solaris 10. I would like to test performance of solaris vs. linux which I'm much more familiar with. the gentoo ebuild and fedora rpm do things like create startup scripts / users etc. Is there a prebuilt package available for solaris 10 somewhere or should I just follow the instructions here: http://www.postgresql.org/docs/8.2/interactive/install-procedure.html ? i think sun give postgres support to all his solaris customers... maybe they can help you? if you want to do it yourself then you should get the sources and compile... -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook -- Gene Hart cell: 443-604-2679 ---(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] PostgreSQL Connections?
Hi Shoaib Following is the output for ps auxwww | grep ^postgres IP address of my server is 172.18.5.155 postgres 9635 0.0 0.4 44436 2420 pts/1S Jan11 0:04 /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data postgres 9636 0.0 0.2 7856 1368 ?Ss Jan11 0:00 postgres: logger process postgres 9638 0.0 3.1 44564 16376 ? Ss Jan11 0:00 postgres: writer process postgres 9639 0.0 0.4 9340 2104 ?Ss Jan11 1:15 postgres: stats collector process postgres 12846 0.0 0.8 45328 4164 ?Ss Jan12 0:00 postgres: qsweb qsweb06jan07 172.18.4.61(4272) idle postgres 18580 0.0 1.4 48176 7276 ?Ss Jan12 0:00 postgres: qsweb qsweb12jan2007 172.18.4.61(1320) idle postgres 21614 0.0 0.6 45204 3584 ?Ss Jan12 0:00 postgres: qsweb qsweb12jan2007 172.18.5.197(3888) idle postgres 21615 0.0 0.7 45204 3728 ?Ss Jan12 0:00 postgres: qsweb qsweb12jan2007 172.18.5.197(3890) idle postgres 21616 0.0 0.7 45160 3784 ?Ss Jan12 0:00 postgres: qsweb qsweb12jan2007 172.18.5.197(3891) idle in transaction postgres 21634 0.0 0.7 45204 3968 ?Ss Jan12 0:00 postgres: qsweb qsweb12jan2007 172.18.5.197(3892) idle in transaction postgres 21635 0.0 0.7 45160 3784 ?Ss Jan12 0:00 postgres: qsweb qsweb12jan2007 172.18.5.197(3893) idle in transaction postgres 21637 0.0 0.7 45204 3968 ?Ss Jan12 0:00 postgres: qsweb qsweb12jan2007 172.18.5.197(3894) idle in transaction postgres 21648 0.0 0.7 45160 3784 ?Ss Jan12 0:00 postgres: qsweb qsweb12jan2007 172.18.5.197(3895) idle in transaction postgres 21652 0.0 0.7 45160 4076 ?Ss Jan12 0:00 postgres: qsweb qsweb12jan2007 172.18.5.197(3896) idle in transaction postgres 21660 0.0 0.9 45544 4812 ?Ss Jan12 0:00 postgres: qsweb qsweb12jan2007 172.18.5.197(3897) idle in transaction postgres 21727 0.0 0.7 45164 4076 ?Ss Jan12 0:00 postgres: qsweb qsweb12jan2007 172.18.5.197(3925) idle in transaction postgres 21728 0.0 0.7 45204 3968 ?Ss Jan12 0:00 postgres: qsweb qsweb12jan2007 172.18.5.197(3926) idle in transaction postgres 21752 0.0 0.7 45160 4076 ?Ss Jan12 0:00 postgres: qsweb qsweb12jan2007 172.18.5.197(3943) idle in transaction postgres 23335 0.0 0.9 45336 4800 ?Ss 11:38 0:00 postgres: qsweb postgres 172.18.4.16(1313) idle postgres 23336 0.0 2.2 48192 11812 ? Ss 11:38 0:00 postgres: qsweb qsweb12jan2007 172.18.4.16(1314) idle postgres 23665 0.0 0.8 45204 4260 ?Ss 12:13 0:00 postgres: qsweb qsweb12jan2007 172.18.5.197(4799) idle postgres 23666 0.0 0.8 45160 4472 ?Ss 12:13 0:00 postgres: qsweb qsweb12jan2007 172.18.5.197(4800) idle in transaction postgres 23667 0.0 0.8 45204 4424 ?Ss 12:13 0:00 postgres: qsweb qsweb12jan2007 172.18.5.197(4803) idle postgres 23668 0.0 0.8 45164 4476 ?Ss 12:13 0:00 postgres: qsweb qsweb12jan2007 172.18.5.197(4804) idle in transaction postgres 23753 0.0 1.0 45336 5216 ?Ss 12:18 0:00 postgres: qsweb postgres 172.18.4.58(1140) idle postgres 23754 0.0 2.7 48192 14308 ? Ss 12:18 0:00 postgres: qsweb qsweb06jan07 172.18.4.58(1141) idle postgres 23755 0.0 0.8 45164 4400 ?Ss 12:18 0:00 postgres: qsweb qsweb06jan07 172.18.4.58(1143) idle postgres 23756 0.0 1.0 45240 5256 ?Ss 12:18 0:00 postgres: qsweb qsweb12jan2007 172.18.4.58(1144) idle postgres 23761 0.0 1.0 45336 5216 ?Ss 12:20 0:00 postgres: qsweb postgres 172.18.4.135(1214) idle postgres 23762 0.0 1.7 46596 8840 ?Ss 12:20 0:00 postgres: qsweb importdata 172.18.4.135(1215) idle postgres 23868 0.0 0.8 45204 4260 ?Ss 12:30 0:00 postgres: qsweb qsweb12jan2007 172.18.5.155(37415) idle postgres 23869 0.0 0.8 45204 4260 ?Ss 12:30 0:00 postgres: qsweb qsweb12jan2007 172.18.5.155(37416) idle postgres 23870 0.0 0.8 45164 4476 ?Ss 12:30 0:00 postgres: qsweb qsweb12jan2007 172.18.5.155(37417) idle in transaction postgres 23871 0.0 0.8 45204 4424 ?Ss 12:30 0:00 postgres: qsweb qsweb12jan2007 172.18.5.155(37418) idle postgres 23872 0.0 0.8 45164 4476 ?Ss 12:30 0:00 postgres: qsweb qsweb12jan2007 172.18.5.155(37419) idle in transaction postgres 23874 0.0 0.8 45164 4476 ?Ss 12:30 0:00 postgres: qsweb qsweb12jan2007 172.18.5.155(37420) idle in transaction postgres 23875 0.0 0.9 45204 4736 ?Ss 12:30 0:00 postgres: qsweb qsweb12jan2007 172.18.5.155(37421) idle in transaction postgres 23877 0.0 0.8 45164 4476 ?Ss 12:30 0:00 postgres: qsweb qsweb12jan2007 172.18.5.155(37422) idle in transaction postgres 23878 0.0 0.8 45204 4424 ?Ss 12:30 0:00 postgres: qsweb qsweb12jan2007 172.18.5.155(37423) idle postgres 23879 0.0 0.8 45164 4476 ?Ss 12:30 0:00 postgres: qsweb qsweb12jan2007 172.18.5.155(37424) idle in transaction
Re: [GENERAL] FK Constraint on index not PK
Scott Marlowe a écrit : Being quite familiar with both of those issues from the past, I can't imagine either one causing a problem with an update prior to dumping so he can then upgrade to 8.2. Seriously. Hungarian collation, plerl can no longer change locale and corrupt indexes, and a minor security update. And none of them need to be applied to do the pg_dump and then import to 8.2 Now, if he were gonna keep the 7.4 machine up and running, then I'd definitely recommend he look into the points made in the release notes for those versions. But all the OP seemed to be in search of was dropping those extra indexes before dumping / migrating to 8.2. My goal is to migrate to 8.2.1. definitely. But as you said it, I do not want to recreate unwanted index when migrating. I want to drop them BEFORE. But, I can't just do a drop index command. It fails. That's why I asked for an advice to drop them or not recreate them. I would really prefer not to parse the all dump (some GB). Thx SAS ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] FK Constraint on index not PK
Joshua D. Drake a écrit : On Fri, 2007-01-12 at 17:50 +0100, Stéphane Schildknecht wrote: Dear community members, I'm having a quite strange behaviour while trying to drop some index. We have some tables with two indexes on a primary key. The first one was automatically created by the primary constraint. The second one was manually created on the same column. Don't know why, but I would now want to suppress it. Drop the second index. It is redundant. I know it. But I can't. SAS ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [GENERAL] Checkpoint request failed on version 8.2.1.
I find it very unlikely that you would during normal operations end up in a situation where you would first have permissions to create files in a directory, and then lose them. What could be is that you have a directory where you never had permissions to create the file in the first place. Any chance to differentiate between these? The cases we're concerned about involve access to an existing file, not attempts to create a new one, so I'm not clear what your point is. I am wondering if we can delete the file by opening it with FILE_FLAG_DELETE_ON_CLOSE, and immediately close it again. The semantics should be clear if we let the OS delete the file after the last handle on it is closed ? Until all handles are closed another process can still open it with FILE_SHARE_DELETE (according to docs), but not without the flag. This seems to be what we want. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/fileio/ fs/createfile.asp If this fails (see the loop in dirmod.c) we could try to move it to the recycle bin with SHFileOperation with FO_DELETE. It seems the win unlink is not implemented correctly and we need to replace it. I don't feel easy with the ignore EACCES idea. Should I try to supply a patch along this line ? Andreas ---(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: [HACKERS] [GENERAL] Checkpoint request failed on version 8.2.1.
On Fri, Jan 12, 2007 at 10:49:53AM +0100, Zeugswetter Andreas ADI SD wrote: I find it very unlikely that you would during normal operations end up in a situation where you would first have permissions to create files in a directory, and then lose them. What could be is that you have a directory where you never had permissions to create the file in the first place. Any chance to differentiate between these? The cases we're concerned about involve access to an existing file, not attempts to create a new one, so I'm not clear what your point is. I am wondering if we can delete the file by opening it with FILE_FLAG_DELETE_ON_CLOSE, and immediately close it again. The semantics should be clear if we let the OS delete the file after the last handle on it is closed ? Until all handles are closed another process can still open it with FILE_SHARE_DELETE (according to docs), but not without the flag. This seems to be what we want. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/fileio/ fs/createfile.asp If this fails (see the loop in dirmod.c) we could try to move it to the recycle bin with SHFileOperation with FO_DELETE. It seems the win unlink is not implemented correctly and we need to replace it. I don't feel easy with the ignore EACCES idea. Should I try to supply a patch along this line ? Doesn't sound unreasonable, so yes, let's give it a try at least. //Magnus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [GENERAL] Checkpoint request failed on version 8.2.1.
On Thu, Jan 11, 2007 at 10:39:47PM -0500, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: On Thu, Jan 11, 2007 at 03:14:37PM -0500, Tom Lane wrote: ... And anyway there should never *be* a real permissions problem; if there is then the user's been poking under the hood sufficient to void the warranty anyway ;-) Or some other helpful process such as a virus scanner has been poking under the hood for you... :( One point worth making is that I'm not really convinced anymore that we have proof that antivirus code has been creating any such problems. We do. I have positive proof of this being caused by AV software. I don't know that it has been the problem in *all cases*, certainly, but I've had kernel stacktraces pointing into AV filter drivers more than once. We have several anecdotal cases where someone reported erratic permission denied problems on Windows, and we suggested getting rid of any AV code, and it seemed to fix their problem --- but how long did they test? This problem is inherently very timing-sensitive, and so the fact that you don't see it for a little while is hardly proof that it's gone. See the report that started this thread for examples of apparent correlations that are really quite spurious, like whether the test case is being driven locally or not. It could easy be that every report we've heard really traces to the not-yet-deleted-file problem. No, not all of them. But certainly a fair share of them can have been. So basically what we'd have is that if you manually remove permissions on a database file or directory you'd be risking data loss; but heck, if you manually move, rename, delete such a file you're risking (guaranteeing) data loss. That was the point I was trying tom ake erarlier :-) //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] Checkpoint request failed on version 8.2.1.
Zeugswetter Andreas ADI SD [EMAIL PROTECTED] writes: It seems the win unlink is not implemented correctly and we need to replace it. Easier said than done ... regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [GENERAL] Checkpoint request failed on version 8.2.1.
Magnus Hagander [EMAIL PROTECTED] writes: On Thu, Jan 11, 2007 at 10:39:47PM -0500, Tom Lane wrote: One point worth making is that I'm not really convinced anymore that we have proof that antivirus code has been creating any such problems. We do. I have positive proof of this being caused by AV software. I don't know that it has been the problem in *all cases*, certainly, but I've had kernel stacktraces pointing into AV filter drivers more than once. No, I didn't claim that Windows AV software is bug-free ;-). What I said was that I'm not certain it's related to the permission denied reports, as opposed to other problems. Or are your stack traces specifically for permission denied failures? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] Checkpoint request failed on version 8.2.1.
On Fri, Jan 12, 2007 at 09:47:55AM -0500, Tom Lane wrote: Magnus Hagander [EMAIL PROTECTED] writes: On Thu, Jan 11, 2007 at 10:39:47PM -0500, Tom Lane wrote: One point worth making is that I'm not really convinced anymore that we have proof that antivirus code has been creating any such problems. We do. I have positive proof of this being caused by AV software. I don't know that it has been the problem in *all cases*, certainly, but I've had kernel stacktraces pointing into AV filter drivers more than once. No, I didn't claim that Windows AV software is bug-free ;-). What I said was that I'm not certain it's related to the permission denied reports, as opposed to other problems. Or are your stack traces specifically for permission denied failures? I have at least two cases specifically for the permission denied failures in postgres. //Magnus ---(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: [HACKERS] [GENERAL] Checkpoint request failed on version 8.2.1.
Magnus Hagander [EMAIL PROTECTED] writes: On Fri, Jan 12, 2007 at 09:47:55AM -0500, Tom Lane wrote: No, I didn't claim that Windows AV software is bug-free ;-). What I said was that I'm not certain it's related to the permission denied reports, as opposed to other problems. Or are your stack traces specifically for permission denied failures? I have at least two cases specifically for the permission denied failures in postgres. Actually, it could still be the same problem, with the AV software only involved to the extent that it's trying to scan files for viruses. That could result in the AV code holding a table file open for a little bit (or not such a little bit, if it's a big table) after it's nominally been deleted, and that's exactly the situation we see checkpoints failing in. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [GENERAL] Checkpoint request failed on version 8.2.1.
Tom Lane wrote: Magnus Hagander [EMAIL PROTECTED] writes: On Fri, Jan 12, 2007 at 09:47:55AM -0500, Tom Lane wrote: No, I didn't claim that Windows AV software is bug-free ;-). What I said was that I'm not certain it's related to the permission denied reports, as opposed to other problems. Or are your stack traces specifically for permission denied failures? I have at least two cases specifically for the permission denied failures in postgres. Actually, it could still be the same problem, with the AV software only involved to the extent that it's trying to scan files for viruses. That could result in the AV code holding a table file open for a little bit (or not such a little bit, if it's a big table) after it's nominally been deleted, and that's exactly the situation we see checkpoints failing in. Partially the same, but I've seen AV software keeping it open for hours... Basically until reboot. //Magnus ---(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: [HACKERS] [GENERAL] Checkpoint request failed on version 8.2.1.
Magnus Hagander [EMAIL PROTECTED] writes: Tom Lane wrote: Actually, it could still be the same problem, with the AV software only involved to the extent that it's trying to scan files for viruses. Partially the same, but I've seen AV software keeping it open for hours... Basically until reboot. Well, the bug report that just went by proves there's another problem: : select version(); : : PostgreSQL 8.2.1 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) : 3.4.2 (mingw-special) : : pg_log: : : 2007-01-12 17:23:16 PANIC: could not open control file : global/pg_control: Permission denied pg_control is certainly not ever deleted or renamed, and in fact I believe there's an LWLock enforcing that only one PG process at a time is even touching it. So we need another theory to explain this one :-( ... anyone have a better one than Windows is a piece of junk? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [HACKERS] [GENERAL] Checkpoint request failed on version 8.2.1.
Tom Lane wrote: Magnus Hagander [EMAIL PROTECTED] writes: Tom Lane wrote: Actually, it could still be the same problem, with the AV software only involved to the extent that it's trying to scan files for viruses. Partially the same, but I've seen AV software keeping it open for hours... Basically until reboot. Well, the bug report that just went by proves there's another problem: : select version(); : : PostgreSQL 8.2.1 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) : 3.4.2 (mingw-special) : : pg_log: : : 2007-01-12 17:23:16 PANIC: could not open control file : global/pg_control: Permission denied pg_control is certainly not ever deleted or renamed, and in fact I believe there's an LWLock enforcing that only one PG process at a time is even touching it. So we need another theory to explain this one :-( ... anyone have a better one than Windows is a piece of junk? Right. What we need is a list of which processes have handles open to the file, which can be dumped using Process Explorer (there are other sysinternals tools to do it as well, but PE is probably the easiest)- //Magnus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [GENERAL] Checkpoint request failed on version 8.2.1.
Magnus Hagander [EMAIL PROTECTED] writes: Tom Lane wrote: pg_control is certainly not ever deleted or renamed, and in fact I believe there's an LWLock enforcing that only one PG process at a time is even touching it. So we need another theory to explain this one :-( Right. What we need is a list of which processes have handles open to the file, which can be dumped using Process Explorer (there are other sysinternals tools to do it as well, but PE is probably the easiest)- Hmm, are you just assuming that the underlying error is ERROR_SHARING_VIOLATION? One of the things that's bothered me all along is that there are a dozen different Windows error codes that we map to EACCES ... perhaps it's time to think about disambiguating that a bit better? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Autovacuum Improvements
On Fri, 2006-12-29 at 20:25 -0300, Alvaro Herrera wrote: Christopher Browne wrote: Seems to me that you could get ~80% of the way by having the simplest 2 queue implementation, where tables with size some threshold get thrown at the little table queue, and tables above that size go to the big table queue. That should keep any small tables from getting vacuum-starved. Hmm, would it make sense to keep 2 queues, one that goes through the tables in smaller-to-larger order, and the other one in the reverse direction? I am currently writing a design on how to create vacuum queues but I'm thinking that maybe it's getting too complex to handle, and a simple idea like yours is enough (given sufficient polish). Sounds good to me. My colleague Pavan has just suggested multiple autovacuums and then prototyped something almost as a side issue while trying to solve other problems. I'll show him this entry, maybe he saw it already? I wasn't following this discussion until now. The 2 queue implementation seemed to me to be the most straightforward implementation, mirroring Chris' suggestion. A few aspects that haven't been mentioned are: - if you have more than one VACUUM running, we'll need to watch memory management. Having different queues based upon table size is a good way of doing that, since the smaller queues have a naturally limited memory consumption. - with different size-based queues, the larger VACUUMs can be delayed so they take much longer, while the small tables can go straight through Some feedback from initial testing is that 2 queues probably isn't enough. If you have tables with 100s of blocks and tables with millions of blocks, the tables in the mid-range still lose out. So I'm thinking that a design with 3 queues based upon size ranges, plus the idea that when a queue is empty it will scan for tables slightly above/below its normal range. That way we wouldn't need to specify the cut-offs with a difficult to understand new set of GUC parameters, define them exactly and then have them be wrong when databases grow. The largest queue would be the one reserved for Xid wraparound avoidance. No table would be eligible for more than one queue at a time, though it might change between queues as it grows. Alvaro, have you completed your design? Pavan, what are your thoughts? -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Autovacuum Improvements
Simon Riggs wrote: Some feedback from initial testing is that 2 queues probably isn't enough. If you have tables with 100s of blocks and tables with millions of blocks, the tables in the mid-range still lose out. So I'm thinking that a design with 3 queues based upon size ranges, plus the idea that when a queue is empty it will scan for tables slightly above/below its normal range. Yeah, eventually it occurred to me the fact that as soon as you have 2 queues, you may as well want to have 3 or in fact any number. Which in my proposal is very easily achieved. Alvaro, have you completed your design? No, I haven't, and the part that's missing is precisely the queues stuff. I think I've been delaying posting it for too long, and that is harmful because it makes other people waste time thinking on issues that I may already have resolved, and delays the bashing that yet others will surely inflict on my proposal, which is never a good thing ;-) So maybe I'll put in a stub about the queues stuff and see how people like the whole thing. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Autovacuum Improvements
On Fri, 2007-01-12 at 19:33 -0300, Alvaro Herrera wrote: Alvaro, have you completed your design? No, I haven't, and the part that's missing is precisely the queues stuff. I think I've been delaying posting it for too long, and that is harmful because it makes other people waste time thinking on issues that I may already have resolved, and delays the bashing that yet others will surely inflict on my proposal, which is never a good thing ;-) So maybe I'll put in a stub about the queues stuff and see how people like the whole thing. I've not read a word spoken against the general idea, so I think we should pursue this actively for 8.3. It should be straightforward to harvest the good ideas, though there will definitely be many. Perhaps we should focus on the issues that might result, so that we address those before we spend time on the details of the user interface. Can we deadlock or hang from running multiple autovacuums? -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
