Re: [GENERAL] Indexes on Expressions -- Parentheses
[EMAIL PROTECTED] On Tue, 28 Sep 2004, Thomas F.O'Connell wrote: From 11.5 in the docs: The syntax of the CREATE INDEX command normally requires writing parentheses around index expressions, as shown in the second example. The parentheses may be omitted when the expression is just a function call, as in the first example. But when I try this: db=# CREATE INDEX expression_idx on some_table( extract( year from some_column ) ); Extract(year from some_column) is not really just a function call it's an expression that looks similar to a function call because that's how SQL defined it. ---(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 OID or not to OID
On Wed, 2004-09-29 at 00:23, Doug McNaught wrote: Nothing in PG depends on user tables having an OID column. They used to be a way to get a primary key before SERIAL came along, but they are now deprecated for user tables. WITHOUT OIDS will be the default in a future release. Indeed, that will be the default in 8.1, if we stick to the previous discussions on -hackers. 8.0 introduces a GUC variable default_with_oids that controls whether OIDs are added to tables by default. In 8.0 this will be true by default (so 8.0 will behave exactly the same as earlier releases), but I'll be pushing for it to be set to false for 8.1. -Neil ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] error: unicode characters greater than or equal to 0x10000
good day everyone!
i am using postgresql-8.0beta2-dev3 windows port, with postgis-0.9
add-on. anyway, while trying to insert a table using shp2pgsql, i get
this error:
psql:temp.sql:38: ERROR: Unicode characters greater than or equal to
0x1 are not supported
this is line 38 of temp.sql:
INSERT INTO esiadminbounds
(gid,area,town,barangay,dens,pop90,popd90,pop95,popd95,pop97,popd97,the_geom)
VALUES
('37','1.424549','Bauan','Pitogo','0.00','0','4.03','0','0.00','0','0.',GeometryFromText('MULTIPOLYGON(((120.955684569578
13.7725006999301 ,120.9554735709 13.7724626988676 ,120.955403570202
13.7724506990452 ,120.955229571123 13.7722566997561 ,120.95522957124
13.7722516993503 ,120.955229570082 13.7722466989339 ,120.955070571067
13.7722456995021 ,120.954931571036 13.7722436992655 ,120.954931571145
13.7722496995042 ,120.954897570853 13.7722496999685 ,120.954776571639
13.7722426992188 ,120.954677571648 13.7722366990558 ,120.954480571351
13.772390698913 ,120.954469571209 13.7723906997389 ,120.954469571997
13.7723996994764 ,120.954418571133 13.7724396989397 ,120.954417571232
13.7724396993554 ,120.954417571463 13.7724406991889 ,120.954378572154
13.7724706990999 ,120.95426457253 13.7723926996639 ,120.954264571623
13.7723886990838 ,120.954258572245 13.7723886990816 ,120.954236572153
13.7723726996713 ,120.954121572059 13.7724486995595 ,120.954069571652
13.7724736993948 ,120.954011571976 13.7725216990704 ,120.95388257267
13.7726066993588 ,120.953660572868 13.7726926990537 ,120.953463573
13.7727506992206 ,120.9533925736 13.7726866993123 ,120.953150573447
13.7724657000595 ,120.951933574673 13.77245579 ,120.95057757599
13.7724446997878 ,120.9498125778 13.772822700065 ,120.949654577942
13.7728726991687 ,120.949389577744 13.7731016997272 ,120.949284578733
13.7731526999402 ,120.949072578313 13.7733046998845 ,120.948914579293
13.773354699701 ,120.948548579143 13.773526699911 ,120.94790157952
13.7737966994859 ,120.94790157952 13.7737966994859 ,120.947780579619
13.7738826991356 ,120.947781579913 13.7738846996532 ,120.947685579841
13.7739317000246 ,120.947491580821 13.7740436995634 ,120.947881580104
13.774638699372 ,120.947875580117 13.7752566999521 ,120.947691580716
13.7752806995878 ,120.947028581302 13.7758416993301 ,120.947027580884
13.7759436995362 ,120.946894581169 13.7760456991315 ,120.946156582435
13.7761686995377 ,120.9459695827 13.7765017001618 ,120.945968582131
13.7765786996464 ,120.946045582334 13.7768107000939 ,120.946124582967
13.7768376991713 ,120.946307582683 13.7769417002886 ,120.946251582585
13.7772756997884 ,120.945954583113 13.7782256996654 ,120.945768582824
13.7783786995522 ,120.945081583341 13.7786307001322 ,120.944682584804
13.7790646996173 ,120.944339584713 13.7792416999451 ,120.944046584884
13.7795226992718 ,120.94365058579 13.7796737001305 ,120.943646585685
13.7801367003654 ,120.942773586314 13.7806187000421 ,120.942145588119
13.7807477000423 ,120.94214758713 13.7807577000478 ,120.941949587394
13.7808966994756 ,120.941898587357 13.7808967003478 ,120.941898587357
13.7808967003478 ,120.941898588216 13.7809166993276 ,120.941882588124
13.7809167000871 ,120.941665588478 13.7811476998331 ,120.941711588532
13.7812886996558 ,120.941543588505 13.7814507000611 ,120.941326589029
13.7815886995907 ,120.941324588756 13.7818456998425 ,120.941222588855
13.7820557003543 ,120.941221588706 13.7820927004117 ,120.941221588706
13.7820927004117 ,120.941221589376 13.7821427003189 ,120.941375589047
13.782143700156 ,120.941373588147 13.7823437004827 ,120.941372588722
13.7824537001356 ,120.941366588069 13.782546699571 ,120.941532588916
13.7826416996957 ,120.942150587739 13.7830436993852 ,120.942317587143
13.7830216993328 ,120.942844587087 13.7829096996479 ,120.943083586784
13.78288770041 ,120.943081586605 13.7830987002521 ,120.943151586748
13.7833087003247 ,120.943076586407 13.7837057002219 ,120.942361587216
13.783442700435 ,120.942284587 13.7840957004391 ,120.94285558738
13.7843576993204 ,120.943071585955 13.7843127001598 ,120.943521586262
13.7847366993097 ,120.943521586245 13.7847146994284 ,120.943810585371
13.7847166997569 ,120.943810585371 13.7847166997569 ,120.943903585345
13.7847176992901 ,120.944144584882 13.7846126993342 ,120.944337584852
13.7844027002044 ,120.944554584503 13.7842646993978 ,120.944866584234
13.7841736999533 ,120.945274584138 13.7841296999024 ,120.945684583269
13.7838056997859 ,120.945901582998 13.7837366993142 ,120.946166582781
13.7835746992964 ,120.947193581247 13.7838646992182 ,120.947139581061
13.7846376991343 ,120.947401581608 13.784756700147 ,120.947952579977
13.7848316993526 ,120.948478580273 13.7849526990372 ,120.949122578952
13.7852156999324 ,120.949362578372 13.7852176990199 ,120.949746577914
13.785174699536 ,120.950011577631 13.7849886993708 ,120.950347577
13.7849216992745 ,120.950632576422 13.7852286994772 ,120.950726576421
13.785463699583 ,120.951227576775 13.7856786999332 ,120.951200575881
13.7859826996745 ,120.951866575863 13.7865966990361 ,120.951999575304
13.7865066990118
[GENERAL] About PostgreSQL's limit on arithmetic operations
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, We were performing some tests on PostgreSQL and found that it fails on the following query: SELECT 512*18014398509481984 AS result; and the result is: result - -- -9223372036854775808 It should be 9223372036854775808... This is PostgreSQL 7.4.5 on Fedora Core 2. We tested the same query on some other database servers: Oracle 9 ias r2 : 9,22337203685478E18 Oracle 9i WinXP, 32 bit : 9223372036854775808 Oracle 9.2.0.3 on Redhat 9 : 9223372036854775808 Oracle 9.2.0.3 on Redhat Advanced Server 2.1 : 9223372036854775808 MS-SQL Server 2000 : 9223372036854775808 MySQL on Sun Solaris 6 : -9223372036854775808 MySQL on Red Hat Enterprise Linux 3.0 : -9223372036854775808 It seems that MySQL and PostgreSQL fails on this query. Is this a bug, or a lack of something? ... or is there a limit? Regards, - -- Devrim GUNDUZ devrim~gunduz.orgdevrim.gunduz~linux.org.tr http://www.tdmsoft.com http://www.gunduz.org -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQFBWmc4tl86P3SPfQ4RAliCAJ9JJA3111mxrcBnnWptI8cTQzdpoQCgsBQV HMKeNGowkd35BhMwW6kWwbs= =BWS3 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] About PostgreSQL's limit on arithmetic operations
On Wednesday 29 Sep 2004 1:11 pm, Devrim GUNDUZ wrote: Hi, We were performing some tests on PostgreSQL and found that it fails on the following query: SELECT 512*18014398509481984 AS result; and the result is: result -- -9223372036854775808 It should be 9223372036854775808... This is PostgreSQL 7.4.5 on Fedora Core 2. [EMAIL PROTECTED]:~$ psql template1 Welcome to psql 8.0.0beta2, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit template1=# SELECT 512*18014398509481984 AS result; result -- -9223372036854775808 (1 row) template1=# SELECT 512*18014398509481984::bigint AS result; result -- -9223372036854775808 (1 row) template1=# SELECT 512*18014398509481984::numeric(20) AS result; result - 9223372036854775808 (1 row) template1=# select version(); version - PostgreSQL 8.0.0beta2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.4 (1 row) Shridhar ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] sql script confusion
I am in the process of testing PostGreSQL for our sales and product database and I am a little confused about SQL scripting and various available languages. (My background is mostly MSSQL Server). I am using the PGAdmin tool and I would like to write a script to update all sequences to the current max value in the table. Do I need to use a specific language in a function to do this or does it work as native SQL, as it would in T-SQL? What I would like to do is something like - (pseudo code) declare cursor for select relname from pg_statio_user_sequences open cursor fetch next into var_relname while not cursor.eof set var_tblname=substring(var_relname, 0 until _) set var_fieldname=substring(var_relname,first _ until 2nd _) select var_maxID=max(var_fieldname) from var_tblname ALTER SEQUENCE var_relname RESTART WITH var_maxID+1; fetch next into var_relname end loop Can dynamic statements be written in raw sql or do they need to be encompassed in a language? Do all language scripts have to be functions or can I do something like: Start Language Processing Here ...Code End Language Processing Thank You Sim Zacks IT Manager CompuLab 04-829-0145 - Office 04-832-5251 - Fax ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Composite Type Argument Construction to a Function
create type my_type (my_test text, my_int integer); create function my_function(my_type) returns timestamp as 'begin return (current_timestamp); end; ' language 'plpgsql'; In 7.3.5, we are trying to get around the 32 argument limit of the functions WITHOUT re-compiling with a greater func_max_args (due to operational reasons). My question is - HOW do I construct the argument to my_function(my_type) so that the function recognises that I am passing into it composite data? NB: The example function is just junque to test passing a composite type to a function. TIA ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Multiple Rules :: Postgres Is confused !!
Hi all i am running PostgreSQL 7.3.2, i have aVIEW for which i implemented multipleRULES on UPDATE. The weird think is that the Update Query corresponding to one of the rules is updating MULTIPLE ROWS even though it should only update one ROW !! THE WEIRDEST is that when i remove 2 of the update Rules on the VIEW The Update Query Works FINE WHY IS THAT HAPPENNING ?? I can provide more details if anyone is ready to help ...
Re: [GENERAL] Syntax Issue in Trigger Function??
Cheers for that! We did catch it eventually. My colleague was using pgAdminIII and was apparently typing: v_ref := ''/''; and pgAdminIII appears to have been helping out by escaping the single quotes. On Tue, 28 Sep 2004 22:46:31 +0300, Andre Maasikas [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] wrote: In Postgres 7.3.5 - When we try to insert a new record into our parties.party table which is then meant to fire off a trigger to update a column in the table with some de-normalised information, we get the following error: ERROR: FLOATING POINT EXCEPTION! The last floating point operation either exceeded the legal ranges or was a divide by zero. Can someone help spot our syntax erorr, please? This looks to me like a candidate: v_ref := \'\'/\'\'; Without escaping it looks like v_ref := ''/''; dividing 2 empty strings, and indeed gives division by zero in psql. What dividing 2 strings is actually supposed to mean is not evident form the docs in the first glance. v_ref := \'\'/\'\' || v_parent_party_id || v_ref; This one too. Andre ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] About PostgreSQL's limit on arithmetic operations
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Wed, 29 Sep 2004, Shridhar Daithankar wrote: SELECT 512*18014398509481984 AS result; and the result is: result -- -9223372036854775808 It should be 9223372036854775808... This is PostgreSQL 7.4.5 on Fedora Core 2. [EMAIL PROTECTED]:~$ psql template1 snip template1=# SELECT 512*18014398509481984::numeric(20) AS result; result - 9223372036854775808 (1 row) Ok, I got the same result in 7.4.5... But... Why do we have to cast it into numeric? The results from other databases shows that they can perform it without casting... Anyway, thanks for the tip. Regards, - -- Devrim GUNDUZ devrim~gunduz.orgdevrim.gunduz~linux.org.tr http://www.tdmsoft.com http://www.gunduz.org -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQFBWniTtl86P3SPfQ4RAt9/AJ0dG8bXaDVgqAvtgIDiEAX7MwU4YACfeR+l c4nhYwft3NvJWhOdHJ90Lm8= =4uVc -END PGP SIGNATURE- ---(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] sql script confusion
Sim Zacks wrote: Do I need to use a specific language in a function to do this or does it work as native SQL, as it would in T-SQL? You need to use a specific language. What I would like to do is something like - (pseudo code) declare cursor for select relname from pg_statio_user_sequences open cursor fetch next into var_relname while not cursor.eof set var_tblname=substring(var_relname, 0 until _) set var_fieldname=substring(var_relname,first _ until 2nd _) select var_maxID=max(var_fieldname) from var_tblname ALTER SEQUENCE var_relname RESTART WITH var_maxID+1; fetch next into var_relname end loop For this plpgsql might be a good choice. Think sql with a few loop/control structures and variables. Can dynamic statements be written in raw sql or do they need to be encompassed in a language? Do all language scripts have to be functions or can I do something like: Start Language Processing Here ...Code End Language Processing You need to create a function and then call it. You can create dynamic SQL as a string and then EXECUTE it with plpgsql. The other languages offer various ways too. -- Richard Huxton Archonet Ltd ---(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] Multiple Rules :: Postgres Is confused !!
Najib Abi Fadel wrote: WHY IS THAT HAPPENNING ?? I can provide more details if anyone is ready to help ... You will need to provide if anyone is to help. One thing you need to consider is that rules are basically like macros, with all the issues that can have. -- Richard Huxton Archonet Ltd ---(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
[GENERAL] Setting search paths inside a function (plpgsql)
Dear peoples,
I've got a problem which seemed to be neatly solved by the use of schemas, and in fact
it mostly works, but I have tried to go one step too far, perhaps.
Rather than have the application do
SET search_path TO f12057;
SELECT * FROM parcel-owners WHERE ... ;
SET search_path TO public;
I thought I'd have a single function in the public schema which they call:
select * from fips_name_srch('12057','white');
and in the function I do:
env_str := ''SET search_path TO f'' || p_fips || '',public'';
EXECUTE env_str;
and then my search and a LOOP to return values with a final SET command to put us back
to the public schema ...
In fact it works, once, and returns the expected values, but subsequent calls get
exactly the same data ...
gex_vector=# select * from fips_name_srch('12057','white');
NOTICE: doing name search for fips 12057
NOTICE: did exec of SET search_path TO f12057,public
parcel_gid | parcel_fips | parcel_zip | parcel_ownname
+-++
11449960 | 12057 | 33548 | DELANOE WHITE
11437500 | 12057 | 33548 | WHITE DORREN
11444394 | 12057 | 33548 | WHITE FERD T AND LACY A JR
...
select * from fips_name_srch('12031','white');
NOTICE: doing name search for fips 12031
NOTICE: did exec of SET search_path TO f12031,public
parcel_gid | parcel_fips | parcel_zip | parcel_ownname
+-++
11449960 | 12057 | 33548 | DELANOE WHITE
11437500 | 12057 | 33548 | WHITE DORREN
11444394 | 12057 | 33548 | WHITE FERD T AND LACY A JR
...
If I exit and run the second one it works:
gex_vector=# select * from fips_name_srch('12031','white');
NOTICE: doing name search for fips 12031
NOTICE: did exec of SET search_path TO f12031,public
parcel_gid | parcel_fips | parcel_zip | parcel_ownname
+-++
8830922 | 12031 | 32202 | CARLA WHITE MISSION
8830925 | 12031 | 32202 | CARLA WHITE MISSION
8855011 | 12031 | 32202 | CARLA WHITE MISSION
8824016 | 12031 | 32202 | CARLA WHITE MISSION INC
...
I have tried variations with VOLATILE explicitly defined and some unsuccessful
gyrations. I am sure the answer is obvious but I am not seeing it. This is postgres
7.4, the function is below.
Any suggestions or advice would be welcome ... (RTFM acceptable but a page reference
would be helpful)
thanks,
Greg Williamson
DBA
GlobeXplorer LLC
CREATE TYPE fips_name_results_t AS (parcel_gid INTEGER,
parcel_fips VARCHAR(10),parcel_zip VARCHAR(10),parcel_ownname TEXT);
BEGIN;
CREATE OR REPLACE FUNCTION fips_name_srch(VARCHAR,VARCHAR)
RETURNS setof fips_name_results_t AS '
DECLARE p_fips ALIAS FOR $1;
p_srchstr ALIAS FOR $2;
parcel_gid INTEGER;
parcel_zip VARCHAR(10);
parcel_ownname TEXT;
env_str TEXT;
retrec fips_name_results_t%rowtype;
BEGIN
RAISE NOTICE ''doing name search for fips %'',p_fips;
env_str := ''SET search_path TO f'' || p_fips || '',public'';
EXECUTE env_str;
RAISE NOTICE ''did exec of %'',env_str;
FOR retrec IN
SELECT o.gid,o.s_fips_cou,o.s_zip,o.s_ownername
FROM parcel_owners o, parcel_owner_fti f
WHERE f.string = p_srchstr AND f.id = o.orig_id ORDER BY 2,3,4
LOOP
RETURN NEXT retrec;
--SET search_path TO public;
END LOOP;
RETURN;
SET search_path TO public;
END;
' LANGUAGE 'plpgsql' VOLATILE;
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Multiple Rules :: Postgres Is confused !!
Details: I have a table transactions_sco and a view transactions_sco_v defined as : create view transactions_sco_v as select * from transactions_sco; I have the following Rules: CREATE RULE transactions_sco_up1 AS ON UPDATE TO transactions_sco_v DO INSTEAD UPDATE transactions_sco SET traiter = new.traiter WHERE (transactions_sco.cursus_id = old.cursus_id) AND (transactions_sco.vers_id = old.vers_id)) AND (transactions_sco.traiter = old.traiter)) AND (transactions_sco.code_type_academic = old.cod e_type_academic)) AND (transactions_sco.cod_etu = old.cod_etu)); CREATE RULE transactions_sco_up2 AS ON UPDATE TO transactions_sco_v DO INSTEAD UPDATE transactions_sco SET traiter = new.traiter WHERE transactions_sco.cursus_id = old.cursus_id) AND (transactions_sco.vers_id = old.vers_id)) A ND (transactions_sco.traiter = old.traiter)) AND (transactions_sco.code_type_academic = old.code _type_academic)); CREATE RULE transactions_sco_up8 AS ON UPDATE TO transactions_sco_v DO INSTEAD UPDATE transactions_sco SET traiter = new.traiter WHERE (transactions_sco.id = old.id); Now look what is happening: SELECT count(1) from transactions_sco where traiter='f'; count --- 17591 update transactions_sco_v set traiter='t' where id = 53597; UPDATE 1 SELECT count(1) from transactions_sco where traiter='f'; count --- 17589 AS YOU CAN SEE TWO ROWS WHERE UPDATED INSTEAD OF ONE !! THE COMMON THINGS BETWEEN THE TWO ROWS IS THAT THE FIELDS: cod_etu, cursus_id,vers_id,code_type_academic are the same IF I REMOVE the rules transactions_sco_up1 and transactions_sco_up2 the update works fine ... Thx for any help. ---(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] Multiple Rules :: Postgres Is confused !!
Najib Abi Fadel wrote: Details: I have a table transactions_sco and a view transactions_sco_v defined as : create view transactions_sco_v as select * from transactions_sco; I have the following Rules: CREATE RULE transactions_sco_up1 AS ON UPDATE TO transactions_sco_v DO INSTEAD UPDATE transactions_sco SET traiter = new.traiter WHERE (transactions_sco.cursus_id = old.cursus_id) AND (transactions_sco.vers_id = old.vers_id)) AND (transactions_sco.traiter = old.traiter)) AND (transactions_sco.code_type_academic = old.cod e_type_academic)) AND (transactions_sco.cod_etu = old.cod_etu)); CREATE RULE transactions_sco_up2 AS ON UPDATE TO transactions_sco_v DO INSTEAD UPDATE transactions_sco SET traiter = new.traiter WHERE transactions_sco.cursus_id = old.cursus_id) AND (transactions_sco.vers_id = old.vers_id)) A ND (transactions_sco.traiter = old.traiter)) AND (transactions_sco.code_type_academic = old.code _type_academic)); OK, so upd1 compares: (cursus_id, vers_id, traiter, code_type_academic, cod_etu) upd2 compares: (cursus_id, vers_id, traiter, code_type_academic) This means upd1 is redundant since any rows affected by upd1 *must* be affected by upd2. CREATE RULE transactions_sco_up8 AS ON UPDATE TO transactions_sco_v DO INSTEAD UPDATE transactions_sco SET traiter = new.traiter WHERE (transactions_sco.id = old.id); OK, this one just compares id, which is presumably the primary key and unique. Now look what is happening: SELECT count(1) from transactions_sco where traiter='f'; count --- 17591 update transactions_sco_v set traiter='t' where id = 53597; UPDATE 1 SELECT count(1) from transactions_sco where traiter='f'; count --- 17589 AS YOU CAN SEE TWO ROWS WHERE UPDATED INSTEAD OF ONE !! THE COMMON THINGS BETWEEN THE TWO ROWS IS THAT THE FIELDS: cod_etu, cursus_id,vers_id,code_type_academic are the same Because that's what you asked upd1/2 to do for you. To see what is happening, try selecting row id=53597 then manually running each rule yourself, substituting in the OLD.foo from your selected row. You should find that there are two rows that match 53597 on (cursus_id, vers_id, traiter, code_type_academic) - itself and one other. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: Null comparisons (was Re: [GENERAL] checksum)
On Tue, 28 Sep 2004, Greg Stark wrote: Marco Colombo [EMAIL PROTECTED] writes: (a = b or (a is null and b is null)) that raises a flag for me. It seems that NULL is used as a special value, which is not. Well, as I said, it raised a flag for me too. However, it's not good to be too dogmatic about things. General rules are useful guiding principles but you have to recognize when it's worth it to break them. We don't know enough about his problem to say another approach would be any better. The way SQL treats NULLs in boolean expressions is not a general rule. It's just the way it is. I'm being pragmatic not dogmatic. For example, in one application I have a table that *does* have unknown values. However I do need to look up records that match criteria including having unknown values in specific positions. For most queries using NULL is convenient and it's perfectly appropriate. But I have queries like this user does and I use coalesce since I find the resulting expression much clearer than using the three-way logical expression above. Using special values is a mistake, but I agree that's being dogmatic. Using NULL as a special value is a way worse mistake. Three way logic it's there, there's nothing you can do about that. NULL can't be a value. That's why you can't use it in comparisons. As for the dogma part, I bet you're using special values only to model states, not values, and are using the same columns you're using to model values. Just add another column, life will be better. This has nothing to do with NULLs, BTW. Incidentally, coalesce(a,0)=coalesce(b,0) has the advantage over all the other suggestions that you can build an index on coalesce(a,0) and/or coalesce(b,0) and use them for the join or for individual record lookups. BTW, coalesce(a,0) = coalesce(b,0) is wrong, since it assumes 0 is a special value, never used in the table. If so, it's better use it from the start instead of NULL for those special rows. I specifically said you had to use a special value in my suggestion. Saying something is wrong when it does what's needed just because it violates some abstract design principle is just short-sighted. Using 0 in the table might violate unique constraints or foreign key constraints. I try to avoid having a single quirky table propagate its quirkiness to the rest of the system. _Unique_ constraints? I don't get this. 0 has no special meaning. 1, -1, 100 might violate unique constraints as well. Any value might. What's the point here? Same goes for foreign key constraints. Actually same goes for _any_ constraint. I agree that using a value you _know_ it's invalid due to some contraints allows you to do the coalesce trick safely. But this assumes there is at least _one_ invalid value. This is not true in general. In general, coalesce(a,0) = coalesce(b,0) is not the same of (a = b) or (a is null and b is null). I've even provided a fine example. It can't be a general equivalence. For example, creating a bogus 0 record in some other table just to satisfy the foreign key constraint then having the rest of the application have to work around this bogus record results in a much less workable system than simply using NULL instead of 0 for the special value. I'm lost here. I've never proposed to add 0 anywhere. I just wonder: what if someone does?. In order to use 0 in coalesce(a,0) = coalesce(b,0) you have to make sure 0 is invalid for both a and b. If you knew -1 is invalid instead, you would use coalesce(a,-1) = coalesce(b,-1). But what if there's no invalid value? It might seem a dogmatic question, but my point is: why bother? Just get the model right. Mapping NULLs to 0 or -1 or whatever is meaningless, if the model is right. That's again the whole point. What you're proposing sounds like this: I'd use 0 (or other special value) in the table, but that's not good cause it may break some constraint. So I use NULLs in place of my special value, and convert them at later time with coalesce(), so that I can compare them again. Now, that's abusing of NULLs. There's a reason why NULLs don't break foreign key constraints, and a reason why you can't compare them. Your use of coalesce(), your functional index, it's just placing a brown paperbag on the real problem, which is a wrong model. Don't play with NULLs, fix your schema. A badly designed model is not a matter of general principles. It's a plain real-world mistake. And leads to any kind of acrobatic exercises in SQL to get the system work. One day you'll run into another query you've got a hard time to write or to make run efficently. NULLs are not meant to be values and should never be used as such. Anytime you feel the need of an index on them, or to compare them, you're treating them as values, and that's plain wrong. Not because of any dogma, but because that's how the system works. The _only_ way to use them as values is to abuse of them. coalesce(a = b, a is null and b is null) is correct, and maybe slightly
Re: [GENERAL] About PostgreSQL's limit on arithmetic operations
On Wednesday 29 Sep 2004 2:25 pm, Devrim GUNDUZ wrote: template1=# SELECT 512*18014398509481984::numeric(20) AS result; result - 9223372036854775808 (1 row) Ok, I got the same result in 7.4.5... But... Why do we have to cast it into numeric? The results from other databases shows that they can perform it without casting... Probably because the normal integer is 4 bytes long and bigint is 8 bytes long. The value above is exactly 2^63 at which a 8 bytes long signed bigint should flip sign/overflow. I am still puzzled with correct value and negative sign.. For arbitrary precision integer, you have to use numeric. It is not same as oracle. Furthermore if your number fit in range, then numbers like precision(4,0) in oracle to smallint in postgresql would buy you huge speed improvement(compared to postgresql numeric I mean) Please correct me if I am wrong.. Shridhar ---(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] Multiple Rules :: Postgres Is confused !!
This means upd1 is redundant since any rows affected by upd1 *must* be affected by upd2. OK CREATE RULE transactions_sco_up8 AS ON UPDATE TO transactions_sco_v DO INSTEAD UPDATE transactions_sco SET traiter = new.traiter WHERE (transactions_sco.id = old.id); OK, this one just compares id, which is presumably the primary key and unique. Right id is the primary key Now look what is happening: SELECT count(1) from transactions_sco where traiter='f'; count --- 17591 update transactions_sco_v set traiter='t' where id = 53597; UPDATE 1 SELECT count(1) from transactions_sco where traiter='f'; count --- 17589 AS YOU CAN SEE TWO ROWS WHERE UPDATED INSTEAD OF ONE !! THE COMMON THINGS BETWEEN THE TWO ROWS IS THAT THE FIELDS: cod_etu, cursus_id,vers_id,code_type_academic are the same Because that's what you asked upd1/2 to do for you. To see what is happening, try selecting row id=53597 then manually running each rule yourself, substituting in the OLD.foo from your selected row. You should find that there are two rows that match 53597 on (cursus_id, vers_id, traiter, code_type_academic) - itself and one other. Sorry, I didn't understand the manuel test procedure What is happening here? I am doing an update and the condition is on the ID and it is corresponding to the last Rule so why should the other rules interfer. Thanx for your help Najib. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] About PostgreSQL's limit on arithmetic operations
On Wed, Sep 29, 2004 at 04:51:30PM +0530, Shridhar Daithankar wrote: Probably because the normal integer is 4 bytes long and bigint is 8 bytes long. The value above is exactly 2^63 at which a 8 bytes long signed bigint should flip sign/overflow. I am still puzzled with correct value and negative sign.. Because in signed integer arithmatic the negative numbers go one further than the positive numbers. So the largest positive number is 2^63-1 and the largest negative number is -2^63. Or put it another way: -2^63 = +2^63. It's the far end of the loop, exactly opposite the zero. For arbitrary precision integer, you have to use numeric. It is not same as oracle. I would say that it should produce an overflow error rather than just returning the wrong answer, but that's not my call... Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpLJmK24WF87.pgp Description: PGP signature
Re: [GENERAL] Multiple Rules :: Postgres Is confused !!
Because that's what you asked upd1/2 to do for you. To see what is happening, try selecting row id=53597 then manually running each rule yourself, substituting in the OLD.foo from your selected row. You should find that there are two rows that match 53597 on (cursus_id, vers_id, traiter, code_type_academic) - itself and one other. Sorry, I didn't understand the manuel test procedure What is happening here? I am doing an update and the condition is on the ID and it is corresponding to the last Rule so why should the other rules interfer. Here you misunderstand. You've got an UPDATE on that table set to trigger a RULE. *All* the rules. Postgresql is not just going to pick one based on what it thinks you might mean. Since you've got 3 rules for UPDATE on that table, I imagine all three rules are getting fired. With DO INSTEAD I expect either the first one or the last one to win, I don't know enough about the specifics. Hope this helps, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpxGRwBvxvwt.pgp Description: PGP signature
Re: [GENERAL] Multiple Rules :: Postgres Is confused !!
Najib Abi Fadel wrote: AS YOU CAN SEE TWO ROWS WHERE UPDATED INSTEAD OF ONE !! THE COMMON THINGS BETWEEN THE TWO ROWS IS THAT THE FIELDS: cod_etu, cursus_id,vers_id,code_type_academic are the same Because that's what you asked upd1/2 to do for you. To see what is happening, try selecting row id=53597 then manually running each rule yourself, substituting in the OLD.foo from your selected row. You should find that there are two rows that match 53597 on (cursus_id, vers_id, traiter, code_type_academic) - itself and one other. Sorry, I didn't understand the manuel test procedure What is happening here? I am doing an update and the condition is on the ID and it is corresponding to the last Rule so why should the other rules interfer. ALL rules get executed. Conditions get combined (actually, parse trees get merged). === BEGIN rule_test.sql === CREATE TABLE foo (a int4 PRIMARY KEY, b text); COPY foo FROM stdin; 1 aaa 2 bbb 3 ccc 4 aaa 5 bbb 6 ccc \. CREATE VIEW foo_v AS SELECT * FROM foo WHERE b='bbb'; CREATE RULE foo_v_upd1 AS ON UPDATE TO foo_v DO INSTEAD UPDATE foo SET b = 'z' || NEW.b WHERE a = OLD.a; SELECT * FROM foo ORDER BY a; UPDATE foo_v SET b='xxx'; SELECT * FROM foo ORDER BY a; === END rule_test.sql === This will update 2 rows (those with b='bbb') since we impose no WHERE in our update but the view does. The OLD/NEW refer to target rows before/after the change. Does that make things clearer? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] About PostgreSQL's limit on arithmetic operations
On Wed, 29 Sep 2004, Devrim GUNDUZ wrote: We were performing some tests on PostgreSQL and found that it fails on the following query: SELECT 512*18014398509481984 AS result; and the result is: result - -- -9223372036854775808 This query should either return the correct answer or error. The constants are probably getting typed as int4 and int8 but the result overflows int8. If we say that the result of an int8 and int4 must be within the int8 precision (which appears to be allowed by the spec, although may not be what was inteneded) then it should error. We don't currently check the overflow cases for intn to generate the error, and we don't upgrade smaller integer types into numerics if it would have overflowed. At some point we probably need to do one of those, but AFAIK noone's been particularly motivated to do it. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] string is sometimes null ?
Hiya I have written a PL/PGSQL function which tots up users accounting information from their RADACCT data on the fly. So each insert or update modifies their totals. This has worked without error when I have tested it by hand with hand crafted inserts and updates, however when I put it on the actual live accounting server (both systems are running 7.4.3) it seems to work partially. The RADIUS server is reporting problems when trying to write START of accounting entries, and the error given is: ERROR: cannot EXECUTE a null querystring CONTEXT: PL/pgSQL function radacct_trig line 43 at open The lines its refering to are: --- START --- str:=''SELECT username,year_of_jan,current_in,current_out,current_start,last_update,m''||monthcurr||''_in AS monthin,m''||monthcurr||''_out AS monthout FROM customer_stats WHERE username=''||username||; OPEN curs FOR EXECUTE str; --- END --- str is defined as a varchar(255). What could be causing str to be NULL when the execute is caused? I would have thought if some of the variables were null that it would have complained about a malformed SQL query but this is saying the string is NULL! any ideas help much appreciated, thanks. -- - Graeme Hinchliffe (BSc) Core Internet Systems Designer Zen Internet (http://www.zen.co.uk/) Direct: 0845 058 9074 Main : 0845 058 9000 Fax : 0845 058 9005 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Setting search paths inside a function (plpgsql)
On Wed, 29 Sep 2004, Gregory S. Williamson wrote:
I've got a problem which seemed to be neatly solved by the use of
schemas, and in fact it mostly works, but I have tried to go one step
too far, perhaps.
Rather than have the application do
SET search_path TO f12057;
SELECT * FROM parcel-owners WHERE ... ;
SET search_path TO public;
I thought I'd have a single function in the public schema which they call:
select * from fips_name_srch('12057','white');
and in the function I do:
env_str := ''SET search_path TO f'' || p_fips || '',public'';
EXECUTE env_str;
and then my search and a LOOP to return values with a final SET command to put us
back to the public schema ...
I think you probably need to be using EXECUTE on the query you want to
have be affected by the above. Otherwise it's likely to be planned once
and saved with the first values used for the session.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Converting to Java date example
Quick question, hopefully... How do you store a date in Postgresql using Java date format, i.e. 2004-00-01 where that would be January 1, 2004 (looking for the 00 as Jan.) I've googled but only found someone else with the same problem. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Setting search paths inside a function (plpgsql)
Gregory S. Williamson [EMAIL PROTECTED] writes: RAISE NOTICE ''doing name search for fips %'',p_fips; env_str := ''SET search_path TO f'' || p_fips || '',public''; EXECUTE env_str; RAISE NOTICE ''did exec of %'',env_str; FOR retrec IN SELECT o.gid,o.s_fips_cou,o.s_zip,o.s_ownername FROM parcel_owners o, parcel_owner_fti f WHERE f.string = p_srchstr AND f.id = o.orig_id ORDER BY 2,3,4 LOOP You'd have to use FOR-IN-EXECUTE to make this work the way you are expecting. As is, the plan for the SELECT is generated and cached the first time through, and in the process the table references are bound to specific tables in specific schemas. regards, tom lane ---(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] Postgres inherited table, some questions...
Hello, I hate to re-ask this again (sorry to be repetitive!)... I really could use some help with this, if anyone is familiar with table inheritance as it applies to my questions Thanks! - Greg Hello, I am playing with the INHERITS functionality of create table to determine its suitability to my needs and I've hit a roadblock (hopefully minor)... If I have two tables like this: CREATE TABLE tablea ( id SERIAL ); CREATE TABLE tableb () INHERITS (tablea); #1. Now first of all, I notice that if I insert something into tableb, it appears in tablea and if I insert something in tablea, it does not appear in tableb... Is this correct and will the use of the SERIAL type cause any confusion or can I insert rows in each table without being concerned about any internal ramifications of this? #2. If I do explain select id from tablea where id=4, I get something like this: QUERY PLAN --- Seq Scan on jobdata (cost=0.00..1.04 rows=1 width=4) Filter: (id = 4) ...If, however, I do explain select id from tableb where id=4: - Result (cost=0.00..2.08 rows=2 width=4) - Append (cost=0.00..2.08 rows=2 width=4) - Seq Scan on jobdata_revisions (cost=0.00..1.04 rows=1 width=4) Filter: (id = 4) - Seq Scan on jobdata jobdata_revisions (cost=0.00..1.04 rows=1 width=4) Filter: (id = 4) I'm a bit mystified by the results of tableb, I don't understand what the Append and the second Seq Scan is for... More importantly, I'm not sure how to optimize this properly... Any ideas what I can do here?... Thanks! - Greg ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Composite Type Argument Construction to a Function
[EMAIL PROTECTED] writes: My question is - HOW do I construct the argument to my_function(my_type) so that the function recognises that I am passing into it composite data? Sorry, don't think you're going to have much joy on that in 7.3. In 8.0 you could do it with a ROW() constructor, but there's no such thing in 7.3. IIRC the only way to pass a row value as a function argument in 7.3 is to select the row from a table, viz select my_function(t.*) from some_table t; regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] string is sometimes null ?
On Wed, 29 Sep 2004, Graeme Hinchliffe wrote: The RADIUS server is reporting problems when trying to write START of accounting entries, and the error given is: ERROR: cannot EXECUTE a null querystring CONTEXT: PL/pgSQL function radacct_trig line 43 at open The lines its refering to are: --- START --- str:=''SELECT username,year_of_jan,current_in,current_out,current_start,last_update,m''||monthcurr||''_in AS monthin,m''||monthcurr||''_out AS monthout FROM customer_stats WHERE username=''||username||; If monthcurr or username are null, the above will be null. Anything || NULL = NULL. ---(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] PostgreSQL Installation Question
--- Geisler, Jim [EMAIL PROTECTED] wrote: I am trying to create an installer that will run PostgreSQL as a service onto a Windows machine( 2000 or Server 2003). Are there any suggestions for solving this problem? Any response is much appreciated. You might want to look at the source for the current 8.0 beta installer at http://pgfoundry.org/ for inspiration. Regards, Shelby Cain __ Do you Yahoo!? New and Improved Yahoo! Mail - 100MB free storage! http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] failure to restart postgresql service after improper shutdown
Title: Message after an improper shutdown (power failure) the postgres service cannot be restarted. simply restoring the postmaster.pid does not allow the postgres service to start. also, is there somekind of data integrity check built in with postgresql that can notify me of any data corruption resulting from the 'crash'? thanks.
Re: [GENERAL] Setting search paths inside a function (plpgsql)
Doh ! Thanks for the insight. Greg -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Wed 9/29/2004 8:40 AM To: Gregory S. Williamson Cc: [EMAIL PROTECTED] Subject:Re: [GENERAL] Setting search paths inside a function (plpgsql) Gregory S. Williamson [EMAIL PROTECTED] writes: RAISE NOTICE ''doing name search for fips %'',p_fips; env_str := ''SET search_path TO f'' || p_fips || '',public''; EXECUTE env_str; RAISE NOTICE ''did exec of %'',env_str; FOR retrec IN SELECT o.gid,o.s_fips_cou,o.s_zip,o.s_ownername FROM parcel_owners o, parcel_owner_fti f WHERE f.string = p_srchstr AND f.id = o.orig_id ORDER BY 2,3,4 LOOP You'd have to use FOR-IN-EXECUTE to make this work the way you are expecting. As is, the plan for the SELECT is generated and cached the first time through, and in the process the table references are bound to specific tables in specific schemas. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: Null comparisons (was Re: [GENERAL] checksum)
Marco Colombo [EMAIL PROTECTED] writes: I'm not against abusing of the db, nor playing dirty tricks, if that fits your needs. You're free to design your db the way you like and face the cost of a careful design or of later SQL gymnastics. I'm fine, as long as you don't ask for syntactic sugar to support those features. My point is that you're making judgements about his schema without actually knowing what you're talking about. For all we know his schema is entirely reasonable and it's the query that has unusual requirements. Spouting general design principles that may or may not apply as being iron-clad rules and saying it's just wrong to break them is wilful blindness. Database modelling is not something you can do by holding up some textbook and screaming third normal form until the developers trying to get work done cower in submission. There are lots of times when breaking or bending the rules is entirely reasonable and blindly following them is simply a waste of time. For example, I have a table that uses NULLs to represent absent data. In 90% of the queries three value logic is just exactly what's needed. In any case they each have foreign key dependencies and having special values to represent the absent values would be a major pain. It would require satisfying the foreign keys with bogus records. However I have queries that have to match provided data with other records, including having missing data in the same position. For such a query I need to break the usual model of three value logic and write something similar to what this user needs. In my case no index would really be reasonable since there are half a dozen such fields, but in general there's no reason an index shouldn't be available for such cases. -- greg ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Postgres inherited table, some questions...
Well, considering that the EXPLAIN tables are different from tablea/tableb, I can only assume that your jobdata tables follow the same pattern. Are you familiar with the ONLY syntax for SELECT when using inheritance? It looks like you might need to SELECT ONLY ... FROM tableb. -tfo On Sep 29, 2004, at 12:10 PM, Net Virtual Mailing Lists wrote: Hello, I hate to re-ask this again (sorry to be repetitive!)... I really could use some help with this, if anyone is familiar with table inheritance as it applies to my questions Thanks! - Greg Hello, I am playing with the INHERITS functionality of create table to determine its suitability to my needs and I've hit a roadblock (hopefully minor)... If I have two tables like this: CREATE TABLE tablea ( id SERIAL ); CREATE TABLE tableb () INHERITS (tablea); #1. Now first of all, I notice that if I insert something into tableb, it appears in tablea and if I insert something in tablea, it does not appear in tableb... Is this correct and will the use of the SERIAL type cause any confusion or can I insert rows in each table without being concerned about any internal ramifications of this? #2. If I do explain select id from tablea where id=4, I get something like this: QUERY PLAN --- Seq Scan on jobdata (cost=0.00..1.04 rows=1 width=4) Filter: (id = 4) ...If, however, I do explain select id from tableb where id=4: - Result (cost=0.00..2.08 rows=2 width=4) - Append (cost=0.00..2.08 rows=2 width=4) - Seq Scan on jobdata_revisions (cost=0.00..1.04 rows=1 width=4) Filter: (id = 4) - Seq Scan on jobdata jobdata_revisions (cost=0.00..1.04 rows=1 width=4) Filter: (id = 4) I'm a bit mystified by the results of tableb, I don't understand what the Append and the second Seq Scan is for... More importantly, I'm not sure how to optimize this properly... Any ideas what I can do here?... Thanks! - Greg ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] COPY FROM STDIN not in local file
Is there a way to do COPY FROM STDIN from sql? So, remotely I could run the copy command and somehow push the info over instead of having it on the server. -Josh ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Custom Functions
Hello, On Tue, 2004-09-28 at 13:57, Tim Penhey wrote: Matthew Metnetsky wrote: So, does anyone know of (or have) good examples of queries within functions (and returning sets of data)? Thanks in advance, ~ Matthew Hi Matthew, I wrote up an example which can be found at http://www.scorefirst.com/postgresql.html, not sure if it is what you are after or not. Tim That was the exact foundation I was looking for. I'm not sure why I over looked plpgsql but I did, and its much easier than C custom functions which are hideous and completely far from portable. Thanks, ~ Matthew ---(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] COPY FROM STDIN not in local file
You have to use psql's \copy. --- Josh Close wrote: Is there a way to do COPY FROM STDIN from sql? So, remotely I could run the copy command and somehow push the info over instead of having it on the server. -Josh ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] dangling permission on tables after drop user.
I have a database which started on Pg 7.1, moved to 7.2 via pg_dump/restore, and ultimately to Pg 7.4 likewise. While it was in 7.2, I added one user and granted access to various tables. After the 7.4 migration, that user was no longer needed, so was removed via dropuser command line tool. Now, when I pg_dump that db using the version 7.4.5 tools, I cannot restore because there are still grants in there for this phantom user: REVOKE ALL ON TABLE partners FROM PUBLIC; GRANT INSERT,SELECT,UPDATE,DELETE ON TABLE partners TO www; GRANT ALL ON TABLE partners TO 102; there is no user with ID 102 in the pg_user view. pg_restore complains about the missing user 102. And no, the user was not 102 it was the name of a (former) employee. My questions are: 1) did I do something wrong in dropping that user? 2) how do I fix this in my system tables? The gross hack is to pg_restore to an ascii file and delete those GRANT lines, but the compressed dump is over 2Gb for this database. Vivek Khera, Ph.D. +1-301-869-4449 x806 ---(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] dangling permission on tables after drop user.
Vivek Khera wrote: there is no user with ID 102 in the pg_user view. pg_restore complains about the missing user 102. And no, the user was not 102 it was the name of a (former) employee. The gross hack is to pg_restore to an ascii file and delete those GRANT lines, but the compressed dump is over 2Gb for this database. Am I missing something Vivek, or should the gross hack be creating a user with id=102 ? -- Richard Huxton Archonet Ltd ---(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] dangling permission on tables after drop user.
On Sep 29, 2004, at 4:55 PM, Richard Huxton wrote: Vivek Khera wrote: there is no user with ID 102 in the pg_user view. pg_restore complains about the missing user 102. And no, the user was not 102 it was the name of a (former) employee. The gross hack is to pg_restore to an ascii file and delete those GRANT lines, but the compressed dump is over 2Gb for this database. Am I missing something Vivek, or should the gross hack be creating a user with id=102 ? And how exactly does one accomplish this? pg_users is a view so you can't insert into it. Vivek Khera, Ph.D. +1-301-869-4449 x806 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] dangling permission on tables after drop user.
On Wed, Sep 29, 2004 at 05:07:38PM -0400, Vivek Khera wrote: On Sep 29, 2004, at 4:55 PM, Richard Huxton wrote: Vivek Khera wrote: there is no user with ID 102 in the pg_user view. pg_restore complains about the missing user 102. And no, the user was not 102 it was the name of a (former) employee. The gross hack is to pg_restore to an ascii file and delete those GRANT lines, but the compressed dump is over 2Gb for this database. Am I missing something Vivek, or should the gross hack be creating a user with id=102 ? And how exactly does one accomplish this? pg_users is a view so you can't insert into it. CREATE USER ... WITH SYSID 102; -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Dios hizo a Adán, pero fue Eva quien lo hizo hombre. ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Index Analysis: Filters
I'm interested to know a little bit more about the postgres
implementation of indexes. I'm specifically wondering what it means in
the output of EXPLAIN when a filter is applied.
I'm trying to decide whether it makes sense to use indexes on
expressions rather than relying on a left-anchored LIKE for date
filtering.
Here's what I've got:
WHERE some_date LIKE 'year-month%' *
And what I'm wondering is whether it would be faster to add indexes on
expressions for something like:
WHERE EXTRACT( year from some_date ) = 'year'
AND EXTRACT( month from some_date ) = 'month'
In practice, the point in the implementation has other parameters, so
it ends up looking something like:
SELECT COUNT( * )
FROM some_table AS st, some_other_table AS sot
WHERE st.id = 'id'
AND st.some_date LIKE 'year-month%'
AND st.other_id = sot.other_id
Here's what I get from an EXPLAIN:
QUERY
PLAN
Aggregate (cost=4749.19..4749.19 rows=1 width=0)
- Nested Loop (cost=0.00..4749.17 rows=9 width=0)
- Index Scan using st_id_idx on emma_mailings em
(cost=0.00..147.56 rows=1 width=8)
Index Cond: (emma_account_id = 1::bigint)
Filter: ((some_date)::text ~~ '2004-06%'::text)
- Index Scan using sot_other_id_idx on some_other_table sot
(cost=0.00..3164.42 rows=114975 width=8)
Index Cond: (outer.other_id = sot.other_id)
Then I try:
SELECT COUNT( * )
FROM some_table AS st, some_other_table AS sot
WHERE st.id = 'id'
AND EXTRACT( year FROM st.some_date ) = 'year'
AND EXTRACT( month FROM st.some_date ) = 'month'
AND st.other_id = sot.other_id
When I first added indexes on the EXTRACT expressions on
some_table.some_date, I basically (the numbers are fudged because now
the new indexes are working) saw:
QUERY
PLAN
Aggregate (cost=4749.19..4749.19 rows=1 width=0)
- Nested Loop (cost=0.00..4749.17 rows=9 width=0)
- Index Scan using st_id_idx on emma_mailings em
(cost=0.00..147.56 rows=1 width=8)
Index Cond: (emma_account_id = 1::bigint)
Filter: ((date_part('year'::text, emma_mailing_start_ts)
= 2004::double precision) AND (date_part('year'::text,
emma_mailing_start_ts) = 6::double precision))
- Index Scan using sot_other_id_idx on some_other_table sot
(cost=0.00..3164.42 rows=114975 width=8)
Index Cond: (outer.other_id = sot.other_id)
Now, though, it seems to be using the expression indexes. I guess my
main question is what it means to apply a Filter to an Index Scan, and
whether the LIKE filter would be slower than the EXTRACT filter. In the
queries I've tested, EXPLAIN ANALYZE resulted in almost identical
runtimes until the indexes on expressions kicked in. Once that
happened, the new indexes were much faster.
A related question might be what might've happened between the times I
tested when the indexes weren't working and when they were. I've been
testing on a low-traffic development server, and I ANALYZED after
adding the indexes. It was in a new session, though, that the indexes
on EXTRACT actually kicked in.
-tfo
* = generic pseudocode placeholder for variable/constant data
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] dangling permission on tables after drop user.
Vivek Khera [EMAIL PROTECTED] writes: On Sep 29, 2004, at 4:55 PM, Richard Huxton wrote: Am I missing something Vivek, or should the gross hack be creating a user with id=102 ? And how exactly does one accomplish this? CREATE USER. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] 7.3.4 vacuum/analyze error
I'm getting a slew of these repeatable errors when running ANALYZE and/or VACUUM ANALYZE (from an autovacuum process) against a 7.3.4 cluster on HP-UX B.11.00: 2004-09-29 18:14:53.621 [520]ERROR: Memory exhausted in AllocSetAlloc(1189) This error is in the FAQ, but that answer does not appear applicable. The error is occurring on 2 different databases, on multiple tables, and all tables involved are frequently updated. Any clues? Here's an example with more context: 2004-09-29 18:20:55.426 [3728] LOG: query: ANALYZE audit TopMemoryContext: 32792 total in 4 blocks; 11664 free (23 chunks); 21128 used TopTransactionContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used DeferredTriggerXact: 0 total in 0 blocks; 0 free (0 chunks); 0 used TransactionCommandContext: 8192 total in 1 blocks; 8176 free (9 chunks); 16 used QueryContext: 8192 total in 1 blocks; 7440 free (1 chunks); 752 used Analyze: 132263832 total in 27 blocks; 2984 free (35 chunks); 132260848 used Vacuum: 8192 total in 1 blocks; 8152 free (0 chunks); 40 used DeferredTriggerSession: 0 total in 0 blocks; 0 free (0 chunks); 0 used PortalMemory: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used CacheMemoryContext: 516096 total in 6 blocks; 170872 free (1 chunks); 345224 used pg_index_indrelid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_toast_16410_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used pg_stat_all_tables: 19456 total in 19 blocks; 216 free (0 chunks); 19240 used pg_settings: 5120 total in 5 blocks; 336 free (0 chunks); 4784 used pg_conversion_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_index_indexrelid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_shadow_usesysid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used pg_group_name_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_opclass_am_name_nsp_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used pg_cast_source_target_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used pg_language_name_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_type_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_language_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_amop_opc_strategy_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used pg_statistic_relid_att_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used pg_amproc_opc_procnum_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used pg_class_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_class_relname_nsp_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used pg_type_typname_nsp_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used pg_conversion_default_index: 2072 total in 2 blocks; 712 free (0 chunks); 1360 used pg_aggregate_fnoid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_namespace_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_group_sysid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used pg_shadow_usename_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_namespace_nspname_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_inherits_relid_seqno_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used pg_operator_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_conversion_name_nsp_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used pg_opclass_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_proc_proname_args_nsp_index: 2072 total in 2 blocks; 712 free (0 chunks); 1360 used pg_proc_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_operator_oprname_l_r_n_index: 2072 total in 2 blocks; 712 free (0 chunks); 1360 used pg_amop_opc_opr_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used MdSmgr: 8192 total in 1 blocks; 6120 free (0 chunks); 2072 used DynaHash: 8192 total in 1 blocks; 7064 free (0 chunks); 1128 used DynaHashTable: 8192 total in 1 blocks; 5080 free (0 chunks); 3112 used DynaHashTable: 8192 total in 1 blocks; 6112 free (0 chunks); 2080 used DynaHashTable: 8192 total in 1 blocks; 3016 free (0 chunks); 5176 used DynaHashTable: 8192 total in 1 blocks; 4040 free (0 chunks); 4152 used DynaHashTable: 24576 total in 2 blocks; 13240 free (4 chunks); 11336 used DynaHashTable: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used DynaHashTable: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used DynaHashTable: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used DynaHashTable: 8192 total in 1 blocks; 8176 free (0
Re: [GENERAL] Index Analysis: Filters
Thomas F.O'Connell [EMAIL PROTECTED] writes:
I'm interested to know a little bit more about the postgres
implementation of indexes. I'm specifically wondering what it means in
the output of EXPLAIN when a filter is applied.
The index itself is using the condition(s) indicated as Index Cond ---
that is, the index scan will pull all rows satisfying Index Cond from
the table. The Filter condition, if any, is then evaluated at each
such row to decide whether to return it up to the next plan level.
Basically the filter is whatever conditions apply to the table but can't
be implemented directly with the chosen index.
Here's what I've got:
WHERE some_date LIKE 'year-month%' *
Seems like you'd be better off turning this into a range query. A
textual LIKE is just about the most inefficient way of testing a date
range that I can think of. How about
WHERE some_date = 'year-month-01'::date AND some_date
('year-month-01'::date + '1 month'::interval)::date
(adjust as appropriate if it's really a timestamp column).
regards, tom lane
---(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] 7.3.4 vacuum/analyze error
Ed L. [EMAIL PROTECTED] writes: I'm getting a slew of these repeatable errors when running ANALYZE and/or VACUUM ANALYZE (from an autovacuum process) against a 7.3.4 cluster on HP-UX B.11.00: 2004-09-29 18:14:53.621 [520]ERROR: Memory exhausted in AllocSetAlloc(1189) Analyze: 132263832 total in 27 blocks; 2984 free (35 chunks); 132260848 used Either increase your per-process memory limit, or reduce the statistics targets for this table ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Index Analysis: Filters
Yeah, I suppose this would be faster than the EXTRACT technique, too,
eh? Because it requires only a single index and is not an index on an
expression, which are generally more expensive?
-tfo
On Sep 29, 2004, at 6:59 PM, Tom Lane wrote:
Thomas F.O'Connell [EMAIL PROTECTED] writes:
Here's what I've got:
WHERE some_date LIKE 'year-month%' *
Seems like you'd be better off turning this into a range query. A
textual LIKE is just about the most inefficient way of testing a date
range that I can think of. How about
WHERE some_date = 'year-month-01'::date AND some_date
('year-month-01'::date + '1 month'::interval)::date
(adjust as appropriate if it's really a timestamp column).
regards, tom lane
---(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] 7.3.4 vacuum/analyze error
On Wednesday September 29 2004 5:17, Tom Lane wrote: Ed L. [EMAIL PROTECTED] writes: I'm getting a slew of these repeatable errors when running ANALYZE and/or VACUUM ANALYZE (from an autovacuum process) against a 7.3.4 cluster on HP-UX B.11.00: 2004-09-29 18:14:53.621 [520]ERROR: Memory exhausted in AllocSetAlloc(1189) Analyze: 132263832 total in 27 blocks; 2984 free (35 chunks); 132260848 used Either increase your per-process memory limit, or reduce the statistics targets for this table ... What am I missing? $ ulimit -a time(seconds)unlimited file(blocks) unlimited data(kbytes) 131072 stack(kbytes)8192 memory(kbytes) unlimited coredump(blocks) 4194303 nofiles(descriptors) 120 $ psql -c select name, setting from pg_settings | egrep stats_ stats_block_level | off stats_command_string | off stats_reset_on_server_start| on stats_row_level| off stats_start_collector | on $ psql -c analyze audit ERROR: Memory exhausted in AllocSetAlloc(1189) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] 7.3.4 vacuum/analyze error
On Wednesday September 29 2004 8:33, Ed L. wrote: On Wednesday September 29 2004 5:17, Tom Lane wrote: Ed L. [EMAIL PROTECTED] writes: I'm getting a slew of these repeatable errors when running ANALYZE and/or VACUUM ANALYZE (from an autovacuum process) against a 7.3.4 cluster on HP-UX B.11.00: 2004-09-29 18:14:53.621 [520]ERROR: Memory exhausted in AllocSetAlloc(1189) Analyze: 132263832 total in 27 blocks; 2984 free (35 chunks); 132260848 used Either increase your per-process memory limit, or reduce the statistics targets for this table ... What am I missing? $ ulimit -a memory(kbytes) unlimited $ psql -c select name, setting from pg_settings | egrep stats_ stats_block_level | off stats_command_string | off stats_reset_on_server_start| on stats_row_level| off stats_start_collector | on $ psql -c analyze audit ERROR: Memory exhausted in AllocSetAlloc(1189) We actually count on those stats (stats_row_level?) in order to effectively autovacuum based on real changes, so turning them off would not be good. Is this a bug fixed in a later versions? What might be triggering this? The only thing I see in common is that all the tables are frequently updated... Ed ---(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] 7.3.4 vacuum/analyze error
Ed L. [EMAIL PROTECTED] writes: Either increase your per-process memory limit, or reduce the statistics targets for this table ... What am I missing? $ ulimit -a time(seconds)unlimited file(blocks) unlimited data(kbytes) 131072 ^^^ This is where the limit is coming from ... $ psql -c select name, setting from pg_settings | egrep stats_ stats_block_level | off Those are not the statistics I'm talking about. I was assuming that you'd done ALTER TABLE SET STATISTICS somewhere along the line, or changed default_statistics_target. If you haven't, then this must be a pretty strange table --- how many columns has it got? regards, tom lane ---(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] 7.3.4 vacuum/analyze error
On Wednesday September 29 2004 8:59, Tom Lane wrote: Ed L. [EMAIL PROTECTED] writes: Either increase your per-process memory limit, or reduce the statistics targets for this table ... What am I missing? $ ulimit -a time(seconds)unlimited file(blocks) unlimited data(kbytes) 131072 ^^^ This is where the limit is coming from ... $ psql -c select name, setting from pg_settings | egrep stats_ stats_block_level | off Those are not the statistics I'm talking about. I was assuming that you'd done ALTER TABLE SET STATISTICS somewhere along the line, or changed default_statistics_target. If you haven't, then this must be a pretty strange table --- how many columns has it got? No ALTER TABLE SET STATISTICS being done here, but I do enable stats_row_level and stats_block_level. The table has 3 columns, one of which contains huge text values. Yes, barf. Should I change the data size to 'unlimited'? Or just bump it up? And is it possible to change the data size limit for the postmaster and backends without restarting them? Ed ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] How to get user's role?
Hi all, I want to get what roles/groups that the current user is member of. I tried to use the following commands but they both returned no rows. SELECT * FROM information_schema.enabled_roles SELECT * FROM information_schema.applicable_roles Anybody know how to solve this ? or anybody has a certain function that look up for user's roles/groups ? Thanks in advanced, Armen
[GENERAL] Out of memory errors on OS X
I have a couple users trying to install Postgres on OS X. To the best of my knowledge, both of them are using 7.4.5/10.3.5, and got identical errors while trying to init the database: Reducing the shared buffers didn't help. Any thoughts would be appreciated. Jeffrey Melloy [EMAIL PROTECTED] William-Rowcliffes-Computer:/Users/wmrowcliffe postgres$ /usr/local/bin/initdb -D /usr/local/pgsql/data The files belonging to this database system will be owned by user postgres. This user must also own the server process. The database cluster will be initialized with locale C. fixing permissions on existing directory /usr/local/pgsql/data... ok creating directory /usr/local/pgsql/data/base... ok creating directory /usr/local/pgsql/data/global... ok creating directory /usr/local/pgsql/data/pg_xlog... ok creating directory /usr/local/pgsql/data/pg_clog... ok selecting default max_connections... 10 selecting default shared_buffers... 50 creating configuration files... ok creating template1 database in /usr/local/pgsql/data/base/1... FATAL: could not create shared memory segment: Cannot allocate memory DETAIL: Failed system call was shmget(key=1, size=1081344, 03600). HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory or swap space. To reduce the request size (currently 1081344 bytes), reduce PostgreSQL's shared_buffers parameter (currently 50) and/or its max_connections parameter (currently 10). The PostgreSQL documentation contains more information about shared memory configuration. initdb: failed ---(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] 7.3.4 vacuum/analyze error
Ed L. [EMAIL PROTECTED] writes: The table has 3 columns, one of which contains huge text values. Yes, barf. That shouldn't matter --- ANALYZE actually deliberately ignores very-wide values so as not to run out of memory. You sure you haven't changed default_statistics_target? I'd think you'd uncovered a memory leak in ANALYZE, except that somebody else would have found any such thing before now. Which PG version are you using exactly? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Out of memory errors on OS X
On Thu, 2004-09-30 at 13:49, Jeffrey Melloy wrote: I have a couple users trying to install Postgres on OS X. To the best of my knowledge, both of them are using 7.4.5/10.3.5, and got identical errors while trying to init the database: Have you tried the suggestions in the documentation? http://www.postgresql.org/docs/7.4/static/kernel-resources.html#SYSVIPC -Neil ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] 7.3.4 vacuum/analyze error
Honestly, I don't even know how. How can I check its value to see if someone else has? show default_statistics_target; regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] 7.3.4 vacuum/analyze error
On Wednesday September 29 2004 10:18, Tom Lane wrote: Honestly, I don't even know how. How can I check its value to see if someone else has? $ psql -c show default_statistics_target; default_statistics_target --- 1000 (1 row) Does that look like its been changed? I know I haven't changed this before. Ed ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] 7.3.4 vacuum/analyze error
On Wednesday September 29 2004 10:18, Tom Lane wrote: Honestly, I don't even know how. How can I check its value to see if someone else has? show default_statistics_target; Let me add this: I'm seeing this error show up across multiple clusters, maybe 5 or 6, all with similar schemas, on 2 different hpux boxes, all running 7.3.4. Ed ---(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] Out of memory errors on OS X
Tom Lane wrote: Jeffrey Melloy [EMAIL PROTECTED] writes: I have a couple users trying to install Postgres on OS X. To the best of my knowledge, both of them are using 7.4.5/10.3.5, and got identical errors while trying to init the database: They need to increase the system's shmmax limit (sysctl kern.sysv.shmmax, which is only 4MB by default). You can run one postmaster that way ... not very well, but it will run ... but you definitely can't start two. I surmise that they already had one postmaster running? In OSX 10.3 I believe that the recommended way to fix this is to edit /etc/rc's setting, and then reboot. AFAICS there is no reason not to raise shmmax to 50% or so of physical RAM. I have asked Apple about using a saner default for shmmax, but a few more complaints in their bug system wouldn't hurt. regards, tom lane I'll pass it on, though I'm wondering why they would have that problem and others (myself included) don't. Jeff ---(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] 7.3.4 vacuum/analyze error
Ed L. [EMAIL PROTECTED] writes: On Wednesday September 29 2004 10:18, Tom Lane wrote: $ psql -c show default_statistics_target; default_statistics_target --- 1000 (1 row) Does that look like its been changed? Uh ... the default is 10. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] 7.3.4 vacuum/analyze error
Hi All, Please let me know how do I unsbscribe to this newsgroup? Thanks, Murali -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ed L. Sent: Wednesday, September 29, 2004 9:21 PM To: Tom Lane Cc: [EMAIL PROTECTED] Subject: Re: [GENERAL] 7.3.4 vacuum/analyze error On Wednesday September 29 2004 10:18, Tom Lane wrote: Honestly, I don't even know how. How can I check its value to see if someone else has? $ psql -c show default_statistics_target; default_statistics_target --- 1000 (1 row) Does that look like its been changed? I know I haven't changed this before. Ed ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] How do I unsubscribe?
Hi All, Please let me know How do I unsbscribe from this news group? Thanks, Murali -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ed L. Sent: Wednesday, September 29, 2004 9:23 PM To: Tom Lane Cc: [EMAIL PROTECTED] Subject: Re: [GENERAL] 7.3.4 vacuum/analyze error On Wednesday September 29 2004 10:18, Tom Lane wrote: Honestly, I don't even know how. How can I check its value to see if someone else has? show default_statistics_target; Let me add this: I'm seeing this error show up across multiple clusters, maybe 5 or 6, all with similar schemas, on 2 different hpux boxes, all running 7.3.4. Ed ---(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 ---(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] 7.3.4 vacuum/analyze error
On Wednesday September 29 2004 5:17, Tom Lane wrote: 2004-09-29 18:14:53.621 [520]ERROR: Memory exhausted in AllocSetAlloc(1189) Analyze: 132263832 total in 27 blocks; 2984 free (35 chunks); 132260848 used Either increase your per-process memory limit, or reduce the statistics targets for this table ... Can you explain a little more of how you interpret the numbers above to draw your conclusion? Ed ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
