Re: [GENERAL] Backwards index scan
Thanks for putting me straight - I thought I remembered a previous post from Tom about nulls not being indexed but it was probably referring to partial indexes not indexing values that are null... Coalescing null values might still be helpful to ensure that they are ordered in the index at a specific location (either the beginning or the end depending on your substitution value). John Greg Stark wrote: John Sidney-Woollett [EMAIL PROTECTED] writes: I don't think that null values are indexed - you'll probably need to coalesce your null data value to some value if you want it indexed. That is most definitely not true for Postgres. NULL values are included in the index. However NULLs sort as greater than all values in Postgres. So when you sort descending they'll appear *first*. If you sort ascending they'll appear last. If you have any clauses like 'WHERE col foo' then it will not be true for NULL values of col regardless of what foo is and those records will be dropped. This is true regardless of whether there's an index. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Problem in Pg 8.1.4 with CREATEDB
Alexander Scholz wrote: Yes, we have the same problem! Refer to the thread with the subject Problem V8.1.4 - providing pwd for commandline tools doesn't work anymore. They seem to have change the old behaviour of the commandline tools, which worked well since 8.0! I don't understand why the change should be more secure, but I see that nobody took care about the possible consequences for installation scripts, third party applications and so on. :-((( At least they should have clearly stated this in the update readme. (The comment Fix problem with password prompting on some Win32 systems just says nothing at all, which could have alerted anybody that might concern the effect of it) Sigh, Alexander. Thank you for this hint. We will have to change our setup asap. Which one of the methods passing a password do you prefer ? Setting the Password as a environment variable doesn't seem to be very secure to me :-) Pit ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] ean code data type
Is there a data type for ean codes for postgresql 7.4? I found the isbn data type, and I would appreciate something similar for ean codes. Thank you signature.asc Description: OpenPGP digital signature
Re: [GENERAL] Data about rate of downloads
On Tue, Jun 06, 2006 at 09:05:30PM -0300, Rodrigo Gonzalez wrote: I dont know the answer to your question, but I think that you forgot somethingmost linux distributions have postgresql included.so, (I dont know the number) some people that use it, didnt download from postgresql site or mirrors... Indeed. If you look at something like popcon for Debian [1], it'll tell you that out of 13211 submissions, 1442 say they are using postgresql-common. Back of the envelope calculation tells you that about 10% of Debian machines are using it, but then you're stuck with estimating how many Debian machines there are. On top of that there are lot of different versions, you have to determine what the number means. I myself have never downloaded a tarball from the mirrors. I've only ever used CVS and Debian distributed versions... [1] http://popcon.debian.org/ Have a nice day, -- 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
[GENERAL] Problems copying data to different database
Hi,
My requirement is to archive records from main DB to the archive DB after
certain period. Using Dblink facility, I'm able to perform any kind of activity
on the archive DB, except copying from Table of MainDb to a Table of ArchiveDb.
Is it possible to move the selective records using following kind of query:
select dblink_exec ('dbname = ArchiveDb', 'insert into Archive_Table select *
from To_Be_Archived_Table')
Request your suggestions to achieve this.
Thanks,
RamaKrishna.
---(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] help with rules please
I dont succeed with writing my rules proper. I need the following rules: when I INSERT/UPDATE to a table and a certain condition is true then a special field in this data-row should be updated to. I came as far: # \d testa Table public.testa Column | Type | Modifiers +--+--- x1 | text | x2 | text | Rules: r1 AS ON INSERT TO testa WHERE new.x1 = 'house'::text DO UPDATE testa SET x2 = 'mouse'::text this works fine. When I insert a row with x1=house then x2 gets set to mouse. BUT: i) it always updates all rows in the tables instead of only the current row. I guess this is a huge performance-lack on big tables :) I tried to get a where oid=new.oid in or something like that, but it did not work. ii) the above rule does not work on UPDATE, cause I get a deep recursion. Each update causes another update on the same table which makes postgres break with a nested.loop -error (happily postgres detects the recursion :) thnx for any help peter -- mag. peter pilsl goldfisch.at IT- dataconsulting tel: +43 650 3574035 tel: +43 1 8900602 fax: +43 1 8900602 15 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] date value not geting inserted properly
i have a cloumn in my table which is date type i parse a file to get the date from there, and then i form a call to the stored procedure, with the above data as the parameter. but however it seems that when i form this call, something is messing up, and the value for the birth date, getting into that field is "1969-12-31" , and this happens always, it inserts this value only, whtever be the input data value. i am pasting the code that i am trying to do stmt.setObject(i + 1, (tagV.value),((TypeAttrib) (primaryKeyMap_.get(tMap.get(temp.dataType); can u please tell as to what should the dataType be for date, isnt it 91- integer? thanks, regards Surabhi
Re: [GENERAL] help with rules please
am 07.06.2006, um 13:23:09 +0200 mailte peter pilsl folgendes: I dont succeed with writing my rules proper. I need the following rules: when I INSERT/UPDATE to a table and a certain condition is true then a special field in this data-row should be updated to. I came as far: # \d testa Table public.testa Column | Type | Modifiers +--+--- x1 | text | x2 | text | Rules: r1 AS ON INSERT TO testa WHERE new.x1 = 'house'::text DO UPDATE testa SET x2 = 'mouse'::text this works fine. When I insert a row with x1=house then x2 gets set to mouse. BUT: i) it always updates all rows in the tables instead of only the current row. I guess this is a huge performance-lack on big tables :) I tried to get a where oid=new.oid in or something like that, but it did not work. My suggestion: write a TRIGGER for this. Examples for TRIGGER: http://www.postgresql.org/docs/8.1/interactive/plpgsql-trigger.html HTH, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Some mystery with execution plans on postgres 8.1
If short: adding index to table change execution plans on query which dont
use new index.
More detailed info:
tv2=# \d tv_events
Table public.tv_events
Column |Type |
Modifiers
+-+-
--
id | integer | not null default
nextval(('public.documents_id_seq'::text)::regclass)
status | smallint| not null default 0
name | character varying(255) |
ext_id | integer |
start | timestamp without time zone |
finish | timestamp without time zone |
star | integer |
flag_id| integer |
flag2_id | integer |
channel_id | integer |
Indexes:
tv_events_pkey PRIMARY KEY, btree (id)
tv_events_main3_idx btree (flag_id, start, finish)
tv_events_main_idx btree (channel_id, start, finish)
tv_events_start_finish btree (start, finish)
tv2=# select count(*) from tv_events;
count
---
30353
(1 row)
tv2=# EXPLAIN ANALYZE
tv2-# SELECT d.class, d.id, d.name, d.ctime, d.mtime, d.dtime, d.status,
d.ext_id, d.start, d.finish, d.star, d.flag_id, d.flag2_id, d.channel_id,
d.data FROM tv_events AS d WHERE (d.class IN ('tv2::Event')) AND finish =
'2006-06-09 06:00' AND start = '2006-06-08 06:00' AND (d.flag_id IN (5))
ORDER BY start;
QUERY PLAN
--
Sort (cost=862.48..864.12 rows=656 width=522) (actual time=6.913..7.684
rows=226 loops=1)
Sort Key: start
- Bitmap Heap Scan on tv_events d (cost=16.34..831.79 rows=656
width=522) (actual time=1.530..4.642 rows=226 loops=1)
Recheck Cond: ((flag_id = 5) AND (start = '2006-06-08
06:00:00'::timestamp without time zone) AND (finish = '2006-06-09
06:00:00'::timestamp without time zone))
Filter: ((class)::text = 'tv2::Event'::text)
- Bitmap Index Scan on tv_events_main3_idx (cost=0.00..16.34
rows=656 width=0) (actual time=1.366..1.366 rows=678 loops=1)
Index Cond: ((flag_id = 5) AND (start = '2006-06-08
06:00:00'::timestamp without time zone) AND (finish = '2006-06-09
06:00:00'::timestamp without time zone))
Total runtime: 8.657 ms
(8 rows)
Ok here used Bitmap Index Scan on tv_events_main3_idx
Lets now add one more index:
tv2=# CREATE INDEX test_idx on tv_events (flag_id,start);
CREATE INDEX
tv2=# ANALYZE tv_events;
ANALYZE
tv2=# EXPLAIN ANALYZE
tv2-# SELECT d.class, d.id, d.name, d.ctime, d.mtime, d.dtime, d.status,
d.ext_id, d.start, d.finish, d.star, d.flag_id, d.flag2_id, d.channel_id,
d.data FROM tv_events AS d WHERE (d.class IN ('tv2::Event')) AND finish =
'2006-06-09 06:00' AND start = '2006-06-08 06:00' AND (d.flag_id IN (5))
ORDER BY start;
QUERY PLAN
--
Index Scan using tv_events_main3_idx on tv_events d (cost=0.00..919.02
rows=656 width=522) (actual time=0.137..3.907 rows=226 loops=1)
Index Cond: ((flag_id = 5) AND (start = '2006-06-08
06:00:00'::timestamp without time zone) AND (finish = '2006-06-09
06:00:00'::timestamp without time zone))
Filter: ((class)::text = 'tv2::Event'::text)
Total runtime: 4.746 ms
(4 rows)
Now plan changed to more optimal and fast. But plan NOT using new index
test_idx...
Lets drop new test index again:
tv2=# drop INDEX test_idx;
DROP INDEX
tv2=# ANALYZE tv_events;
ANALYZE
tv2=# EXPLAIN ANALYZE
tv2-# SELECT d.class, d.id, d.name, d.ctime, d.mtime, d.dtime, d.status,
d.ext_id, d.start, d.finish, d.star, d.flag_id, d.flag2_id, d.channel_id,
d.data FROM tv_events AS d WHERE (d.class IN ('tv2::Event')) AND finish =
'2006-06-09 06:00' AND start = '2006-06-08 06:00' AND (d.flag_id IN (5))
ORDER BY start;
QUERY PLAN
--
Sort (cost=862.48..864.12 rows=656 width=522) (actual time=5.754..6.522
rows=226 loops=1)
Sort Key: start
- Bitmap Heap Scan on tv_events d (cost=16.34..831.79 rows=656
width=522) (actual time=0.952..3.584 rows=226 loops=1)
Recheck Cond: ((flag_id = 5) AND (start = '2006-06-08
06:00:00'::timestamp without time zone) AND (finish = '2006-06-09
06:00:00'::timestamp without time zone))
Filter: ((class)::text = 'tv2::Event'::text)
- Bitmap Index Scan on tv_events_main3_idx (cost=0.00..16.34
rows=656 width=0) (actual time=0.788..0.788
Re: [GENERAL] ean code data type
On 6/7/06, Ottavio Campana [EMAIL PROTECTED] wrote: Is there a data type for ean codes for postgresql 7.4? I found the isbn data type, and I would appreciate something similar for ean codes. If there isn't you can create your own: http://www.postgresql.org/docs/8.1/static/sql-createtype.html -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] COLLATE
(group dupe) 05 Jun 2006 12:53:57 -0400, Greg Stark [EMAIL PROTECTED]: But the closest partial solution suggested so far is the pg_xfrm function that has been implemented and posted at least three times by three different posters to the postgres mailing lists. In the interest of avoiding a fourth independent implementation I'll attach the one I use below, it's not big. Thanks! It may be the only solution available at the moment. But I have no idea how to compile/install it. I imagine there should be separate /contrib/pg_strxfrm directory, with the makefile etc. is it correct? if so, how should the makefile look like? I tried compiling but it failed: [EMAIL PROTECTED]:~/src/postgresql-8.1.3/src$ gcc -I ./include -fPIC -c pg_strxfrm.c pg_strxfrm.c: In function 'pg_strxfrm': pg_strxfrm.c:98: error: 'Warn_restart' undeclared (first use in this function) pg_strxfrm.c:98: error: (Each undeclared identifier is reported only once pg_strxfrm.c:98: error: for each function it appears in.) ---(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] Best open source tool for database design / ERDs?
Bjørn T Johansen wrote: Have you tried Druid (http://druid.sourceforge.net/index.html) ? It does anything a good ERD designer do and it's free... Thanks Bjorn. I have downloaded it but not tested it yet. I will test it in the next few days. DIA doesn't seem like a good choice. Did somebody say Druid can do forward engineering for PostgreSQL? I'm a little concerned about stepping over dollars to pick-up pennies so to speak. If Druid does about as much as the commercial diagramming products do, then I will use it. However, if there is a non-open source diagrammer that is USD $200 or less that does a lot more, or does what it does a lot better - e.g. it makes me a lot more efficient, then I would rather pay for the commercial tool. What inexpensive (~USD $200 or less) ERD tools are out there, and are they a lot more feature-rich than Druid? Thanks. Dana ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Import Data from MS SQL Server
Hi.. Im Daniel and need soem help in importing data from MS SQL Server to Postgresql DB. Is there a bulit in option for importing from Postgresql? Help me out!! Regards, Daniel. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Best open source tool for database design / ERDs?
What about OpenOffice Draw 2.0? Can this do ER / UML diagrams that one can then use to forward engineer DDL statements? How does DIA compare to Draw, and wouldn't it be better to roll DIA into OpenOffice? I love OpenOffice and use it as an MS Word / MS Excel replacement. Works great, since it can read/write MS formats. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] What are the characteristics of a good user-defined data type
List, This is a general question for my own education. Something I read earlier today triggered some ideas in my head, and out of curiosity Im researching and learning. What characteristics of data sets lend themselves to reasonable user-defined data types? Are there characteristics of a data type that would limit its usefulness? Ive reviewed the PostgreSQL documentation on user defined data types. It seems that data types that cant be ordered or compared for equality would be bad candidates. After all, if a data type cant be indexed or used in a where clause, what value does a custom type bring over a binary or textual representation? Additionally, the careful tone in the documentation regarding the definition of the comparison and equality operators suggests that these definitions may be an exceptionally delicate matter. Any experience or suggestions on the matter? Tim Hart
[GENERAL] autovacuum, xid wraparound, pg_database column values
I'm trying to make sense of Section 22.1.3 (Preventing transaction ID wraparound failures) in the on-line docs to make sure the DB's autovacuum settings are adequate and are having an effect. The docs state: The age column measures the number of transactions from the cutoff XID to the current transaction's XID. and: With the standard freezing policy, the age column will start at one billion for a freshly-vacuumed database. I don't see numbers larger than 1 billion in my pg_database columns, though. Can anyone offer any information or pointers to a good on-line explanation of vacuuming? The database in question is doing about 3M inserts/day (in about 500K transactions/day) and has been up for a little more than 3 months. The database config: [EMAIL PROTECTED]:~$ egrep autovacuum /data02/pgsql/data/postgresql.conf autovacuum = on # enable autovacuum subprocess? autovacuum_naptime = 120# time between autovacuum runs, in secs #autovacuum_vacuum_threshold = 1000 # min # of tuple updates before #autovacuum_analyze_threshold = 500 # min # of tuple updates before #autovacuum_vacuum_scale_factor = 0.4 # fraction of rel size before #autovacuum_analyze_scale_factor = 0.2 # fraction of rel size before #autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for #autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for [EMAIL PROTECTED]:~$ /usr/local/pgsql/bin/psql reporting Welcome to psql 8.1.3, 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 reporting=# select version() ; version -- PostgreSQL 8.1.3 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.0.2 20051125 (Red Hat 4.0.2-8) (1 row) reporting=# select datname, datvacuumxid, datfrozenxid from pg_database ; datname | datvacuumxid | datfrozenxid ---+--+-- postgres | 499 | 499 reporting | 499 | 499 template1 | 499 | 499 template0 | 499 | 499 (4 rows) reporting=# SELECT datname, age(datfrozenxid) FROM pg_database ; datname | age ---+-- postgres | 27995112 reporting | 27995112 template1 | 27995112 template0 | 27995112 (4 rows) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Problem with 'postgres' db with 8.0 on a MacBook
Hi all, this is my first time posting here because I've been running pgsql without problem on a couple of Linux boxes for a while now. I'm migrating to a new MacBook, and while I finally got it to compile, initdb, and start a postmaster, any time I try createdb or createuser, I get a message that the database 'postgres' cannot be found. I installed after a manual build, chown'ed /usr/local/pgsql to postgres, added a 'data' directory, and called initdb on it. But all calls to createdb or createuser gave the message above. Can anyone help? Jonathon McKitrick -- My other computer is your Windows box. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] composite data type question
Hello all. I am brand new to creating my own data types in postgres. I first created a type called indices, containing two integers. Now, I want to create another type, called progress, which should contain one timestamp and an array of indices. However, I cannot figure out how to get postgres to recognize the array of my home-brewed composite data type as a valid data type. Can I just insert a new element into the pg_type table, for a type called _indices? Any hints or advice greatly appreciated. TIA, Meghan ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Action Table of postgresql
Hi, it's possible to read the list of the tables modified during the transaction? -- __Claudio Tognolo [EMAIL PROTECTED]www.claudio.tognolo.nameL'informatica è come la fisica. La differenza è che mentre in fisica devi capire com'è fatto il mondo, in informatica sei tu a crearlo. Puoi essere un dio. Su piccola scala.(Linus Torvalds)-Fight back spam! Download the Blue Frog. http://www.bluesecurity.com/register/s?user=Y2xhdWRpby50b2dub2xvMzkzMQ%3D%3D
Re: [GENERAL] Import Data from MS SQL Server
Hi.. Im Daniel and need soem help in importing data from MS SQL Server to Postgresql DB. Is there a bulit in option for importing from Postgresql? Help me out!! You can easily use the DTS tool in SQL Server to export data to PostgreSQL through the ODBC driver. //Magnus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Import Data from MS SQL Server
You can use the DTS of SQL Server and there use the ODBC driver for PostgreSQL for making a connection to the target PostgreSQL database./ShoaibOn 7 Jun 2006 05:13:15 -0700, Daniel [EMAIL PROTECTED] wrote: Hi..Im Daniel and need soem help in importing data from MS SQL Serverto Postgresql DB. Is there a bulit in option for importing fromPostgresql? Help me out!!Regards,Daniel.---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Best open source tool for database design / ERDs?
Take a look at http://www.databaseanswers.com/modelling_tools.htm I have used Case Studio 2 (fine, weak on documentation generation), and PowerDesigner by Sybase (truly excellent! but $$$) John [EMAIL PROTECTED] wrote: Bjørn T Johansen wrote: Have you tried Druid (http://druid.sourceforge.net/index.html) ? It does anything a good ERD designer do and it's free... Thanks Bjorn. I have downloaded it but not tested it yet. I will test it in the next few days. DIA doesn't seem like a good choice. Did somebody say Druid can do forward engineering for PostgreSQL? I'm a little concerned about stepping over dollars to pick-up pennies so to speak. If Druid does about as much as the commercial diagramming products do, then I will use it. However, if there is a non-open source diagrammer that is USD $200 or less that does a lot more, or does what it does a lot better - e.g. it makes me a lot more efficient, then I would rather pay for the commercial tool. What inexpensive (~USD $200 or less) ERD tools are out there, and are they a lot more feature-rich than Druid? Thanks. Dana ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] What are the characteristics of a good user-defined data type?
List, This is a general question for my own education. Something I read earlier today triggered some ideas in my head, and out of curiosity Im researching and learning. What characteristics of data sets lend themselves to reasonable user-defined data types? Are there characteristics of a data type that would limit its usefulness? Ive reviewed the PostgreSQL documentation on user defined data types. It seems that data types that cant be ordered or compared for equality would be bad candidates. After all, if a data type cant be indexed or used in a where clause, what value does a custom type bring over a binary or textual representation? Additionally, the careful tone in the documentation regarding the definition of the comparison and equality operators suggests that these definitions may be an exceptionally delicate matter. Any experience or suggestions on the matter? Tim Hart
Re: [GENERAL] [pgsql-advocacy] Me And My Database
On Wednesday 7. June 2006 06:26, Robert Treat wrote: On Tuesday 06 June 2006 18:44, Leif B. Kristensen wrote: The reason why the generation of eg. the family sheet is faster in the MySQL web context than in my production environment, is that I'm really comparing apples and potatoes here. The Web database has a much flatter and denormalized structure, due to the fact that there's no editing. The entire Web database is repopulated from scratch every time I do an update. If you going through this kind of step now, why not just generate the whole site from the pg database as html pages and then push those out to the client? That way you eliminate any dbms overhead and reduce load on your webservers (and eliminate the need for a 2nd db schema) Ouch. The method I'm using today, is quick, easy, and works like a charm. It's one local script that runs in a few seconds, generating SQL command files which are tarred and gzipped to a 1.5MB file, and scp'ed to the server, and then a serverside load script which takes a couple of minutes. Generating 4+ static HTML pages, each of up to 10K, would fill up my disk quota faster than I can spell postgresql. And how would you write a name search for static pages? It ain't broken, and I ain't gonna fix it. -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Problem with 'postgres' db with 8.0 on a MacBook
Hi Jonathon, does the following command succeed? # psql template1 -c CREATE DATABASE test; The database 'postgres' is a system table which has been added in 8.2 (or 8.1 already, dunno). It should exist if you used the correct initdb and postmaster. What does psql -l say? And did you recheck permissions in pg_hba.conf? BTW: nice laptop, isn't it? Linux still needs some work to run it, tough. But I'm getting there ;-) Regards Markus Jonathon McKitrick wrote: Hi all, this is my first time posting here because I've been running pgsql without problem on a couple of Linux boxes for a while now. I'm migrating to a new MacBook, and while I finally got it to compile, initdb, and start a postmaster, any time I try createdb or createuser, I get a message that the database 'postgres' cannot be found. I installed after a manual build, chown'ed /usr/local/pgsql to postgres, added a 'data' directory, and called initdb on it. But all calls to createdb or createuser gave the message above. Can anyone help? Jonathon McKitrick -- My other computer is your Windows box. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Can PostGreSQL slow down a Windows PC much?
Our software will be using PostGreSQL as a database. Now I was wondering, if the database is installed on lets say an entry level Celeron, with 256MB of Ram, will it slow down the PC at all? I'm not taking any queries into account here, just generally, does installing the database slow down ones PC a bit? Unfortunately I don't have access to such an entry-level PC, so I can't test it. Thanks ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] help with rules please
May be this is not a full explanation, but at least a recepiet, that works for me: CREATE TABLE testa (x1 text, x2 text); CREATE VIEW testb AS SELECT * from testa; CREATE RULE r0 AS ON INSERT TO testb DO INSTEAD INSERT INTO testa (x1,x2) VALUES (new.x1,new.x2); CREATE RULE r1 AS ON INSERT TO testb WHERE new.x1 = 'house' DO INSTEAD INSERT INTO testa (x1,x2) VALUES (new.x1, 'mouse'); BTW: some time ago I tried the rule system at the TABLEs themselves, and I couldn't figure out how to write correct statements. With VIEWs it all work just fine. Rule #1: RULES for VIEWS :) -R On Wed, 2006-06-07 at 13:23 +0200, peter pilsl wrote: I dont succeed with writing my rules proper. I need the following rules: when I INSERT/UPDATE to a table and a certain condition is true then a special field in this data-row should be updated to. I came as far: # \d testa Table public.testa Column | Type | Modifiers +--+--- x1 | text | x2 | text | Rules: r1 AS ON INSERT TO testa WHERE new.x1 = 'house'::text DO UPDATE testa SET x2 = 'mouse'::text this works fine. When I insert a row with x1=house then x2 gets set to mouse. BUT: i) it always updates all rows in the tables instead of only the current row. I guess this is a huge performance-lack on big tables :) I tried to get a where oid=new.oid in or something like that, but it did not work. ii) the above rule does not work on UPDATE, cause I get a deep recursion. Each update causes another update on the same table which makes postgres break with a nested.loop -error (happily postgres detects the recursion :) thnx for any help peter -- -R ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] composite data type question
On Tue, Jun 06, 2006 at 11:40:05AM -0600, Jessica M Salmon wrote: I am brand new to creating my own data types in postgres. I first created a type called indices, containing two integers. Now, I want to create another type, called progress, which should contain one timestamp and an array of indices. However, I cannot figure out how to get postgres to recognize the array of my home-brewed composite data type as a valid data type. Can I just insert a new element into the pg_type table, for a type called _indices? http://www.postgresql.org/docs/8.1/interactive/arrays.html Arrays of any built-in or user-defined base type can be created. (Arrays of composite types or domains are not yet supported, however.) What are you trying to model? Have you considered other ways of representing it? -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Can PostGreSQL slow down a Windows PC much?
Our software will be using PostGreSQL as a database. Now I was wondering, if the database is installed on lets say an entry level Celeron, with 256MB of Ram, will it slow down the PC at all? I'm not taking any queries into account here, just generally, does installing the database slow down ones PC a bit? Unfortunately I don't have access to such an entry-level PC, so I can't test it. As long as there are no queries running, the effect will be almost nothing. If you're sure you don't need it, you can turn off autovacuum which will bring it even closer to zero. (But as long as your db is reasonably small, having autovacuum check now and then shouldn't be noticeable either). When you start putting a query load on it, it will show of course, depending on what queries you have. Depending on what you have set for shared memory, some RAM will be used for it, but it will likely get swapped out if there is no activity. //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: [GENERAL] autovacuum, xid wraparound, pg_database column values
[EMAIL PROTECTED] [EMAIL PROTECTED] writes: I'm trying to make sense of Section 22.1.3 (Preventing transaction ID wraparound failures) in the on-line docs to make sure the DB's autovacuum settings are adequate and are having an effect. ... I don't see numbers larger than 1 billion in my pg_database columns, though. The pg_database columns only update when you do a database-wide VACUUM, which is something that autovacuum only undertakes when it sees that the age values are getting large. I forget the exact threshold, but at ~28M transactions you are certainly a long way away. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Problem with 'postgres' db with 8.0 on a MacBook
Jonathon McKitrick [EMAIL PROTECTED] writes: I'm migrating to a new MacBook, and while I finally got it to compile, initdb, and start a postmaster, any time I try createdb or createuser, I get a message that the database 'postgres' cannot be found. It sounds to me like you're trying to use PG 8.1 client tools with an 8.0 (or older) postmaster. 8.1 tools expect there to be a 'postgres' database created by default, but that is not the convention in older releases. Better check exactly what's laying around already on that machine. Apple does ship some version of Postgres in OS X IIRC... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Action Table of postgresql
Claudio Tognolo [EMAIL PROTECTED] writes: it's possible to read the list of the tables modified during the transaction? No, because there is no such list. Depending on what you want to do, looking at your own transaction's locks in pg_locks might be a workable substitute. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Import Data from MS SQL Server
Daniel wrote: Hi.. Im Daniel and need soem help in importing data from MS SQL Server to Postgresql DB. Is there a bulit in option for importing from Postgresql? Help me out!! Regards, Daniel. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings Hi Daniel, PG Lightning Admin has some very nice import export/abilities and can import directly using the MS SQL ADO drivers that are already on your PC. Currently we only create structure automatically for Access tables, and this is only because no one has asked for MS SQL server structure creation. Basically you would just create a empty table with the fields you need, open the empty table, select import, select ADO, then follow the wizard. The wizard will allow you to map the source fields to your fields. You can also do everything else you need to do right from PGLA, no need for anything else. Check it out here: http://www.amsoftwaredesign.com/lightning_admin.php -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration ---(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] What are the characteristics of a good user-defined data type?
Tim Hart [EMAIL PROTECTED] writes: I've reviewed the PostgreSQL documentation on user defined data types. It seems that data types that can't be ordered or compared for equality would be bad candidates. After all, if a data type can't be indexed or used in a where clause, what value does a custom type bring over a binary or textual representation? Well, the possibility of error-checking for bad values might alone justify a custom type, depending on what you're doing. A type with no support beyond the required I/O functions could offer that. But it's kinda hard to imagine a datatype in which there is no meaningful way to define equality ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] pgcrypto sha256/384/512 don't work on Redhat. Please help!
If it was commited to HEAD, it will appear in 8.1.5, right? On 5/30/06, Bruce Momjian pgman@candle.pha.pa.us wrote: Patch applied to CVS HEAD and 8.1.X. Thanks. --- Marko Kreen wrote: On 5/9/06, Joe Kramer [EMAIL PROTECTED] wrote: On 5/9/06, Marko Kreen [EMAIL PROTECTED] wrote: The fact that Fedora pgcrypto is linked with OpenSSL that does not support SHA256 is not a bug, just a fact. It's not Fedora only, same problem with Gentoo/portage. I think it's problem for all distros. You need recompile pgcrypto or install openssl 0.9.8 which is considered as unstable by most distros. Maybe pgcrypto should use built-in algorithms until OpenSSL 0.9.8 is mainstream/default install. To be honest, pgcrypto actually falls back on built-in code for AES, in case old OpenSSL that does not have AES. Thats because AES should be always there, together with md5/sha1/blowfish. I do not consider SHA2 that important (yet?), so they don't get same treatment. OTOH, the nicest solution to your problem would be self-compiled pgcrypto, that would work with stock PostgreSQL. As the conflict happens with only (new) SHA2 functions, I can prepare a patch for symbol conflict, would that be satisfactory for you? Ideally, would be great if pgcrypto could fallback to built-in algorithm of OpenSSL don't support it. But since it's compile switch, completely seld-compiled pgcrypto would be great. Attached is a patch that re-defines SHA2 symbols so that they would not conflict with OpenSSL. Now that I think about it, if your OpenSSL does not contain SHA2, then there should be no conflict. But ofcourse, if someone upgrades OpenSSL, server starts crashing. So I think its best to always apply this patch. I think I'll send the patch to 8.2 later, not sure if it's important enough for 8.1. -- marko [ Attachment, skipping... ] ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Bruce Momjian http://candle.pha.pa.us 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] Some mystery with execution plans on postgres 8.1
Boguk Maxim [EMAIL PROTECTED] writes: If short: adding index to table change execution plans on query which dont use new index. Exactly which PG version is this? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] pgcrypto sha256/384/512 don't work on Redhat. Please help!
HEAD only appears in 8.2, but 8.1.X means it will appear in 8.1.5. --- Joe Kramer wrote: If it was commited to HEAD, it will appear in 8.1.5, right? On 5/30/06, Bruce Momjian pgman@candle.pha.pa.us wrote: Patch applied to CVS HEAD and 8.1.X. Thanks. --- Marko Kreen wrote: On 5/9/06, Joe Kramer [EMAIL PROTECTED] wrote: On 5/9/06, Marko Kreen [EMAIL PROTECTED] wrote: The fact that Fedora pgcrypto is linked with OpenSSL that does not support SHA256 is not a bug, just a fact. It's not Fedora only, same problem with Gentoo/portage. I think it's problem for all distros. You need recompile pgcrypto or install openssl 0.9.8 which is considered as unstable by most distros. Maybe pgcrypto should use built-in algorithms until OpenSSL 0.9.8 is mainstream/default install. To be honest, pgcrypto actually falls back on built-in code for AES, in case old OpenSSL that does not have AES. Thats because AES should be always there, together with md5/sha1/blowfish. I do not consider SHA2 that important (yet?), so they don't get same treatment. OTOH, the nicest solution to your problem would be self-compiled pgcrypto, that would work with stock PostgreSQL. As the conflict happens with only (new) SHA2 functions, I can prepare a patch for symbol conflict, would that be satisfactory for you? Ideally, would be great if pgcrypto could fallback to built-in algorithm of OpenSSL don't support it. But since it's compile switch, completely seld-compiled pgcrypto would be great. Attached is a patch that re-defines SHA2 symbols so that they would not conflict with OpenSSL. Now that I think about it, if your OpenSSL does not contain SHA2, then there should be no conflict. But ofcourse, if someone upgrades OpenSSL, server starts crashing. So I think its best to always apply this patch. I think I'll send the patch to 8.2 later, not sure if it's important enough for 8.1. -- marko [ Attachment, skipping... ] ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Bruce Momjian http://candle.pha.pa.us 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 -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(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] Backslash problems with 8.1.4
Hello list, I upgraded to postgres-8.1.4 and saw all of the backslash escape changes and understand why, but I can't figure out how to put a literal \' in the database. If \ is no longer escaping shouldn't I be able to use \” and have postgres ignore the \ and use standard sql syntax to escape the single '? It seems that no matter what I try postgres returns an error message because it thinks I'm trying to escape the '. The data is coming from PHP, and yes, I know that embedded SQL is bad, but I want to disable \ escaping now since I don't use it and it will be a little while before I can convert to PDO. Is there any way to disable \ escaping and pass a literal \' without postgres kicking back an error on the query? schu ---(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] Import Data from MS SQL Server
On 7 Jun 2006 05:13:15 -0700, Daniel [EMAIL PROTECTED] wrote: Hi.. Im Daniel and need soem help in importing data from MS SQL Server to Postgresql DB. Is there a bulit in option for importing from Postgresql? Help me out!! as others suggest, you definately want to go 'dts' on anything to/from ms sql server. dts can actually transfer from any odbc to any other odbc source. you could for example do a dts transformation from ms sql to csv files and then do csv import into postgresql via the copy command. you would still want to use dts to create the tables though. one tip: take the opportunity during the conversion process to convert your tables to all lower case and no spaces in the table/column names. Merlin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Backslash problems with 8.1.4
Matthew Schumacher [EMAIL PROTECTED] writes: I upgraded to postgres-8.1.4 and saw all of the backslash escape changes and understand why, but I can't figure out how to put a literal \' in the database. You use the SQL-standard way, which is to repeat the quote mark: 'Meet at Joe''s house' The data is coming from PHP, I have met your problem, and its name is addslashes(). Don't use it. addslashes is exactly the security hole we are trying to plug. 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] Backslash problems with 8.1.4
Tom Lane wrote: Matthew Schumacher [EMAIL PROTECTED] writes: I upgraded to postgres-8.1.4 and saw all of the backslash escape changes and understand why, but I can't figure out how to put a literal \' in the database. You use the SQL-standard way, which is to repeat the quote mark: 'Meet at Joe''s house' The data is coming from PHP, I have met your problem, and its name is addslashes(). Don't use it. addslashes is exactly the security hole we are trying to plug. regards, tom lane Thanks for the reply Tom, however I don't think you understand my issue. I'm not using addslashes and I am using the SQL standard way to escape a single quote. The problem is that I want to put a literal \' inside the database. So if \ is no longer an escape character, and '' is the SQL way to pass a literal ' then you would think that \'' would put a literal \' into the database, however postgres rejects this and spits out an error. So the question isn't how to I escape ', the question is how do I insert a literal \' into a varchar? Thanks, schu ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Backslash problems with 8.1.4
Matthew Schumacher [EMAIL PROTECTED] writes: Thanks for the reply Tom, however I don't think you understand my issue. I'm not using addslashes and I am using the SQL standard way to escape a single quote. The problem is that I want to put a literal \' inside the database. So if \ is no longer an escape character, and '' is the SQL way to pass a literal ' then you would think that \'' would put a literal \' into the database, however postgres rejects this and spits out an error. Oh, you're mistaken about \ ... it's still an escape character, as of 8.1. (Beginning in 8.2 there will be a way to make it not an escape.) So what you need for that is \\'' inside your literal string. 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] Problem with 'postgres' db with 8.0 on a MacBook
Hi Jonathon, Jonathon McKitrick wrote: : # psql template1 -c CREATE DATABASE test; CREATE DATABASE is the result. Looks good. Can you connect to that database then? : What does psql -l say? FATAL: database 'postgres' does not exist As Tom said: check if you are really calling you self-compiled binaries: # which pgsql Markus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] What are the characteristics of a good user-defined data type?
Could custom types benefit significantly from custom operators as well? Do custom C functions stand a good chance of introducing speed benefits over their raw SQL or pl/sql counterparts? Or is the field too broad to speculate on the general case? The scenario that inspired this question was about data that had to be stored accurately, but the data itself wasn't usually precise. You could think of an individual datum being a set of ranges. You could definitely define equality on this data type, but the ordering operators would probably be meaningless. On the other hand, some (but not all) of the geometric operators could probably be interpreted to apply to this data set, as long as you ignore the 'above' and 'below' semantics, and replace the concepts of left and right with less than and greater than. So for example, while (is strictly left of) Wouldn't make sense, using the same operator to mean 'strictly less than' might. Would R-tree indexes be useful for a data type like this? Would it be possible to define the base type such that an R-tree index would always be created? Once again - this is entirely idle curiosity. This isn't anything I have a real need for. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane Sent: Wednesday, June 07, 2006 9:37 AM To: Tim Hart Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] What are the characteristics of a good user-defined data type? Tim Hart [EMAIL PROTECTED] writes: I've reviewed the PostgreSQL documentation on user defined data types. It seems that data types that can't be ordered or compared for equality would be bad candidates. After all, if a data type can't be indexed or used in a where clause, what value does a custom type bring over a binary or textual representation? Well, the possibility of error-checking for bad values might alone justify a custom type, depending on what you're doing. A type with no support beyond the required I/O functions could offer that. But it's kinda hard to imagine a datatype in which there is no meaningful way to define equality ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Some mystery with execution plans on postgres 8.1
Boguk Maxim [EMAIL PROTECTED] writes: If short: adding index to table change execution plans on query which dont use new index. After some experimentation I was able to create a similar misbehavior here. I think what is happening is that the added index is capturing the bitmap scan plan, even though it ends up making that plan more expensive and thus a loser to the plain indexscan. The reason is this bug: http://archives.postgresql.org/pgsql-committers/2006-06/msg00064.php which makes choose_bitmap_and sort the available indexes in the wrong order. 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] GPL Licensed Files in 8.1.4
On Wed, May 31, 2006 at 10:50:46AM -0400, Charles Comiskey wrote: PostgreSQL 8.1.4 appears to have 2 GPL licensed files according to licenses embedded in the source. In both cases, the files have had a I should like to point out (in addition to Peter E's comments) that these are both files in contrib/. Nothing in the main code tree is GPL, as far as I know, and nobody that I know of expects a complete PostgreSQL installation to include everything in contrib/ At the same time, it strikes me that at least the userlock stuff, and maybe dbmirror as well, are candidates for pgfoundry rather than contrib/ A -- Andrew Sullivan | [EMAIL PROTECTED] Users never remark, Wow, this software may be buggy and hard to use, but at least there is a lot of code underneath. --Damien Katz ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] GPL Licensed Files in 8.1.4
Andrew Sullivan [EMAIL PROTECTED] writes: At the same time, it strikes me that at least the userlock stuff, and maybe dbmirror as well, are candidates for pgfoundry rather than contrib/ We'd already agreed to move dbmirror to pgfoundry, but it just didn't get done for 8.1. I had not thought of pgfoundry as a reasonable solution for userlock, but maybe that's the best thing to do with it. A better idea would be to contact the module authors and get them to relicense, but that might be hard. Dal Zotto at least hasn't been seen on these lists for a long time :-( regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] GPL Licensed Files in 8.1.4
Tom Lane wrote: Andrew Sullivan [EMAIL PROTECTED] writes: At the same time, it strikes me that at least the userlock stuff, and maybe dbmirror as well, are candidates for pgfoundry rather than contrib/ We'd already agreed to move dbmirror to pgfoundry, but it just didn't get done for 8.1. I had not thought of pgfoundry as a reasonable solution for userlock, but maybe that's the best thing to do with it. A better idea would be to contact the module authors and get them to relicense, but that might be hard. Dal Zotto at least hasn't been seen on these lists for a long time :-( Here is the most recent feedback we have from Massimo: http://archives.postgresql.org/pgsql-hackers/2001-08/msg01001.php Regarding the licencing of the code, I always release my code under GPL, which is the licence I prefer, but my code in the backend is obviously released under the original postgres licence. Since the module is loaded dynamically and not linked into the backend I don't see a problem here. If the licence becomes a problem I can easily change it, but I prefer the GPL if possible. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] PG Lightning Admin and Linux
PGLA now works great on Linux with WINE!!! I tested with the latest version of wine 0.9.14 and Ubuntu 6.06. Before today PGLA sort of worked on WINE, it would get AV in several locations and would raise a AV when closing. I am happy to say these AVs have been eliminated. I did some debugging and discovered that WINE, for what ever reason does not like it when Delphi changes the border style of forms at run time. The AVs where caused because PGLA uses embedded forms in a pagecontrol and at runtime I was setting the borderstyle to bsnone which just freaked out WINE, the solution was to simply set this property at design time. There were also a couple of instances where I was reusing dialog forms by embedding them in a nested pagecontrol, a example of this is the maintenance and dependencies tabs on the table editor. I solved this by using Delphi frames and two forms, one with a border and the other with no border. A Delphi frame allows the contents of a form to be reused via inheritance. It's also important that the MS True Type core fonts be installed on you Linux box or the text in the editors will look nasty. This new UNOFFICIAL support for WINE will be in build 1053 which should be available tonight. My test PC is a older AMD Athlon XP 1800+ and PGLA was very responsive and very usable, while not as fast as running natively on Windows, it is very very usable. Comments are welcome :-) No flames though :-) -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] log_duration - exactly what does it measure?
In version 7.4, could someone please tell me the start and end points are for measuring the execution time of a query when log_duration is enabled? I need to know exactly what gets measured in this time. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(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] COLLATE
On Wed, Jun 07, 2006 at 12:40:49AM +0400, Nikolay Samokhvalov wrote: I were looking forward to this feature... So many troubles in my projects would be vanished... What is 'lack of interest'? Interest from community, or major developers, or your personal one? Kind of all three, feeding off eachother. There's just not enough interest from any front to really get it moving. It's a fairly invasive change and without significant support and interest from somewhere, chances of completion let alone acceptance are pretty slim... Have a nice day -- 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] COLLATE
On 6/7/06, Martijn van Oosterhout kleptog@svana.org wrote: Kind of all three, feeding off eachother. There's just not enough interest from any front to really get it moving. It's a fairly invasive change and without significant support and interest from somewhere, chances of completion let alone acceptance are pretty slim... I don't get it. Maybe it's me or my environment. About half of the work we do in my company is building webs on the (excellent) eZ publish CMS. We routinely use MySQL even though the CMS (at least declaratively) wolks on top of pgsql. We use MySQL on inertia: it's the default db under eZ publish and it's easy to get hosting. On the other hand, I'd rather bite my hand off than use MySQL in some other Internet apps (e-shop with a card payment interface and others). Where am I going with all this? Well, if you don't live in an english-speaking country, there's no such thing as a single language web. Because of collation limitations, however, postgresql would be the first to be crossed out on my list no matter how good it is in all other respects. I understand that the needed change is uncomfortably invasive, but not beeing able to collate correctly is a show-stopping problem on a professional site. The user opens a drop-down with several dozens of cities, scrolls down a bit to where his city should be, dooesn't see it because the collator places the weird letter all the way down at the end of the list - and the user walks away. The other user can't get info about the bus lines to the city or he doesn't buy a product he want's because he doesn't see it where it's supposed to be. Another user notices the error and dissregards the site as amateurish. I understand I'm talking about a specific area of use, but isn't that where the biggest growth in both the number of new applications as well as the number of users is? As it is, I'm happy to have a great RDBMS to build intranet, rich client apps on - but that's about it. I've used MySQL and MSSQL (unfortunately) and they both support collation much better than pgsql. Not perfect, but much much better. Maybe postgresql should try to set less ambitious goals and instead of going for the holy grail of collation management (which is usualy praiseworthy) try to provide at least db-level collation definitions if table/row/cell level collation settings should proove too challenging at the moment. t.n.a. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] COLLATE
Martijn van Oosterhout kleptog@svana.org writes: On Wed, Jun 07, 2006 at 12:40:49AM +0400, Nikolay Samokhvalov wrote: What is 'lack of interest'? Interest from community, or major developers, or your personal one? Kind of all three, feeding off eachother. There's just not enough interest from any front to really get it moving. It's a fairly invasive change and without significant support and interest from somewhere, chances of completion let alone acceptance are pretty slim... I think there was also considerable concern about introducing a dependency on a very large chunk of outside software (viz, ICU). Loss of control, licensing questions, etc. Of course, doing it *without* ICU is even more daunting :-( but I'd like to think we'll get there someday. 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] What are the characteristics of a good user-defined data type?
On Wed, Jun 07, 2006 at 12:57:15PM -0500, Tim Hart wrote: Could custom types benefit significantly from custom operators as well? Yes. Do custom C functions stand a good chance of introducing speed benefits over their raw SQL or pl/sql counterparts? Or is the field too broad to speculate on the general case? Generally, it's too broad to say. Note also that programmer time is a valuable resource and CPU time is cheap. The scenario that inspired this question was about data that had to be stored accurately, but the data itself wasn't usually precise. You could think of an individual datum being a set of ranges. You could definitely define equality on this data type, but the ordering operators would probably be meaningless. Right. Just don't define a or operator, but do define an = operator on the type :) On the other hand, some (but not all) of the geometric operators could probably be interpreted to apply to this data set, as long as you ignore the 'above' and 'below' semantics, and replace the concepts of left and right with less than and greater than. So for example, while (is strictly left of) Wouldn't make sense, using the same operator to mean 'strictly less than' might. Would R-tree indexes be useful for a data type like this? Would it be possible to define the base type such that an R-tree index would always be created? Kinda depends on what you're doing. Once again - this is entirely idle curiosity. This isn't anything I have a real need for. You might some day. One of PostgreSQL's Killer Features(TM) is its radical extensibility. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Problem finding libpq.so.4 in PG 8.1.4 RH EL4
Newbie here .. RH EL4 Apache 2.0.48 Apologies if this isn't the right place for this post! Trying to get PHP5 support running in PG 8.1.4 and regardless that I have postgresql-libs-8.1.4-1PGDG.i686.rpm loaded and freshened on the machine, when I try to bring up httpd, it complains that libpq.so.4 isn't loaded. I believe it lives in the above lib - am I not defining where it lives properly maybe? (And where should a post like this go if this isn't the right group??) I don't know if this an apache or a postgres problem!) tia! Dave ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] A web based rewards management system for [EMAIL PROTECTED]
Hi All, Can you help a colleague? [EMAIL PROTECTED] He is looking for the following:- Okay here goes with a v brief requirement: + A web based rewards management system (a points scheme like mypoints, ipoints, rpoints), + Versatile content management system + community, + CRMS / email marketing system (or use a white-listed third party), + Easily skinned, + Powerful product, retailer, manufacturer directory, with - rate and reviews functionality , price comparison (eventually) + Ability to import and update large datasets easily, + Ability to serve own content and others content, maybe using xml/xsl, easily fast + All with some clever back-end functions, stats, fraud prevention … I want to skew rewards for green / ethical / fair-trade products / services and use affiliate schemes like trade-doubler CJ to get commissions which are recycled, but u get points whatever you buy (you cannot be good all o the time), but more if you buy green. You collect points and redeem for vouchers or donate to charity. Some of the commission goes to planet saving schemes. I discovered joomla recently, and I reckon you could do this through it, drupal or similar, plus stuff like oscommerce, phpbb, phplist, h2desk, for a very low initial outlay. When stumbled on the CMS Plone and remembered you talking enthusiastically about zope and python (good enuf for google is good enuf 4 me). Anyway the rewards system, which tracks links to completed transactions, is well beyond my primitive abilities. Also is my directory requirement combined with rewards actually actually just a modified shop? I don’t have a large budget, so was wondering about procuring in eastern Europe or India – am I completely mad? Any ideas? Best Regards/MfG, Bob Wheldon Mobile +49 179 5061083 Private/Privat [EMAIL PROTECTED] [EMAIL PROTECTED] begin:vcard fn:Bob Wheldon n:Wheldon;Bob email;internet:[EMAIL PROTECTED] tel;work:+49 8444 915382 tel;fax:+49 8444 915384 tel;home:+49 8444 73 09 tel;cell:+49 179 5061083 x-mozilla-html:FALSE version:2.1 end:vcard ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] A web based rewards management system for [EMAIL PROTECTED]
bob gmx wrote: Hi All, Can you help a colleague? There is something called a search engine which would help you with this. -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Problem finding libpq.so.4 in PG 8.1.4 RH EL4
Dave Felt [EMAIL PROTECTED] writes: Trying to get PHP5 support running in PG 8.1.4 and regardless that I have postgresql-libs-8.1.4-1PGDG.i686.rpm loaded and freshened on the machine, when I try to bring up httpd, it complains that libpq.so.4 isn't loaded. libpq.so.4 definitely is in that RPM. Maybe you need to run ldconfig? That's supposed to be run automagically when the RPM is installed, but maybe it got missed ... 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] Problem with 'postgres' db with 8.0 on a MacBook
One thing I have noticed with my install (may be something carrying over from an old version or howto I followed way back) is that the main user is pgsql but the default db is postgres I find I need to use #psql -U pgsql -d postgres By default psql tries to connect to the database of the same name as the pgsql user you log in as (unless specified with the -U option it will use the current system username) You may want to use '-d test' to match the test db you created with the previous instruction or another that shows in the psql -l list. On 8/6/2006 3:18, Markus Schiltknecht [EMAIL PROTECTED] wrote: Hi Jonathon, Jonathon McKitrick wrote: : # psql template1 -c CREATE DATABASE test; CREATE DATABASE is the result. Looks good. Can you connect to that database then? : What does psql -l say? FATAL: database 'postgres' does not exist As Tom said: check if you are really calling you self-compiled binaries: # which pgsql Markus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] date value not geting inserted properly
surabhi.ahuja wrote: i have a cloumn in my table which is date type i parse a file to get the date from there, and then i form a call to the stored procedure, with the above data as the parameter. but however it seems that when i form this call, something is messing up, and the value for the birth date, getting into that field is 1969-12-31 , and this happens always, it inserts this value only, whtever be the input data value. i am pasting the code that i am trying to do stmt.setObject(i + 1, (tagV.value), ((TypeAttrib) (primaryKeyMap_.get(tMap.get(temp.dataType); can u please tell as to what should the dataType be for date, isnt it 91- integer? No - a date is a date, it's completely different from an integer. Since this is java, the jdbc list should be able to help you a bit more than this one. -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] UTF8 problem
I'm using DBMail running against PostgreSQL as my mailstore for our company network. I recently converted our company database from SQL_ASCII to UTF8 as I thought this would be a *good thing*. The problem now is that I think I'm loosing emails because in my postgresql logs I get this: 2006-06-08 01:17:05 EDT LOG: unexpected EOF on client connection 2006-06-08 01:17:05 EDT ERROR: invalid byte sequence for encoding UTF8: 0xe1202c This is by far the most common, but I'm getting a few others too such as, 0xae, 0x85, 0x92 and more... The basic setup is that Postfix hands the email to a program called dbmail-smtp which parses and insert the message into the database. DBMail doesn't know anything about encoding. I tried setting the enviornment variable PGCLIENTENCODING=SQL_ASCII in the Postfix startup script, but that doesn't seem to be making any difference. Any suggestions? Thanks, Matt ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
