[GENERAL]
in a transaction i try to insert into a table1, followed by insert into table 2 then insert into table 3 and last insert into table 4. However if a unique key violation occurs in the table 1 , the whole trabnsaction aborts. is there no way , where i can ignore this violation, and continue with the remaining insertions. ?
[GENERAL] create aggregates to concatenate
i just wanted to share this with you, i wanted to do something like this for a long time but just recently found out about create aggregate reading old posts, so here it is, using user-defined aggregate functions to concatenate results. when it's numbers i usually use SUM to compute totals, but when it's text you can create your own aggregate function to concatenate: CREATE FUNCTION concat (text, text) RETURNS text AS $$ DECLARE t text; BEGIN IF character_length($1) 0 THEN t = $1 ||', '|| $2; ELSE t = $2; END IF; RETURN t; END; $$ LANGUAGE plpgsql; CREATE AGGREGATE pegar ( sfunc = concat, basetype = text, stype = text, initcond = '' ); then, for instance to list the countries names followed by the cities in those countries as a comma separated list, you can use something like (assuming you have those tables and pais is a foreign key in... etc): SELECT paises.pais, pegar(ciudad) FROM ciudades JOIN paises ON ciudades.pais=paises.pais GROUP BY paises.pais if i'm missing something or doing something wrong please let me know, this is my first aggregate function. javier wilson guegue.com ---(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] Update command too slow
How are you updating this tables? Do you use UPDATE WHERE On Fri, 4 Feb 2005 05:57:32 -0800 (PST), Venkatesh Babu [EMAIL PROTECTED] wrote: Hello, We have a table cm_quotastates which has exactly 4624564 rows and 25 columns and 9 indexes... Out of these, our code retrieves 75262 rows and modifies just one column in each row... but updating these to database is taking some significant time (around 20 minutes)... Tried the following with the update (a) Tried updating after removing all the 9 indexes associated with the table (b) Tried updating the 75K rows in batches (c) vacuum analyze the table before updation but none are helping and update still takes the same amount of time. Is there anything else that can be done so that update takes lesser time... Also, where can I find info about how postgres update actually works? Thanks, Venkatesh __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(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] Update command too slow
you mention you use one update statement by record, this may be to basic but anyway, it just happened to me... do you use the WHERE clause in your UPDATE statement, and if so is the column you use to filter indexed? javier On Sat, 5 Feb 2005 03:14:52 -0800 (PST), Venkatesh Babu [EMAIL PROTECTED] wrote: Hi, There aren't any foreign keys and we are currently using Postgres version 7.4... --- Venkatesh Babu [EMAIL PROTECTED] wrote: Hi, There aren't any triggers but there are 75262 update statements. The problem is that we have a datatype called as Collection and we are fetching the data rows into it, modifying the data and call Collection.save(). This save method generates one update satement per record present in it. Thanks, Venkatesh --- Tom Lane [EMAIL PROTECTED] wrote: Venkatesh Babu [EMAIL PROTECTED] writes: We have a table cm_quotastates which has exactly 4624564 rows and 25 columns and 9 indexes... Out of these, our code retrieves 75262 rows and modifies just one column in each row... but updating these to database is taking some significant time (around 20 minutes)... Tried the following with the update Any triggers or foreign keys on that table? Also, what PG version is this? Are you doing this in a single UPDATE command, or 75262 separate commands? regards, tom lane __ Do you Yahoo!? Yahoo! Mail - Find what you need with new enhanced search. http://info.mail.yahoo.com/mail_250 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org __ Do you Yahoo!? Yahoo! Mail - You care about security. So do we. http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(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] Sorting when * is the initial character
On Tue, 8 Feb 2005 01:10 pm, CoL wrote: hi, Berend Tober wrote, On 2/7/2005 22:20: I encountered what looks like unusually sorting behavior, and I'm wondering if anyone can tell me if this is supposted to happen (and then if so, why) or if this is a bug: SELECT * FROM sample_table ORDER BY 1; account_id,account_name 100,First account 110,Second account *115,Fifth account 120,Third account *125,Fourth account I would expect to see account_id,account_name *115,Fifth account *125,Fourth account 100,First account 110,Second account 120,Third account With 8.0.0 C local, SQL_ASCII Database, I get the expected output. Regards Russell Smith ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL]
You can use savepoints in pg 8.0: http://www.postgresql.org/docs/8.0/static/sql-savepoint.html Surabhi Ahuja wrote: in a transaction i try to insert into a table1, followed by insert into table 2 then insert into table 3 and last insert into table 4. However if a unique key violation occurs in the table 1 , the whole trabnsaction aborts. is there no way , where i can ignore this violation, and continue with the remaining insertions. ? ---(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] Update command too slow
Hello, Thanks for providing info... I tried disabling autocommit, as suggested by Mr. Greg Stark, I tried issuing the command set autocommit to off, but got the following error message: ERROR: SET AUTOCOMMIT TO OFF is no longer supported Also, I can't implement the suggestions of Mr. Christopher Browne, because I'm not working with database directly. There is an abstract layer built over the database. This abstract layer provides an interface between application objects and data tables corresponding to those objects. Our application is developed over this abstract layer. Infact, we are using Collection datatype provided by this layer. Collection is similar to java vectors in that it can store any kind of persistable objects, also it implements the save method (which updates the tables corresponding to each object present in the collection), hence one update statement generated per object present in the collection. all i can do is to play with indexes for the tables or change postgres settings. I hope the problem is clear now... Also, the suggestions of Mr. Tom Lane on transaction blocking and making use of prepared statements and indexes on primary have been taken care of i forgot to mention that even though i deleted all indexes, i ensured that the index on primary key is not deleted to give more background information, we've migrated the database from db2 to postgres things were fine in db2... is this migration having any effect on the poor performance of updates (i mean to say is this problem happening due to some improper migration???) Thanks, Venkatesh --- Tom Lane [EMAIL PROTECTED] wrote: Venkatesh Babu [EMAIL PROTECTED] writes: There aren't any triggers but there are 75262 update statements. The problem is that we have a datatype called as Collection and we are fetching the data rows into it, modifying the data and call Collection.save(). This save method generates one update satement per record present in it. Well, that's going to be dog-slow in any case compared to putting the logic on the server side, but a couple of things you could possibly do: make sure all of this is in one transaction block (a commit per row updated is a lot of overhead) and use a prepared statement for the UPDATE to get you out from under the repeated parse/plan overhead. Check the UPDATE's plan, too, and make sure it's an indexscan on the primary key rather than anything less efficient. 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 __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(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]
Title: Re: [GENERAL] cant the same be done by trapping the errors. by trapping these exceptions? http://www.postgresql.org/docs/8.0/interactive/plpgsql-control-structures.html search for "trapping errors" ? From: Jan Poslusny [mailto:[EMAIL PROTECTED]Sent: Tue 2/8/2005 3:30 PMTo: Surabhi Ahuja Cc: pgsql-general@postgresql.orgSubject: Re: [GENERAL] ***Your mail has been scanned by InterScan VirusWall.***-***You can use savepoints in pg 8.0:http://www.postgresql.org/docs/8.0/static/sql-savepoint.htmlSurabhi Ahuja wrote: in a transaction i try to insert into a table1, followed by insert into table 2 then insert into table 3 and last insert into table 4. However if a unique key violation occurs in the table 1 , the whole trabnsaction aborts. is there no way , where i can ignore this violation, and continue with the remaining insertions. ?
Re: [GENERAL]
If you want to realize your insert chain in plpgsql, trapping exceptions is a good idea, imho. But I am not experienced with these new features in pg 8.0 ... Surabhi Ahuja wrote: cant the same be done by trapping the errors. by trapping these exceptions? http://www.postgresql.org/docs/8.0/interactive/plpgsql-control-structures.html search for trapping errors ? *From:* Jan Poslusny [mailto:[EMAIL PROTECTED] *Sent:* Tue 2/8/2005 3:30 PM *To:* Surabhi Ahuja *Cc:* pgsql-general@postgresql.org *Subject:* Re: [GENERAL] *** Your mail has been scanned by InterScan VirusWall. ***-*** You can use savepoints in pg 8.0: http://www.postgresql.org/docs/8.0/static/sql-savepoint.html Surabhi Ahuja wrote: in a transaction i try to insert into a table1, followed by insert into table 2 then insert into table 3 and last insert into table 4. However if a unique key violation occurs in the table 1 , the whole trabnsaction aborts. is there no way , where i can ignore this violation, and continue with the remaining insertions. ? ---(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] indexing just a part of a string
I'm new to PostgreSQL, and it has been a pleasure so far. There's just one thing I'm trying to do and I didn't find any hints in the manual: I want to index just a part of a string in a column. The situation: I have roughly 300.000 rows, and in the column we're looking at, most of the entries have less than 200 chars. However, there are some (very few) that have more than 3000 chars, and postmaster relplies that this is too many for the index (b-tree). So I would like to say 'index only the first 200 chars of the column', which will result in a full index of 99.9 % of my entries. I did this in MySQL, but I didn't find it in the pg manual. How do I proceed? best regards, and TIA Christoph Pingel ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] External Projects in the PostgreSQL release
On Mon, Feb 07, 2005 at 02:10:32PM -0800, Noah Friedland wrote: Hi! I was wondering if any external projects get bundled into the Postgres release, e.g. JDBC, etc. I'd like to get a better sense of the process. How are decisions made as to which external projects to bundle, and who is responsible for the quality/interoperability of those projects with the core? I'm sure other people will correct me, but AFAIK there has been an effort recently to remove things from the main release that are not actually maintained by the PostgreSQL core. So any project that has its own developers and timeline is generally distributed by that project. The PostgreSQL team releases: PostgreSQL. Hope this helps, -- Martijn van Oosterhout kleptog@svana.org 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. pgpxnB35sGZyg.pgp Description: PGP signature
Re: [GENERAL] Out of memory error
I did: # /sbin/sysctl -w vm.overcommit_memory=2 following http://www.postgresql.org/docs/7.4/static/kernel-resources.html#AEN17068 And got the same error: ERROR: out of memory DETAIL: Failed on request of size 44. CONTEXT: PL/pgSQL function group_dup line 9 at SQL statement The difference now is that the process was killed before overcommiting. Regards, Clodoaldo Pinto ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL]
Title: RE: [GENERAL] You would need to use EXCEPTION, to trap the error. Kind Regards, Shaun Clements -Original Message- From: Jan Poslusny [mailto:pajout@gingerall.cz] Sent: 08 February 2005 12:01 PM To: Surabhi Ahuja Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] You can use savepoints in pg 8.0: http://www.postgresql.org/docs/8.0/static/sql-savepoint.html Surabhi Ahuja wrote: in a transaction i try to insert into a table1, followed by insert into table 2 then insert into table 3 and last insert into table 4. However if a unique key violation occurs in the table 1 , the whole trabnsaction aborts. is there no way , where i can ignore this violation, and continue with the remaining insertions. ? ---(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 Subject to www.relyant.co.za/edisclaim.htm
Re: [GENERAL] Out of memory error
On Tue, 8 Feb 2005 09:06:38 -0200, Clodoaldo Pinto [EMAIL PROTECTED] wrote: I did: # /sbin/sysctl -w vm.overcommit_memory=2 following http://www.postgresql.org/docs/7.4/static/kernel-resources.html#AEN17068 And got the same error: ERROR: out of memory DETAIL: Failed on request of size 44. CONTEXT: PL/pgSQL function group_dup line 9 at SQL statement The difference now is that the process was killed before overcommiting. Regards, Clodoaldo Pinto This is the log file content: TopMemoryContext: 32768 total in 3 blocks; 3720 free (1 chunks); 29048 used TopTransactionContext: 8192 total in 1 blocks; 2432 free (0 chunks); 5760 used SPI Exec: 8192 total in 1 blocks; 8064 free (0 chunks); 128 used ExecutorState: 8192 total in 1 blocks; 5352 free (1 chunks); 2840 used ExecutorState: 35643416 total in 14 blocks; 3999744 free (16 chunks); 31643672 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used AggContext: 8192 total in 1 blocks; 8128 free (1 chunks); 64 used ExprContext: 8192 total in 1 blocks; 8128 free (0 chunks); 64 used ExprContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used SPI Proc: 8192 total in 1 blocks; 7736 free (0 chunks); 456 used SPI TupTable: 8192 total in 1 blocks; 7024 free (0 chunks); 1168 used SPI TupTable: 8192 total in 1 blocks; 3832 free (0 chunks); 4360 used DeferredTriggerXact: 1796202496 total in 224 blocks; 3752 free (10 chunks); 1796198744 used SPI Plan: 7168 total in 3 blocks; 32 free (0 chunks); 7136 used SPI Plan: 3072 total in 2 blocks; 1280 free (0 chunks); 1792 used SPI Plan: 1024 total in 1 blocks; 672 free (0 chunks); 352 used MessageContext: 8192 total in 1 blocks; 6696 free (1 chunks); 1496 used PortalMemory: 8192 total in 1 blocks; 7904 free (0 chunks); 288 used PortalHeapMemory: 3072 total in 2 blocks; 1272 free (0 chunks); 1800 used ExecutorState: 8192 total in 1 blocks; 6440 free (1 chunks); 1752 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used Unique: 0 total in 0 blocks; 0 free (0 chunks); 0 used PortalHeapMemory: 1024 total in 1 blocks; 912 free (0 chunks); 112 used ExecutorState: 8192 total in 1 blocks; 7064 free (1 chunks); 1128 used ExprContext: 8192 total in 1 blocks; 8176 free (4 chunks); 16 used CacheMemoryContext: 516096 total in 6 blocks; 130408 free (19 chunks); 385688 used ndx_usuarios_data: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_description_o_c_o_index: 2048 total in 1 blocks; 768 free (0 chunks); 1280 used pg_depend_depender_index: 2048 total in 1 blocks; 768 free (0 chunks); 1280 used pg_depend_reference_index: 2048 total in 1 blocks; 768 free (0 chunks); 1280 used pg_database_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_database_datname_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_index_indrelid_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_shadow_usename_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_amop_opr_opc_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used pg_conversion_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_language_name_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_statistic_relid_att_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_shadow_usesysid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_cast_source_target_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used pg_conversion_name_nsp_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_namespace_nspname_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_conversion_default_index: 2048 total in 1 blocks; 704 free (0 chunks); 1344 used pg_class_relname_nsp_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used pg_aggregate_fnoid_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_language_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_type_typname_nsp_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used pg_group_sysid_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_proc_proname_args_nsp_index: 2048 total in 1 blocks; 704 free (0 chunks); 1344 used pg_opclass_am_name_nsp_index: 2048 total in 1 blocks; 768 free (0 chunks); 1280 used pg_group_name_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_proc_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_operator_oid_index: 1024 total in 1 blocks; 640
Re: [GENERAL] indexing just a part of a string
Christoph Pingel wrote: So I would like to say 'index only the first 200 chars of the column', which will result in a full index of 99.9 % of my entries. I did this in MySQL, but I didn't find it in the pg manual. How do I proceed? You could do: CREATE INDEX index name ON table name (SUBSTRING(column name, 1, 200)) But that may cause the index to be used only if you query for results using SUBSTRING(). I don't know; You can test if it uses an index scan using EXPLAIN. You could also use separate indices for the short and the long string variants, or maybe you could use a column that's better suited to the task (for example, a column with an MD5 hash of the text or an integer based on a sequence). You could also try a different type of index, an ltree (contrib) for example. It all pretty much depends on what you're trying to do. In any case, you should take a look at the documentation for CREATE INDEX, there are possibilities. Out of general curiosity: I mentioned using a hashed column as a possible solution. Would that be equivalent to using a hash index? Or is searching a hash value in a btree index actually faster than in a hash index? -- Alban Hertroys MAG Productions T: +31(0)53 4346874 F: +31(0)53 4346876 E: [EMAIL PROTECTED] W: http://www.magproductions.nl ---(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] Safely Killing Backends (Was: Applications that leak connections)
Your application should handle failures in the middle of a transaction, connection failures included, in a graceful but correct way. It does very well, until the next bug is discovered. I see your point (being able to safely shut a connection down on the server side), but it\'s at the _bottom_ of any list. .TM. -- / / / / / /Marco Colombo That\'s unfortunate. I\'ve tried to explain my position off list to Marco, but it really isn\'t worth debating. FWIW I think this thread was started by someone with application issues. The fact is, such things happen. Unfortunately Marco choses speaks for any list and I\'ll just repeat that I find this instability issue the most significant drawback for Postgres installations. This doesn\'t mean that there aren\'t other areas of priority for other users. And no, I do not want to debate the meaning of the word instability. :-) Best regards, Jim Wilson ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Confused by to_char
I am am trying to get a day string from a date using to_char ie:
SELECT date1,ti1 ,to1,ti2,to2,adj,ei,eo,to_char('2005-02-07','Day') FROM
vw_times_list1
however I get
function to_char(unknown, unknown) is not unique
(using to_date does not recognise the date format)
anyone any ideas what I am doing wrong
---(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] Confused by to_char
On Tue, 2005-02-08 at 12:28 +, mike wrote:
I am am trying to get a day string from a date using to_char ie:
SELECT date1,ti1 ,to1,ti2,to2,adj,ei,eo,to_char('2005-02-07','Day') FROM
vw_times_list1
however I get
function to_char(unknown, unknown) is not unique
test=# select to_char('2005-02-07'::date,'Day');
to_char
---
Monday
(1 row)
gnari
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] Confused by to_char
On Tue, Feb 08, 2005 at 12:28:26PM +, mike wrote:
I am am trying to get a day string from a date using to_char ie:
SELECT date1,ti1 ,to1,ti2,to2,adj,ei,eo,to_char('2005-02-07','Day') FROM
vw_times_list1
however I get
function to_char(unknown, unknown) is not unique
(using to_date does not recognise the date format)
anyone any ideas what I am doing wrong
Yes, in a way.
to_char needs two parameters, a pattern and a variable to format
according to the pattern. You've given it a pattern it doesn't
recognise and a constant string which doesn't look like a date.
It needs to look something like:-
to_char(date_time, 'MMDDHH24MISS')
This is an Oracle example so I'm not sure if the pattern is exactly
right but it'll look something like this. Look at the documentation
for to_char() for the format of the pattern. date_time is a date
column in your database.
--
Chris Green ([EMAIL PROTECTED])
Never ascribe to malice that which can be explained by incompetence.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Update command too slow
Hi, The where clause is used in update statements and the column present in the where clause is indexed... but still updates are slow. Thanks, Venkatesh --- guegue [EMAIL PROTECTED] wrote: you mention you use one update statement by record, this may be to basic but anyway, it just happened to me... do you use the WHERE clause in your UPDATE statement, and if so is the column you use to filter indexed? javier On Sat, 5 Feb 2005 03:14:52 -0800 (PST), Venkatesh Babu [EMAIL PROTECTED] wrote: Hi, There aren't any foreign keys and we are currently using Postgres version 7.4... --- Venkatesh Babu [EMAIL PROTECTED] wrote: Hi, There aren't any triggers but there are 75262 update statements. The problem is that we have a datatype called as Collection and we are fetching the data rows into it, modifying the data and call Collection.save(). This save method generates one update satement per record present in it. Thanks, Venkatesh --- Tom Lane [EMAIL PROTECTED] wrote: Venkatesh Babu [EMAIL PROTECTED] writes: We have a table cm_quotastates which has exactly 4624564 rows and 25 columns and 9 indexes... Out of these, our code retrieves 75262 rows and modifies just one column in each row... but updating these to database is taking some significant time (around 20 minutes)... Tried the following with the update Any triggers or foreign keys on that table? Also, what PG version is this? Are you doing this in a single UPDATE command, or 75262 separate commands? regards, tom lane __ Do you Yahoo!? Yahoo! Mail - Find what you need with new enhanced search. http://info.mail.yahoo.com/mail_250 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org __ Do you Yahoo!? Yahoo! Mail - You care about security. So do we. http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(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 __ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Performance tuning using copy
Hi, I have to know why does copy commands work faster as compared to the insert commands. Thething is that i have a lot of constraints and triggers.I know insert will check all the triggers and constraints, but i wonder if copy will also do it and then if it does then this has also to be slow. But copy loads the database very fast. Can u tell me why. I also have a concern about the performace tuninig while updating the database. Can some one suggest me to tune in more than the use of copy command. I have tried to even disable the triggers and constraints but what I get is only minimal gain. Is there any other mechanism by which we can do faster updations. Regards, Sid Do you Yahoo!? Yahoo! Search presents - Jib Jab's 'Second Term'
Re: [GENERAL] Creating an index-type for LIKE '%value%'
On Tue, 8 Feb 2005, Oleg Bartunov wrote: On Mon, 7 Feb 2005, Larry Rosenman wrote: Oleg Bartunov wrote: Larry, I pointed you to pg_trgm module mostly following Martijn's suggestions. Now, I see you need another our module - ltree, see http://www.sai.msu.su/~megera/postgres/gist/ltree/ for details. I maybe dense, but could you give me an example? I'm not seeing it for some reason :). Thanks, LER -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: ler@lerctr.org US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Update command too slow
Venkatesh Babu [EMAIL PROTECTED] writes: Hello, Thanks for providing info... I tried disabling autocommit, as suggested by Mr. Greg Stark, I tried issuing the command set autocommit to off, but got the following error message: ERROR: SET AUTOCOMMIT TO OFF is no longer supported Autocommit is handled by the drivers now. Also, I can't implement the suggestions of Mr. Christopher Browne, because I'm not working with database directly. There is an abstract layer built over the database. This abstract layer provides an interface between application objects and data tables corresponding to those objects. Our application is developed over this abstract layer. Infact, we are using Collection datatype provided by this layer. Collection is similar to java vectors in that it can store any kind of persistable objects, also it implements the save method (which updates the tables corresponding to each object present in the collection), hence one update statement generated per object present in the collection. Sounds like Hibernate--is that what you're using? Make sure you use your mapping library's transaction mechanism to execute the save() inside a transaction and you may get get some speedup. -Doug ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Safely Killing Backends (Was: Applications that leak connections)
On Tue, 8 Feb 2005, Jim Wilson wrote: Your application should handle failures in the middle of a transaction, connection failures included, in a graceful but correct way. It does very well, until the next bug is discovered. I see your point (being able to safely shut a connection down on the server side), but it\'s at the _bottom_ of any list. .TM. -- / / / / / / Marco Colombo That\'s unfortunate. I\'ve tried to explain my position off list to Marco, but it really isn\'t worth debating. FWIW I think this thread was started by someone with application issues. The fact is, such things happen. Unfortunately Marco choses speaks for any list and I\'ll just repeat that I find this instability issue the most significant drawback for Postgres installations. This doesn\'t mean that there aren\'t other areas of priority for other users. And no, I do not want to debate the meaning of the word instability. :-) Best regards, Jim Wilson As I wrote in private mail, authenticated clients have many means to perform a DoS attack (whether intentionally or not). Most of cases can be handled only with a server restart. To put simply, PostgreSQL is not designed to handle hostile clients well. IMHO, a friendly enviroment (client behaviour) is a safe assumption for a RDBMS. It's not its job to paperbag over application bugs. Anyway, I agree in ending this thread. I recognize we have different meanings for instability and data loss. .TM. -- / / / / / / Marco Colombo ___/ ___ / / Technical Manager / / / ESI s.r.l. _/ _/ _/ [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Safely Killing Backends (Was: Applications that leak connections)
On Tue, Feb 08, 2005 at 07:31:13AM -0500, Jim Wilson wrote: That\'s unfortunate. I\'ve tried to explain my position off list to Marco, but it really isn\'t worth debating. FWIW I think this thread was started by someone with application issues. The fact is, such things happen. Well, I read the thread on pg-hackers [1] about this being a bad idea currently and the issue seems to be: 1. The SIGTERM is the same as a FATAL error and this code path has not been very well tested. Are locks, etc all correctly removed? The only cases that *are* well tested are cases where these things don't matter. In other words, it will probably work fine, but it's not so well tested that the pg hackers are willing to bless a backend function implementing it. 2. If the backend is so stuck that SIGTERM isn't working, then I guess that's a bug but not enough examples have been collected to work out the problem. In this case you probably can't exit without considering the shared memory corrupt. 3. In theory it would be nice to have a cancel then exit signal, but we're clean out of signal numbers. 4. It appears the original person had a problem with not tracking used resources properly in a language that neither garbage-collects nor reference-counts. If you know you only ever want to open one connection you can solve this problem by creating an open_connection function which checks a global variable to see if a connection has already been opened and returns the same one if it has. Unfortunately Marco choses speaks for any list and I\'ll just repeat that I find this instability issue the most significant drawback for Postgres installations. This doesn\'t mean that there aren\'t other areas of priority for other users. And no, I do not want to debate the meaning of the word instability. :-) I guess it appears on the list of anybody who regularly deals with this problem. That list appears to be mutally exclusive with anyone who can fix it... I wonder how one would test the SIGTERM path anyway... To quote Tom Lane on chances of corruption [2]: Not only wouldn't I give you those odds today, but I don't think we could ever get to the point of saying that session kill is that reliable, at least not from our ordinary methods of field testing. It'd require significant focused code review and testing to acquire such confidence, and continuing effort to make sure we didn't break it again in the future. If we had infinite manpower I'd be happy to delegate a developer or three to stay on top of this particular issue. But we don't :-( I don't know if PostgreSQL has ever had the concept of bounties for stuff. It's an interesting idea... [1] http://archives.postgresql.org/pgsql-patches/2004-07/msg00457.php [2] http://archives.postgresql.org/pgsql-patches/2004-07/msg00480.php Hope this helps, -- Martijn van Oosterhout kleptog@svana.org 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. pgpNodMPuJQ9u.pgp Description: PGP signature
Re: [GENERAL] Creating an index-type for LIKE '%value%'
On Tue, 8 Feb 2005, Larry Rosenman wrote: On Tue, 8 Feb 2005, Oleg Bartunov wrote: On Mon, 7 Feb 2005, Larry Rosenman wrote: Oleg Bartunov wrote: Larry, I pointed you to pg_trgm module mostly following Martijn's suggestions. Now, I see you need another our module - ltree, see http://www.sai.msu.su/~megera/postgres/gist/ltree/ for details. I maybe dense, but could you give me an example? test=# \d tt Table public.tt Column | Type | Modifiers +---+--- domain | ltree | Indexes: ltree_idx gist (domain) test=# select * from tt where domain ~ '*.ru'::lquery; domain - astronet.ru mail.ru pgsql.ru (3 rows) I'm not seeing it for some reason :). Thanks, LER Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(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] indexing just a part of a string
You can use a functional index. Something like CREATE INDEX foo ON bar (substring(blah,1,200)) Should work I think. Ian Harding Programmer/Analyst II Tacoma-Pierce County Health Department [EMAIL PROTECTED] Phone: (253) 798-3549 Pager: (253) 754-0002 Christoph Pingel [EMAIL PROTECTED] 02/08/05 2:50 AM I'm new to PostgreSQL, and it has been a pleasure so far. There's just one thing I'm trying to do and I didn't find any hints in the manual: I want to index just a part of a string in a column. The situation: I have roughly 300.000 rows, and in the column we're looking at, most of the entries have less than 200 chars. However, there are some (very few) that have more than 3000 chars, and postmaster relplies that this is too many for the index (b-tree). So I would like to say 'index only the first 200 chars of the column', which will result in a full index of 99.9 % of my entries. I did this in MySQL, but I didn't find it in the pg manual. How do I proceed? best regards, and TIA Christoph Pingel ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(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] Performance tuning using copy
On Tue, Feb 08, 2005 at 05:15:55AM -0800, sid tow wrote: Hi, I have to know why does copy commands work faster as compared to the insert commands. The thing is that i have a lot of constraints and triggers. I know insert will check all the triggers and constraints, but i wonder if copy will also do it and then if it does then this has also to be slow. But copy loads the database very fast. Can u tell me why. Easy, because each INSERT statement has to be sent to the backend, parsed, planned, and executed. The result is then sent to the client, which then sends the next query. Talk about overhead. On my machine I get a minimum of 0.65ms for an insert. In contrast, COPY does one thing and does it well. Once started, a copy has no planning overhead. The only thing that needs to happen is convert each string element into the right data type. There is no response to the client until the copy is complete. So your load speed is limited only by fast you can transfer data. I also have a concern about the performace tuninig while updating the database. Can some one suggest me to tune in more than the use of copy command. I have tried to even disable the triggers and constraints but what I get is only minimal gain. Is there any other mechanism by which we can do faster updations. Run EXPLAIN ANALYZE on the queries you do often and check they are being executed optimally. Hope this helps, -- Martijn van Oosterhout kleptog@svana.org 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. pgppZVjoU921B.pgp Description: PGP signature
Re: [GENERAL] Problem performing a restore of a data schema in Wi
-Original Message- From: John DeSoi [mailto:[EMAIL PROTECTED] Sent: 07 February 2005 04:21 PM To: Shaun Clements Cc: 'PgSql General' Subject: Re: [GENERAL] Problem performing a restore of a data schema in Windows On Feb 7, 2005, at 8:22 AM, Shaun Clements wrote: psql -U username -d db1 filename.dm You're going the wrong way. Try either psql -U username -d db1 filename.dm or cat filename.dm | psql -U username -d db1 or psql -f filename.dm -U username -d db1 ---(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] Creating an index-type for LIKE '%value%'
Oleg Bartunov wrote:
On Tue, 8 Feb 2005, Larry Rosenman wrote:
On Tue, 8 Feb 2005, Oleg Bartunov wrote:
On Mon, 7 Feb 2005, Larry Rosenman wrote:
Oleg Bartunov wrote:
Larry, I pointed you to pg_trgm module mostly following Martijn's
suggestions. Now, I see you need another our module - ltree, see
http://www.sai.msu.su/~megera/postgres/gist/ltree/
for details.
I maybe dense, but could you give me an example?
test=# \d tt
Table public.tt
Column | Type | Modifiers
+---+---
domain | ltree |
Indexes:
ltree_idx gist (domain)
test=# select * from tt where domain ~ '*.ru'::lquery;
domain
-
astronet.ru
mail.ru
pgsql.ru
(3 rows)
I'm not seeing it for some reason :).
Thanks,
LER
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
It doesn't seem to like pieces with hyphens ('-') in the name, when I try
To update blacklist set new_domain_lt=text2ltree(domain) I get a
Syntax error (apparently for the hyphens).
--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Sorting when '*' is the initial character - solved
On Tue, 8 Feb 2005 01:10 pm, CoL wrote: hi, Berend Tober wrote, On 2/7/2005 22:20: I encountered what looks like unusually sorting behavior, and I'm wondering if anyone can tell me if this is supposted to happen (and then if so, why) or if this is a bug: -- With 8.0.0 C local, SQL_ASCII Database, I get the expected output. Russell Smith -- order by case when account_id like '*%' then 0 else 1 end C. Thanks. It was pointed out to me that this behavior is normal and is dependent on the locale setting. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Help with sorting (ie. ORDER BY expression)
This can be easily done with pl/pgsql, visit the documentation at http://www.postgresql.org/docs/7.3/interactive/programmer-pl.html OT: seems like this is a questionnaire/survey application, yes? - - Jonel Rienton FWIW, given the signature: Reuben D. Budiardja, Dept. Physics and Astronomy he's probably trying to utilize a data base to build an inventory of test questions for the students he is responsible for actually teaching. I am running postgres-7.3. I have a query like this: SELECT question_id, question_text FROM quiz_table WHERE question_id IN (2,10,3,6,4,5); But I want the output to be sorted in the way I give the question_id, something like: SELECT question_id, question_text FROM quiz_table WHERE question_id IN (2,10,3,6,4,5) ORDER BY question_id (2,10,3,6,4,5) Is there any way I can do that, so that the output of the query is question_id, text 2... 10 3 6 4 5 Your understanding of the ORDER BY clause is off. My approach would be to add a column quiz_item_list_order, type integer, and explicity specify the rank order in which you want questions to be returned. SELECT quiz_item_list_order, question_id, question_text FROM quiz_table WHERE question_id IN (2,10,3,6,4,5) ORDER BY quiz_item_list_order; -- BMT ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] [COMMITTERS] How I can add new function writing on C under Win32
deeps1 wrote: Hello pgsql-committers, How I can compiling and linking Dynamically-Loaded Functions on Win32? In Docs describe all platforms BSD , FreeBSD, Linux for example The compiler flag to create PIC is -fpic. On some platforms in some situations -fPIC must be used if -fpic does not work. Refer to the GCC manual for more information. The compiler flag to create a shared library is -shared. A complete example looks like this: cc -fpic -c foo.c cc -shared -o foo.so foo.o but no describe under Win32 for Postges 8.0.1 under Win32 [ Moved to 'general'.] Take a look at how the regression tests link regress.so using the MinGW tools and that will show the flags to use. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (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]
Re: [GENERAL] create aggregates to concatenate
i just wanted to share this with you, i wanted to do something like this for a long time but just recently found out about create aggregate reading old posts, so here it is, using user-defined aggregate functions to concatenate results. when it's numbers i usually use SUM to compute totals, but when it's text you can create your own aggregate function to concatenate: CREATE FUNCTION concat (text, text) RETURNS text AS $$ DECLARE t text; BEGIN IF character_length($1) 0 THEN t = $1 ||', '|| $2; ELSE t = $2; END IF; RETURN t; END; $$ LANGUAGE plpgsql; CREATE AGGREGATE pegar ( sfunc = concat, basetype = text, stype = text, initcond = '' ); then, for instance to list the countries names followed by the cities in those countries as a comma separated list, you can use something like (assuming you have those tables and pais is a foreign key in... etc): SELECT paises.pais, pegar(ciudad) FROM ciudades JOIN paises ON ciudades.pais=paises.pais GROUP BY paises.pais if i'm missing something or doing something wrong please let me know, this is my first aggregate function. And, while somewhat off-topic but in a similar vein, although the following goes against the SQL standard so dearly held to by the Postgresql team, I found it useful in some cirumstances to circumvent the handling of NULL's in text columns with CREATE OR REPLACE FUNCTION public.textcat_null(text, text) RETURNS text AS ' SELECT textcat(COALESCE($1, \'\'), COALESCE($2, \'\')); ' LANGUAGE 'sql' VOLATILE; CREATE OPERATOR public.||( PROCEDURE = public.textcat_null, LEFTARG = text, RIGHTARG = text); ---(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] Creating an index-type for LIKE '%value%'
On Tue, 8 Feb 2005, Larry Rosenman wrote:
It doesn't seem to like pieces with hyphens ('-') in the name, when I try
To update blacklist set new_domain_lt=text2ltree(domain) I get a
Syntax error (apparently for the hyphens).
Try change definition of ISALNUM on ltree.h
#define ISALNUM(x) ( isalnum((unsigned char)(x)) || (x) == '_' )
this was already discussed
http://www.pgsql.ru/db/mw/msg.html?mid=2034299
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] create aggregates to concatenate
i just wanted to share this with you, i wanted to do something like
this for a long time but just recently found out about create
aggregate reading old posts, so here it is, using user-defined
aggregate functions to concatenate results.
when it's numbers i usually use SUM to compute totals, but when it's
text you can create your own aggregate function to concatenate:
CREATE FUNCTION concat (text, text) RETURNS text AS $$
DECLARE
t text;
BEGIN
IF character_length($1) 0 THEN
t = $1 ||', '|| $2;
ELSE
t = $2;
END IF;
RETURN t;
END;
$$ LANGUAGE plpgsql;
CREATE AGGREGATE pegar (
sfunc = concat,
basetype = text,
stype = text,
initcond = ''
);
then, for instance to list the countries names followed by the cities
in those countries as a comma separated list, you can use something
like (assuming you have those tables and pais is a foreign key in...
etc):
SELECT paises.pais, pegar(ciudad) FROM ciudades JOIN paises ON
ciudades.pais=paises.pais GROUP BY paises.pais
if i'm missing something or doing something wrong please let me know,
this is my first aggregate function.
And, while somewhat off-topic but in a similar vein, although the following
goes against the SQL standard so dearly held to by the Postgresql team, I
found it useful in some cirumstances to circumvent the handling of NULL's in
text columns with
CREATE OR REPLACE FUNCTION public.textcat_null(text, text)
RETURNS text AS
'
SELECT textcat(COALESCE($1, \'\'), COALESCE($2, \'\'));
'
LANGUAGE 'sql' VOLATILE;
CREATE OPERATOR public.||(
PROCEDURE = public.textcat_null,
LEFTARG = text,
RIGHTARG = text);
Slightly less off-topic:
-- Try this
CREATE TABLE country (country_name varchar(64) NOT NULL);
INSERT INTO country VALUES ('Afghanistan');
INSERT INTO country VALUES ('Albania');
INSERT INTO country VALUES ('Algeria');
INSERT INTO country VALUES ('Andorra');
INSERT INTO country VALUES ('Angola');
INSERT INTO country VALUES ('Anguilla');
INSERT INTO country VALUES ('Argentina');
INSERT INTO country VALUES ('Armenia');
INSERT INTO country VALUES ('Aruba');
INSERT INTO country VALUES ('Ascension');
INSERT INTO country VALUES ('Australia');
INSERT INTO country VALUES ('Austria');
-- ... etc., etc.
CREATE AGGREGATE concat (
BASETYPE = text,
SFUNC = textcat,
STYPE = text,
INITCOND = ''
);
SELECT TRIM(', ' FROM (SELECT CONCAT(country_name||', ') FROM COUNTRY));
-- to get a comma-separated list of country names.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Creating an index-type for LIKE '%value%'
Oleg Bartunov wrote:
On Tue, 8 Feb 2005, Larry Rosenman wrote:
It doesn't seem to like pieces with hyphens ('-') in the name, when I
try To update blacklist set new_domain_lt=text2ltree(domain) I get a
Syntax error (apparently for the hyphens).
Try change definition of ISALNUM on ltree.h
#define ISALNUM(x) ( isalnum((unsigned char)(x)) || (x) == '_' )
this was already discussed
http://www.pgsql.ru/db/mw/msg.html?mid=2034299
Thanks!
Now, how can I make it always case-insensitive?
--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
---(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] Creating an index-type for LIKE '%value%'
On Tue, 8 Feb 2005, Larry Rosenman wrote:
Oleg Bartunov wrote:
On Tue, 8 Feb 2005, Larry Rosenman wrote:
It doesn't seem to like pieces with hyphens ('-') in the name, when I
try To update blacklist set new_domain_lt=text2ltree(domain) I get a
Syntax error (apparently for the hyphens).
Try change definition of ISALNUM on ltree.h
#define ISALNUM(x) ( isalnum((unsigned char)(x)) || (x) == '_' )
this was already discussed
http://www.pgsql.ru/db/mw/msg.html?mid=2034299
Thanks!
Now, how can I make it always case-insensitive?
from http://www.sai.msu.su/~megera/postgres/gist/ltree/
It is possible to use several modifiers at the end of a label:
@ Do case-insensitive label matching
* Do prefix matching for a label
% Don't account word separator '_' in label matching, that is
'Russian%' would match 'Russian_nations', but not 'Russian'
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Creating an index-type for LIKE '%value%'
Oleg Bartunov wrote:
On Tue, 8 Feb 2005, Larry Rosenman wrote:
Oleg Bartunov wrote:
On Tue, 8 Feb 2005, Larry Rosenman wrote:
It doesn't seem to like pieces with hyphens ('-') in the name, when
I try To update blacklist set new_domain_lt=text2ltree(domain) I
get a Syntax error (apparently for the hyphens).
Try change definition of ISALNUM on ltree.h
#define ISALNUM(x) ( isalnum((unsigned char)(x)) || (x) == '_'
)
this was already discussed
http://www.pgsql.ru/db/mw/msg.html?mid=2034299
Thanks!
Now, how can I make it always case-insensitive?
from http://www.sai.msu.su/~megera/postgres/gist/ltree/
It is possible to use several modifiers at the end of a label:
@ Do case-insensitive label matching
* Do prefix matching for a label
% Don't account word separator '_' in label matching,
that is 'Russian%' would match 'Russian_nations',
but not 'Russian'
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg
Astronomical Institute, Moscow University (Russia) Internet:
oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
Does that apply to each node, or the entire string?
I'd like to not have to parse the lquery string and make each node following
it with an @.
LER
--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] Creating an index-type for LIKE '%value%'
On Tue, 8 Feb 2005, Larry Rosenman wrote:
Oleg Bartunov wrote:
On Tue, 8 Feb 2005, Larry Rosenman wrote:
Oleg Bartunov wrote:
On Tue, 8 Feb 2005, Larry Rosenman wrote:
It doesn't seem to like pieces with hyphens ('-') in the name, when
I try To update blacklist set new_domain_lt=text2ltree(domain) I
get a Syntax error (apparently for the hyphens).
Try change definition of ISALNUM on ltree.h
#define ISALNUM(x) ( isalnum((unsigned char)(x)) || (x) == '_'
)
this was already discussed
http://www.pgsql.ru/db/mw/msg.html?mid=2034299
Thanks!
Now, how can I make it always case-insensitive?
from http://www.sai.msu.su/~megera/postgres/gist/ltree/
It is possible to use several modifiers at the end of a label:
@ Do case-insensitive label matching
* Do prefix matching for a label
% Don't account word separator '_' in label matching,
that is 'Russian%' would match 'Russian_nations',
but not 'Russian'
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg
Astronomical Institute, Moscow University (Russia) Internet:
oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
Does that apply to each node, or the entire string?
I'd like to not have to parse the lquery string and make each node following
it with an @.
I'm a little bit tired :), if you want case insenstive for the whole node,
you could use built-in fuinction 'lower(text)' !
use text2ltree(lower(text))
LER
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[GENERAL]
i have a table in which duplicate rows occur. now i have to remove the duplicates. Please note that however, only the duplicate rows have to be deleted and not the original one. How do i do it?
[GENERAL] Cannot connect to Database
Hi, everytime I try to connect to my Database with a Java-Applikation, I recieve only the following Exception: org.postgresql.util.PSQLException: A connection error has occurred: org.postgresql.util.PSQLException: FATAL: kein pg_hba.conf-Eintrag fĆ¼r Host Ā»127.0.0.1Ā«, Benutzer Ā»postgresĀ«, Datenbank Ā»testĀ«, SSL aus I get the same error-message with other applications, e.g. pgaccess, too. My pg_hba.conf looks like: # TYPE DATABASEUSERIP-ADDRESSIP-MASK METHOD hostall all 127.0.0.1 255.255.255.255 trust The database and the applications are on the same host. What could be the problem. thx (and soory for my english), Bernd ---(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] Cannot connect to Database
On Tue, 08 Feb 2005 18:46:00 +0100, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi, everytime I try to connect to my Database with a Java-Applikation, I recieve only the following Exception: org.postgresql.util.PSQLException: A connection error has occurred: org.postgresql.util.PSQLException: FATAL: kein pg_hba.conf-Eintrag fĆ¼r Host Ā»127.0.0.1Ā«, Benutzer Ā»postgresĀ«, Datenbank Ā»testĀ«, SSL aus I get the same error-message with other applications, e.g. pgaccess, too. My pg_hba.conf looks like: # TYPE DATABASEUSERIP-ADDRESSIP-MASK METHOD hostall all 127.0.0.1 255.255.255.255 trust The database and the applications are on the same host. What could be the problem. Do you have TCP/IP activated in postgresql.conf ? Are you trying to connect via localhost (127.0.0.1) or some other route? -- ~ L. Friedman[EMAIL PROTECTED] LlamaLand http://netllama.linux-sxs.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Confused by to_char
On Tue, 2005-02-08 at 13:00 +, Ragnar Hafsta wrote:
On Tue, 2005-02-08 at 12:28 +, mike wrote:
I am am trying to get a day string from a date using to_char ie:
SELECT date1,ti1 ,to1,ti2,to2,adj,ei,eo,to_char('2005-02-07','Day') FROM
vw_times_list1
however I get
function to_char(unknown, unknown) is not unique
test=# select to_char('2005-02-07'::date,'Day');
to_char
---
Monday
(1 row)
thanks for this - I found the solution to my immediate problem by
looking through a dump for to_char and found the function I was looking
for at the moment (dayname) but this will be useful for the general case
Mike
gnari
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Cannot connect to Database
Do you have TCP/IP activated in postgresql.conf ? Are you trying to connect via localhost (127.0.0.1) or some other route? I start the Server with the -i option, so TCP/IP is activated, isn't it? I want to connect by localhost to my database. thx, Bernd --- UrsprĆ¼ngliche Nachricht --- Datum: 08.02.2005 18:52 Von: Lonni J Friedman [EMAIL PROTECTED] An: [EMAIL PROTECTED] [EMAIL PROTECTED] Betreff: Re: [GENERAL] Cannot connect to Database On Tue, 08 Feb 2005 18:46:00 +0100, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi, everytime I try to connect to my Database with a Java-Applikation, I recieve only the following Exception: org.postgresql.util.PSQLException: A connection error has occurred: org.postgresql.util.PSQLException: FATAL: kein pg_hba.conf-Eintrag fĆ¼r Host Ā»127.0.0.1Ā«, Benutzer Ā»postgresĀ«, Datenbank Ā»testĀ«, SSL aus I get the same error-message with other applications, e.g. pgaccess, too. My pg_hba.conf looks like: # TYPE DATABASEUSERIP-ADDRESSIP-MASK METHOD hostall all 127.0.0.1 255.255.255.255 trust The database and the applications are on the same host. What could be the problem. Do you have TCP/IP activated in postgresql.conf ? Are you trying to connect via localhost (127.0.0.1) or some other route? -- ~ L. Friedman[EMAIL PROTECTED] LlamaLand http://netllama.linux-sxs.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL]
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi Surabhi, search the archives, this has been discussed quite a few times in the pass. regards, - - Jonel Rienton http://blogs.road14.com Software Developer, *nix Advocate On Feb 8, 2005, at 11:44 AM, Surabhi Ahuja wrote: i have a table in which duplicate rows occur. now i have to remove the duplicates. Please note that however, only the duplicate rows have to be deleted and not the original one. How do i do it? -BEGIN PGP SIGNATURE- Version: PGP 8.1 iQA/AwUBQgjvDeAwOVAnbsGCEQKiwgCgn0JzdJKYXMq3WgeskWTKmg6xCUMAnRz9 +gfpmg4HI+PZPMU+KQcKPuLY =6189 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Cannot connect to Database
[EMAIL PROTECTED] wrote: Do you have TCP/IP activated in postgresql.conf ? Are you trying to connect via localhost (127.0.0.1) or some other route? I start the Server with the -i option, so TCP/IP is activated, isn't it? I want to connect by localhost to my database. From the localhost can you: psql -h 127.0.0.1 -U postgres template1 ? Sincerely, Joshua D. Drake thx, Bernd --- UrsprĆ¼ngliche Nachricht --- Datum: 08.02.2005 18:52 Von: Lonni J Friedman [EMAIL PROTECTED] An: [EMAIL PROTECTED] [EMAIL PROTECTED] Betreff: Re: [GENERAL] Cannot connect to Database On Tue, 08 Feb 2005 18:46:00 +0100, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi, everytime I try to connect to my Database with a Java-Applikation, I recieve only the following Exception: org.postgresql.util.PSQLException: A connection error has occurred: org.postgresql.util.PSQLException: FATAL: kein pg_hba.conf-Eintrag fĆ¼r Host Ā»127.0.0.1Ā«, Benutzer Ā»postgresĀ«, Datenbank Ā»testĀ«, SSL aus I get the same error-message with other applications, e.g. pgaccess, too. My pg_hba.conf looks like: # TYPE DATABASEUSERIP-ADDRESSIP-MASK METHOD hostall all 127.0.0.1 255.255.255.255 trust The database and the applications are on the same host. What could be the problem. Do you have TCP/IP activated in postgresql.conf ? Are you trying to connect via localhost (127.0.0.1) or some other route? -- ~ L. Friedman[EMAIL PROTECTED] LlamaLand http://netllama.linux-sxs.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Command Prompt, Inc., your source for PostgreSQL replication, professional support, programming, managed services, shared and dedicated hosting. Home of the Open Source Projects plPHP, plPerlNG, pgManage, and pgPHPtoolkit. Contact us now at: +1-503-667-4564 - http://www.commandprompt.com begin:vcard fn:Joshua D. Drake n:Drake;Joshua D. org:Command Prompt, Inc. adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. x-mozilla-html:FALSE url:http://www.commandprompt.com/ version:2.1 end:vcard ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Cannot connect to Database
I recvive still the same error-message. thx, Bernd --- UrsprĆ¼ngliche Nachricht --- Datum: 08.02.2005 19:14 Von: Joshua D. Drake [EMAIL PROTECTED] An: [EMAIL PROTECTED] Betreff: Re: [GENERAL] Cannot connect to Database [EMAIL PROTECTED] wrote: Do you have TCP/IP activated in postgresql.conf ? Are you trying to connect via localhost (127.0.0.1) or some other route? I start the Server with the -i option, so TCP/IP is activated, isn't it? I want to connect by localhost to my database. From the localhost can you: psql -h 127.0.0.1 -U postgres template1 ? Sincerely, Joshua D. Drake thx, Bernd --- UrsprĆ¼ngliche Nachricht --- Datum: 08.02.2005 18:52 Von: Lonni J Friedman [EMAIL PROTECTED] An: [EMAIL PROTECTED] [EMAIL PROTECTED] Betreff: Re: [GENERAL] Cannot connect to Database On Tue, 08 Feb 2005 18:46:00 +0100, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi, everytime I try to connect to my Database with a Java-Applikation, I recieve only the following Exception: org.postgresql.util.PSQLException: A connection error has occurred: org.postgresql.util.PSQLException: FATAL: kein pg_hba.conf-Eintrag fĆ¼r Host Ā»127.0.0.1Ā«, Benutzer Ā»postgresĀ«, Datenbank Ā»testĀ«, SSL aus I get the same error-message with other applications, e.g. pgaccess, too. My pg_hba.conf looks like: # TYPE DATABASEUSERIP-ADDRESSIP-MASK METHOD hostall all 127.0.0.1 255.255.255.255 trust The database and the applications are on the same host. What could be the problem. Do you have TCP/IP activated in postgresql.conf ? Are you trying to connect via localhost (127.0.0.1) or some other route? -- ~ L. Friedman[EMAIL PROTECTED] LlamaLand http://netllama.linux-sxs.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Command Prompt, Inc., your source for PostgreSQL replication, professional support, programming, managed services, shared and dedicated hosting. Home of the Open Source Projects plPHP, plPerlNG, pgManage, and pgPHPtoolkit. Contact us now at: +1-503-667-4564 - http://www.commandprompt.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Cannot connect to Database
I still recive the same error-message thx, Bernd --- UrsprĆ¼ngliche Nachricht --- Datum: 08.02.2005 19:13 Von: javier wilson [EMAIL PROTECTED] An: [EMAIL PROTECTED] [EMAIL PROTECTED] Betreff: Re: [GENERAL] Cannot connect to Database have you tried connecting to it with other tool, like psql -h localhost -U postgresql test ? in my opinion the message is clear, something must be wrong with pg_hba.conf have reloaded postgresql after updating this file? javier On Tue, 08 Feb 2005 19:01:28 +0100, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Do you have TCP/IP activated in postgresql.conf ? Are you trying to connect via localhost (127.0.0.1) or some other route? I start the Server with the -i option, so TCP/IP is activated, isn't it? I want to connect by localhost to my database. thx, Bernd --- UrsprĆ¼ngliche Nachricht --- Datum: 08.02.2005 18:52 Von: Lonni J Friedman [EMAIL PROTECTED] An: [EMAIL PROTECTED] [EMAIL PROTECTED] Betreff: Re: [GENERAL] Cannot connect to Database On Tue, 08 Feb 2005 18:46:00 +0100, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi, everytime I try to connect to my Database with a Java-Applikation, I recieve only the following Exception: org.postgresql.util.PSQLException: A connection error has occurred: org.postgresql.util.PSQLException: FATAL: kein pg_hba.conf-Eintrag fĆ¼r Host Ā»127.0.0.1Ā«, Benutzer Ā»postgresĀ«, Datenbank Ā»testĀ«, SSL aus I get the same error-message with other applications, e.g. pgaccess, too. My pg_hba.conf looks like: # TYPE DATABASEUSERIP-ADDRESSIP-MASK METHOD hostall all 127.0.0.1 255.255.255.255 trust The database and the applications are on the same host. What could be the problem. Do you have TCP/IP activated in postgresql.conf ? Are you trying to connect via localhost (127.0.0.1) or some other route? -- ~ L. Friedman[EMAIL PROTECTED] LlamaLand http://netllama.linux-sxs.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(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] Cannot connect to Database
Did you restart Postgresql after editing your pg_hba.conf file? On Tue, 2005-02-08 at 12:22, [EMAIL PROTECTED] wrote: I recvive still the same error-message. thx, Bernd --- Ursprngliche Nachricht --- Datum: 08.02.2005 19:14 Von: Joshua D. Drake [EMAIL PROTECTED] An: [EMAIL PROTECTED] Betreff: Re: [GENERAL] Cannot connect to Database [EMAIL PROTECTED] wrote: Do you have TCP/IP activated in postgresql.conf ? Are you trying to connect via localhost (127.0.0.1) or some other route? I start the Server with the -i option, so TCP/IP is activated, isn't it? I want to connect by localhost to my database. From the localhost can you: psql -h 127.0.0.1 -U postgres template1 ? Sincerely, Joshua D. Drake thx, Bernd --- Ursprngliche Nachricht --- Datum: 08.02.2005 18:52 Von: Lonni J Friedman [EMAIL PROTECTED] An: [EMAIL PROTECTED] [EMAIL PROTECTED] Betreff: Re: [GENERAL] Cannot connect to Database On Tue, 08 Feb 2005 18:46:00 +0100, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi, everytime I try to connect to my Database with a Java-Applikation, I recieve only the following Exception: org.postgresql.util.PSQLException: A connection error has occurred: org.postgresql.util.PSQLException: FATAL: kein pg_hba.conf-Eintrag fr Host 127.0.0.1, Benutzer postgres, Datenbank test, SSL aus I get the same error-message with other applications, e.g. pgaccess, too. My pg_hba.conf looks like: # TYPE DATABASEUSERIP-ADDRESSIP-MASK METHOD hostall all 127.0.0.1 255.255.255.255 trust The database and the applications are on the same host. What could be the problem. Do you have TCP/IP activated in postgresql.conf ? Are you trying to connect via localhost (127.0.0.1) or some other route? -- ~ L. Friedman[EMAIL PROTECTED] LlamaLand http://netllama.linux-sxs.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Command Prompt, Inc., your source for PostgreSQL replication, professional support, programming, managed services, shared and dedicated hosting. Home of the Open Source Projects plPHP, plPerlNG, pgManage, and pgPHPtoolkit. Contact us now at: +1-503-667-4564 - http://www.commandprompt.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(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] Cannot connect to Database
Did you restart Postgresql after editing your pg_hba.conf file? Yes, I did. thx, Bernd --- UrsprĆ¼ngliche Nachricht --- Datum: 08.02.2005 19:31 Von: Scott Marlowe [EMAIL PROTECTED] An: [EMAIL PROTECTED] Betreff: Re: [GENERAL] Cannot connect to Database Did you restart Postgresql after editing your pg_hba.conf file? On Tue, 2005-02-08 at 12:22, [EMAIL PROTECTED] wrote: I recvive still the same error-message. thx, Bernd --- UrsprĆĀ¼ngliche Nachricht --- Datum: 08.02.2005 19:14 Von: Joshua D. Drake [EMAIL PROTECTED] An: [EMAIL PROTECTED] Betreff: Re: [GENERAL] Cannot connect to Database [EMAIL PROTECTED] wrote: Do you have TCP/IP activated in postgresql.conf ? Are you trying to connect via localhost (127.0.0.1) or some other route? I start the Server with the -i option, so TCP/IP is activated, isn't it? I want to connect by localhost to my database. From the localhost can you: psql -h 127.0.0.1 -U postgres template1 ? Sincerely, Joshua D. Drake thx, Bernd --- UrsprĆĀ¼ngliche Nachricht --- Datum: 08.02.2005 18:52 Von: Lonni J Friedman [EMAIL PROTECTED] An: [EMAIL PROTECTED] [EMAIL PROTECTED] Betreff: Re: [GENERAL] Cannot connect to Database On Tue, 08 Feb 2005 18:46:00 +0100, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi, everytime I try to connect to my Database with a Java-Applikation, I recieve only the following Exception: org.postgresql.util.PSQLException: A connection error has occurred: org.postgresql.util.PSQLException: FATAL: kein pg_hba.conf-Eintrag fĆĀ¼r Host ĆĀ»127.0.0.1ĆĀ«, Benutzer ĆĀ»postgresĆĀ«, Datenbank ĆĀ»testĆĀ«, SSL aus I get the same error-message with other applications, e.g. pgaccess, too. My pg_hba.conf looks like: # TYPE DATABASEUSERIP-ADDRESSIP-MASK METHOD hostall all 127.0.0.1 255.255.255.255 trust The database and the applications are on the same host. What could be the problem. Do you have TCP/IP activated in postgresql.conf ? Are you trying to connect via localhost (127.0.0.1) or some other route? -- ~ L. Friedman[EMAIL PROTECTED] LlamaLand http://netllama.linux-sxs.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Command Prompt, Inc., your source for PostgreSQL replication, professional support, programming, managed services, shared and dedicated hosting. Home of the Open Source Projects plPHP, plPerlNG, pgManage, and pgPHPtoolkit. Contact us now at: +1-503-667-4564 - http://www.commandprompt.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Cannot connect to Database
Well, its obviosly not listening on localhost. Are you firewalling anywhere? Which OS is this? Which version of postgresql? If this is Linux, what do you get from running 'netstat -an | grep 5432' ? On Tue, 08 Feb 2005 19:44:26 +0100, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Did you restart Postgresql after editing your pg_hba.conf file? Yes, I did. thx, Bernd --- UrsprĆ¼ngliche Nachricht --- Datum: 08.02.2005 19:31 Von: Scott Marlowe [EMAIL PROTECTED] An: [EMAIL PROTECTED] Betreff: Re: [GENERAL] Cannot connect to Database Did you restart Postgresql after editing your pg_hba.conf file? On Tue, 2005-02-08 at 12:22, [EMAIL PROTECTED] wrote: I recvive still the same error-message. thx, Bernd --- UrsprĆĀ¼ngliche Nachricht --- Datum: 08.02.2005 19:14 Von: Joshua D. Drake [EMAIL PROTECTED] An: [EMAIL PROTECTED] Betreff: Re: [GENERAL] Cannot connect to Database [EMAIL PROTECTED] wrote: Do you have TCP/IP activated in postgresql.conf ? Are you trying to connect via localhost (127.0.0.1) or some other route? I start the Server with the -i option, so TCP/IP is activated, isn't it? I want to connect by localhost to my database. From the localhost can you: psql -h 127.0.0.1 -U postgres template1 ? Sincerely, Joshua D. Drake thx, Bernd --- UrsprĆĀ¼ngliche Nachricht --- Datum: 08.02.2005 18:52 Von: Lonni J Friedman [EMAIL PROTECTED] An: [EMAIL PROTECTED] [EMAIL PROTECTED] Betreff: Re: [GENERAL] Cannot connect to Database On Tue, 08 Feb 2005 18:46:00 +0100, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi, everytime I try to connect to my Database with a Java-Applikation, I recieve only the following Exception: org.postgresql.util.PSQLException: A connection error has occurred: org.postgresql.util.PSQLException: FATAL: kein pg_hba.conf-Eintrag fĆĀ¼r Host ĆĀ»127.0.0.1ĆĀ«, Benutzer ĆĀ»postgresĆĀ«, Datenbank ĆĀ»testĆĀ«, SSL aus I get the same error-message with other applications, e.g. pgaccess, too. My pg_hba.conf looks like: # TYPE DATABASEUSERIP-ADDRESSIP-MASK METHOD hostall all 127.0.0.1 255.255.255.255 trust The database and the applications are on the same host. What could be the problem. Do you have TCP/IP activated in postgresql.conf ? Are you trying to connect via localhost (127.0.0.1) or some other route? -- ~ L. Friedman[EMAIL PROTECTED] LlamaLand http://netllama.linux-sxs.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Cannot connect to Database
[EMAIL PROTECTED] writes: everytime I try to connect to my Database with a Java-Applikation, I recieve only the following Exception: org.postgresql.util.PSQLException: A connection error has occurred: org.postgresql.util.PSQLException: FATAL: kein pg_hba.conf-Eintrag fĆ¼r Host Ā»127.0.0.1Ā«, Benutzer Ā»postgresĀ«, Datenbank Ā»testĀ«, SSL aus I get the same error-message with other applications, e.g. pgaccess, too. My pg_hba.conf looks like: # TYPE DATABASEUSERIP-ADDRESSIP-MASK METHOD hostall all 127.0.0.1 255.255.255.255 trust It is simply not possible that you get that error message with that pg_hba.conf. One way or another, the postmaster you are talking to is using some other pg_hba.conf than you think it is. Maybe you are connecting to a different postmaster, or maybe you are editing the wrong copy of pg_hba.conf (we've seen several people make the latter mistake --- the relevant one is the one in the postmaster's data directory). Or you forgot to SIGHUP the postmaster after editing the file, though you say you did that. 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] Cannot connect to Database
Is postgresql on the same machine as your applicaiton? Are you sure postgresql is starting up in the directory you think it is? On Tue, 2005-02-08 at 12:44, [EMAIL PROTECTED] wrote: Did you restart Postgresql after editing your pg_hba.conf file? Yes, I did. thx, Bernd --- Ursprngliche Nachricht --- Datum: 08.02.2005 19:31 Von: Scott Marlowe [EMAIL PROTECTED] An: [EMAIL PROTECTED] Betreff: Re: [GENERAL] Cannot connect to Database Did you restart Postgresql after editing your pg_hba.conf file? On Tue, 2005-02-08 at 12:22, [EMAIL PROTECTED] wrote: I recvive still the same error-message. thx, Bernd --- Ursprngliche Nachricht --- Datum: 08.02.2005 19:14 Von: Joshua D. Drake [EMAIL PROTECTED] An: [EMAIL PROTECTED] Betreff: Re: [GENERAL] Cannot connect to Database [EMAIL PROTECTED] wrote: Do you have TCP/IP activated in postgresql.conf ? Are you trying to connect via localhost (127.0.0.1) or some other route? I start the Server with the -i option, so TCP/IP is activated, isn't it? I want to connect by localhost to my database. From the localhost can you: psql -h 127.0.0.1 -U postgres template1 ? Sincerely, Joshua D. Drake thx, Bernd --- Ursprngliche Nachricht --- Datum: 08.02.2005 18:52 Von: Lonni J Friedman [EMAIL PROTECTED] An: [EMAIL PROTECTED] [EMAIL PROTECTED] Betreff: Re: [GENERAL] Cannot connect to Database On Tue, 08 Feb 2005 18:46:00 +0100, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi, everytime I try to connect to my Database with a Java-Applikation, I recieve only the following Exception: org.postgresql.util.PSQLException: A connection error has occurred: org.postgresql.util.PSQLException: FATAL: kein pg_hba.conf-Eintrag fr Host 127.0.0.1, Benutzer postgres, Datenbank test, SSL aus I get the same error-message with other applications, e.g. pgaccess, too. My pg_hba.conf looks like: # TYPE DATABASEUSERIP-ADDRESSIP-MASK METHOD hostall all 127.0.0.1 255.255.255.255 trust The database and the applications are on the same host. What could be the problem. Do you have TCP/IP activated in postgresql.conf ? Are you trying to connect via localhost (127.0.0.1) or some other route? -- ~ L. Friedman[EMAIL PROTECTED] LlamaLand http://netllama.linux-sxs.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Command Prompt, Inc., your source for PostgreSQL replication, professional support, programming, managed services, shared and dedicated hosting. Home of the Open Source Projects plPHP, plPerlNG, pgManage, and pgPHPtoolkit. Contact us now at: +1-503-667-4564 - http://www.commandprompt.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Cannot connect to Database
I've already shutdown my firewall. My OS is Linux (SuSE 9.2 64Bit) My postgre version is 7.4.6 and the port is on listen-mode [EMAIL PROTECTED]:/home/bernd netstat -an | grep 5432 tcp0 0 0.0.0.0:54320.0.0.0:* LISTEN tcp0 0 :::5432 :::* LISTEN unix 2 [ ACC ] STREAM HĆRT 15159 /tmp/.s.PGSQL.5432 thx, Bernd --- UrsprĆ¼ngliche Nachricht --- Datum: 08.02.2005 19:56 Von: Lonni J Friedman [EMAIL PROTECTED] An: [EMAIL PROTECTED] [EMAIL PROTECTED] Betreff: Re: [GENERAL] Cannot connect to Database Well, its obviosly not listening on localhost. Are you firewalling anywhere? Which OS is this? Which version of postgresql? If this is Linux, what do you get from running 'netstat -an | grep 5432' ? On Tue, 08 Feb 2005 19:44:26 +0100, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Did you restart Postgresql after editing your pg_hba.conf file? Yes, I did. thx, Bernd --- UrsprĆ¼ngliche Nachricht --- Datum: 08.02.2005 19:31 Von: Scott Marlowe [EMAIL PROTECTED] An: [EMAIL PROTECTED] Betreff: Re: [GENERAL] Cannot connect to Database Did you restart Postgresql after editing your pg_hba.conf file? On Tue, 2005-02-08 at 12:22, [EMAIL PROTECTED] wrote: I recvive still the same error-message. thx, Bernd --- UrsprĆĀ¼ngliche Nachricht --- Datum: 08.02.2005 19:14 Von: Joshua D. Drake [EMAIL PROTECTED] An: [EMAIL PROTECTED] Betreff: Re: [GENERAL] Cannot connect to Database [EMAIL PROTECTED] wrote: Do you have TCP/IP activated in postgresql.conf ? Are you trying to connect via localhost (127.0.0.1) or some other route? I start the Server with the -i option, so TCP/IP is activated, isn't it? I want to connect by localhost to my database. From the localhost can you: psql -h 127.0.0.1 -U postgres template1 ? Sincerely, Joshua D. Drake thx, Bernd --- UrsprĆĀ¼ngliche Nachricht --- Datum: 08.02.2005 18:52 Von: Lonni J Friedman [EMAIL PROTECTED] An: [EMAIL PROTECTED] [EMAIL PROTECTED] Betreff: Re: [GENERAL] Cannot connect to Database On Tue, 08 Feb 2005 18:46:00 +0100, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi, everytime I try to connect to my Database with a Java-Applikation, I recieve only the following Exception: org.postgresql.util.PSQLException: A connection error has occurred: org.postgresql.util.PSQLException: FATAL: kein pg_hba.conf-Eintrag fĆĀ¼r Host ĆĀ»127.0.0.1ĆĀ«, Benutzer ĆĀ»postgresĆĀ«, Datenbank ĆĀ»testĆĀ«, SSL aus I get the same error-message with other applications, e.g. pgaccess, too. My pg_hba.conf looks like: # TYPE DATABASEUSERIP-ADDRESSIP-MASK METHOD hostall all 127.0.0.1 255.255.255.255 trust The database and the applications are on the same host. What could be the problem. Do you have TCP/IP activated in postgresql.conf ? Are you trying to connect via localhost (127.0.0.1) or some other route? -- ~ L. Friedman[EMAIL PROTECTED] LlamaLand http://netllama.linux-sxs.org ---(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] Cannot connect to Database
That was it. i've two hb_conf-Files on my system. Now I can connect without any problems. thx --- UrsprĆ¼ngliche Nachricht --- Datum: 08.02.2005 20:06 Von: Scott Marlowe [EMAIL PROTECTED] An: [EMAIL PROTECTED] Betreff: Re: [GENERAL] Cannot connect to Database Is postgresql on the same machine as your applicaiton? Are you sure postgresql is starting up in the directory you think it is? On Tue, 2005-02-08 at 12:44, [EMAIL PROTECTED] wrote: Did you restart Postgresql after editing your pg_hba.conf file? Yes, I did. thx, Bernd --- UrsprĆĀ¼ngliche Nachricht --- Datum: 08.02.2005 19:31 Von: Scott Marlowe [EMAIL PROTECTED] An: [EMAIL PROTECTED] Betreff: Re: [GENERAL] Cannot connect to Database Did you restart Postgresql after editing your pg_hba.conf file? On Tue, 2005-02-08 at 12:22, [EMAIL PROTECTED] wrote: I recvive still the same error-message. thx, Bernd --- UrsprĆĀĆĀ¼ngliche Nachricht --- Datum: 08.02.2005 19:14 Von: Joshua D. Drake [EMAIL PROTECTED] An: [EMAIL PROTECTED] Betreff: Re: [GENERAL] Cannot connect to Database [EMAIL PROTECTED] wrote: Do you have TCP/IP activated in postgresql.conf ? Are you trying to connect via localhost (127.0.0.1) or some other route? I start the Server with the -i option, so TCP/IP is activated, isn't it? I want to connect by localhost to my database. From the localhost can you: psql -h 127.0.0.1 -U postgres template1 ? Sincerely, Joshua D. Drake thx, Bernd --- UrsprĆĀĆĀ¼ngliche Nachricht --- Datum: 08.02.2005 18:52 Von: Lonni J Friedman [EMAIL PROTECTED] An: [EMAIL PROTECTED] [EMAIL PROTECTED] Betreff: Re: [GENERAL] Cannot connect to Database On Tue, 08 Feb 2005 18:46:00 +0100, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi, everytime I try to connect to my Database with a Java-Applikation, I recieve only the following Exception: org.postgresql.util.PSQLException: A connection error has occurred: org.postgresql.util.PSQLException: FATAL: kein pg_hba.conf-Eintrag fĆĀĆĀ¼r Host ĆĀĆĀ»127.0.0.1ĆĀĆĀ«, Benutzer ĆĀĆĀ»postgresĆĀĆĀ«, Datenbank ĆĀĆĀ»testĆĀĆĀ«, SSL aus I get the same error-message with other applications, e.g. pgaccess, too. My pg_hba.conf looks like: # TYPE DATABASEUSERIP-ADDRESSIP-MASK METHOD hostall all 127.0.0.1 255.255.255.255 trust The database and the applications are on the same host. What could be the problem. Do you have TCP/IP activated in postgresql.conf ? Are you trying to connect via localhost (127.0.0.1) or some other route? -- ~ L. Friedman [EMAIL PROTECTED] LlamaLand http://netllama.linux-sxs.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Command Prompt, Inc., your source for PostgreSQL replication, professional support, programming, managed services, shared and dedicated hosting. Home of the Open Source Projects plPHP, plPerlNG, pgManage, and pgPHPtoolkit. Contact us now at: +1-503-667-4564 - http://www.commandprompt.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] What talk would get you to go to OSCon?
Hey folks,
I'm working with a few others to put together a solid line up of talks for
this years OSCon and we'd like to get a straw poll of what talks you would
most like to see. Not just that would be cool but ones that would get you
to go book your plane tickets next weekend :-) So far some folks have
suggested:
Slony
Getting started with Pg
High Availability
Dealing with big big big data
View/Functions
Indexing
Building a datawarehouse
Working with corporate contributors
If you have other ideas please feel free to chime in, we'd really like to see
an uptick in postgresql attendees.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[GENERAL] hi
Norman Virus Control a supprim la pice-jointe game_xxo.txt.exe qui contenait le virus [EMAIL PROTECTED] Here is it! Attachment: No Virus found F-Secure AntiVirus - www.f-secure.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Removing duplicates
Please use a relevant subject for your posts. On Tue, Feb 08, 2005 at 23:14:57 +0530, Surabhi Ahuja [EMAIL PROTECTED] wrote: i have a table in which duplicate rows occur. now i have to remove the duplicates. Please note that however, only the duplicate rows have to be deleted and not the original one. How do i do it? If the table has oids, then you can delete all but the row with the minimum oid for each set of duplicates. Another option is to do a select distinct into a temp table, a delete and then copy the temp table back. You should also add a constraint so that duplicates can't get back into the table once you have it cleaned up. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Netstat - Lots of PG Connections
Is this anything to worry about? Why are there so many Postgresql connections? 'ps axu' usually only shows about 5-10 postgres processes. # netstat tcp0 0 localhost.localdo:55547 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55780 localhost.loca:postgres ESTABLISHED tcp0 0 localhost.localdo:55777 localhost.loca:postgres ESTABLISHED tcp0 0 localhost.localdo:55778 localhost.loca:postgres ESTABLISHED tcp0 0 localhost.localdo:55779 localhost.loca:postgres ESTABLISHED tcp0 0 localhost.localdo:55764 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55760 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55761 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55772 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55773 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55774 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55768 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55769 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55770 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55748 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55749 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55750 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55750 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55751 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55744 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55746 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55747 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55756 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55758 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55752 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55729 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55731 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55741 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55742 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55736 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55737 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55716 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55718 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55719 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55712 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55714 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55715 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55724 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55725 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55727 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55720 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55721 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55722 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55700 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55701 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55703 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55696 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55697 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55698 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55699 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55708 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55709 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55710 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55711 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55704 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55705 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55706 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55684 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55685 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55686 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55687 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55680 localhost.loca:postgres TIME_WAIT tcp
Re: [GENERAL] Netstat - Lots of PG Connections
On Tue, 8 Feb 2005 13:17:38 -0800 (PST), CSN [EMAIL PROTECTED] wrote: Is this anything to worry about? Why are there so many Postgresql connections? 'ps axu' usually only shows about 5-10 postgres processes. # netstat tcp0 0 localhost.localdo:55547 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55780 localhost.loca:postgres ESTABLISHED tcp0 0 localhost.localdo:55777 localhost.loca:postgres ESTABLISHED tcp0 0 localhost.localdo:55778 localhost.loca:postgres ESTABLISHED tcp0 0 localhost.localdo:55779 localhost.loca:postgres ESTABLISHED tcp0 0 localhost.localdo:55764 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55760 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55761 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55772 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55773 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55774 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55768 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55769 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55770 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55748 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55749 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55750 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55750 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55751 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55744 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55746 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55747 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55756 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55758 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55752 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55729 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55731 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55741 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55742 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55736 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55737 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55716 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55718 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55719 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55712 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55714 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55715 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55724 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55725 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55727 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55720 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55721 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55722 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55700 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55701 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55703 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55696 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55697 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55698 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55699 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55708 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55709 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55710 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55711 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55704 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55705 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55706 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55684 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55685 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55686
Re: [GENERAL] Netstat - Lots of PG Connections
--- Lonni J Friedman [EMAIL PROTECTED] wrote: On Tue, 8 Feb 2005 13:17:38 -0800 (PST), CSN [EMAIL PROTECTED] wrote: Is this anything to worry about? Why are there so many Postgresql connections? 'ps axu' usually only shows about 5-10 postgres processes. # netstat tcp0 0 localhost.localdo:55547 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55780 localhost.loca:postgres ESTABLISHED tcp0 0 localhost.localdo:55777 localhost.loca:postgres ESTABLISHED tcp0 0 localhost.localdo:55778 localhost.loca:postgres ESTABLISHED tcp0 0 localhost.localdo:55779 localhost.loca:postgres ESTABLISHED tcp0 0 localhost.localdo:55764 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55760 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55761 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55772 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55773 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55774 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55768 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55769 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55770 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55748 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55749 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55750 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55750 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55751 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55744 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55746 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55747 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55756 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55758 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55752 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55729 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55731 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55741 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55742 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55736 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55737 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55716 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55718 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55719 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55712 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55714 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55715 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55724 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55725 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55727 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55720 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55721 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55722 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55700 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55701 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55703 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55696 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55697 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55698 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55699 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55708 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55709 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55710 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55711 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55704 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55705 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55706 localhost.loca:postgres TIME_WAIT tcp0 0
Re: [GENERAL] Netstat - Lots of PG Connections
It's on Linux (Redhat 9). CSN --- Oisin Glynn [EMAIL PROTECTED] wrote: If this is a windows box I have noticed that the TIME_WAIT lasts far longer than on linux/unix. It is defined as a multiple of double of another TCPIP timer in the registry! Oisin snip __ Do you Yahoo!? The all-new My Yahoo! - Get yours free! http://my.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Netstat - Lots of PG Connections
On Tue, 8 Feb 2005 13:44:48 -0800 (PST), CSN [EMAIL PROTECTED] wrote: --- Lonni J Friedman [EMAIL PROTECTED] wrote: On Tue, 8 Feb 2005 13:17:38 -0800 (PST), CSN [EMAIL PROTECTED] wrote: Is this anything to worry about? Why are there so many Postgresql connections? 'ps axu' usually only shows about 5-10 postgres processes. # netstat tcp0 0 localhost.localdo:55547 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55780 localhost.loca:postgres ESTABLISHED tcp0 0 localhost.localdo:55777 localhost.loca:postgres ESTABLISHED tcp0 0 localhost.localdo:55778 localhost.loca:postgres ESTABLISHED tcp0 0 localhost.localdo:55779 localhost.loca:postgres ESTABLISHED tcp0 0 localhost.localdo:55764 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55760 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55566 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55567 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55562 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55567 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55562 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55563 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.loca:postgres localhost.localdo:55780 ESTABLISHED tcp 47 0 localhost.loca:postgres localhost.localdo:55783 ESTABLISHED tcp 42 0 localhost.loca:postgres localhost.localdo:55782 ESTABLISHED tcp0 0 localhost.loca:postgres localhost.localdo:55777 ESTABLISHED tcp0 0 localhost.loca:postgres localhost.localdo:55779 ESTABLISHED tcp 31 0 localhost.loca:postgres localhost.localdo:55785 ESTABLISHED tcp 47 0 localhost.loca:postgres localhost.localdo:55784 ESTABLISHED The vast majority of these are in a TIME_WAIT state, where the socket is waiting after close to handle packets still in the network. What do you have accessing this DB? It's a web server (Apache and PHP). OK, then that looks fairly normal assuming that you have a decent amount of traffic. -- ~ L. Friedman[EMAIL PROTECTED] LlamaLand http://netllama.linux-sxs.org ---(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] Netstat - Lots of PG Connections
If this is a windows box I have noticed that the TIME_WAIT lasts far longer than on linux/unix. It is defined as a multiple of double of another TCPIP timer in the registry! Oisin - Original Message - From: Lonni J Friedman [EMAIL PROTECTED] To: CSN [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Sent: Tuesday, February 08, 2005 16:25 Subject: Re: [GENERAL] Netstat - Lots of PG Connections On Tue, 8 Feb 2005 13:17:38 -0800 (PST), CSN [EMAIL PROTECTED] wrote: Is this anything to worry about? Why are there so many Postgresql connections? 'ps axu' usually only shows about 5-10 postgres processes. # netstat tcp0 0 localhost.localdo:55547 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55780 localhost.loca:postgres ESTABLISHED tcp0 0 localhost.localdo:55777 localhost.loca:postgres ESTABLISHED tcp0 0 localhost.localdo:55778 localhost.loca:postgres ESTABLISHED tcp0 0 localhost.localdo:55779 localhost.loca:postgres ESTABLISHED tcp0 0 localhost.localdo:55764 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55760 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55761 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55772 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55773 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55774 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55768 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55769 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55770 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55748 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55749 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55750 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55750 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55751 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55744 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55746 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55747 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55756 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55758 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55752 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55729 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55731 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55741 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55742 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55736 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55737 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55716 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55718 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55719 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55712 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55714 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55715 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55724 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55725 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55727 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55720 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55721 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55722 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55700 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55701 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55703 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55696 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55697 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55698 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55699 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55708 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55709 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55710 localhost.loca:postgres TIME_WAIT tcp
[GENERAL] PL/pgSQL bug: FOUND variable is not updated correct with EXECUTE
Doing an EXECUTE for a query which returns results still yields 'f' for the FOUND variable. GET DIAGNOSTICS on the other hand, works fine, Using version 8 on Win32. -- ICQ: 1912453 AIM: VitalyB1984 MSN: [EMAIL PROTECTED] Yahoo!: VitalyBe ---(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] What talk would get you to go to OSCon?
My $0.02: On Tue, 8 Feb 2005 14:29:08 -0500, Robert Treat [EMAIL PROTECTED] wrote: Hey folks, I'm working with a few others to put together a solid line up of talks for this years OSCon and we'd like to get a straw poll of what talks you would most like to see. Not just that would be cool but ones that would get you to go book your plane tickets next weekend :-) So far some folks have suggested: Slony Check! Getting started with Pg High Availability Double check! Dealing with big big big data Check! View/Functions Indexing Building a datawarehouse Check! BTW, thanks in advance! -- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer http://open-ils.org ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PL/pgSQL bug: FOUND variable is not updated correct with EXECUTE
Vitaly Belman [EMAIL PROTECTED] writes: Doing an EXECUTE for a query which returns results still yields 'f' for the FOUND variable. This is not a bug. Read the list of statements that update FOUND. EXECUTE is not one of them. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] PL/pgSQL bug: FOUND variable is not updated correct
On Tue, 2005-02-08 at 18:11 -0500, Tom Lane wrote: Vitaly Belman [EMAIL PROTECTED] writes: Doing an EXECUTE for a query which returns results still yields 'f' for the FOUND variable. This is not a bug. Read the list of statements that update FOUND. EXECUTE is not one of them. See also previous discussion on this topic: http://archives.postgresql.org/pgsql-bugs/2004-10/msg1.php -Neil ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] What talk would get you to go to OSCon?
On Tue, 8 Feb 2005 14:29:08 -0500, Robert Treat [EMAIL PROTECTED] wrote: Slony Yes. High Availability Yes. If you have other ideas please feel free to chime in, we'd really like to see an uptick in postgresql attendees. Will Bruce and Tom be attending this year like they did in 2002? Regards, aaron.glenn ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Hoow do you drop a column in a table
Using phppgadmin, I tried tropping a column in a table. I comes back to try DROP .. CASCADE. Went into psql and typed the following, ALTER TABLE MPC DROP COLUMNG do_not_use CASCADE It appeared to work but did not drop the column. What is the dprocedure for dropping a column? an anyone point me to a document that tells EXACTLY how to do this? Also, how do you find out the dependencies it says it has? I know of none. Art ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Hoow do you drop a column in a table
Hi Art, On Feb 8, 2005, at 7:37 PM, Art Fore wrote: ALTER TABLE MPC DROP COLUMNG do_not_use CASCADE It appeared to work but did not drop the column. What is the dprocedure for dropping a column? an anyone point me to a document that tells EXACTLY how to do this? Also, how do you find out the dependencies it says it has? I know of none. Here is the documentation: http://www.postgresql.org/docs/8.0/interactive/sql-altertable.html If it still does not work, try again in psql and then paste the output in your email so we can better understand the problem. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Hoow do you drop a column in a table
On Tue, 2005-02-08 at 16:37 -0800, Art Fore wrote: Using phppgadmin, I tried tropping a column in a table. I comes back to try DROP .. CASCADE. Went into psql and typed the following, ALTER TABLE MPC DROP COLUMNG do_not_use CASCADE It appeared to work but did not drop the column. What is the dprocedure for dropping a column? an anyone point me to a document that tells EXACTLY how to do this? Also, how do you find out the dependencies it says it has? I know of none. Art ALTER TABLE foo DROP COLUMN name CASCADE; -- /*** * Robby Russell | Owner.Developer.Geek * PLANET ARGON | www.planetargon.com * Portland, OR | [EMAIL PROTECTED] * 503.351.4730 | blog.planetargon.com * PHP/PostgreSQL Hosting Development * --- Now hosting Ruby on Rails Apps --- / ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Hoow do you drop a column in a table
Thanks for the answers. I recreated the views. Did not realize when you renamed a column, it also changed the views. Art Robby Russell wrote: On Tue, 2005-02-08 at 16:37 -0800, Art Fore wrote: Using phppgadmin, I tried tropping a column in a table. I comes back to try DROP .. CASCADE. Went into psql and typed the following, ALTER TABLE MPC DROP COLUMNG do_not_use CASCADE It appeared to work but did not drop the column. What is the dprocedure for dropping a column? an anyone point me to a document that tells EXACTLY how to do this? Also, how do you find out the dependencies it says it has? I know of none. Art ALTER TABLE foo DROP COLUMN name CASCADE; ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Database permissions
I had this working once before, but restarted the database and things went to hell. This user authentication for postgresql I will have to say is the most complex I have seen. Need a block diagram of how it works to understand it. Have .pgpass in postgres home directory, data directory is /home/postgres/data Per the md5 instructions. pg_shadow has postgres md5 password, and my password. Host computer is suse 9.2 at 192.168.121.252 I can acces phproject database from windows machine with no problem. I can access database with pgadmin3 from windows machine, no problem. Can no longer access via ODBC on windows machine Can no longer access from phppgadmin on windows machine Can no longer access from webmin on host machine. All I get is FATAL: IDENT authentication failed for user afore or for user postgres pg_hba.conf is below #local all all trust # IPv4-style local connections: host MPC all 192.168.121.0 255.255.255.0 trust # IPv6-style local connections: #hostall all ::1 :::::::trust #localall all ident sameuser local all all ident md5 host template1 all 192.168.121.0 255.255.255.0 trust host phprojekt all 192.168.121.252 255.255.255.0 trust host phpPgAdmin all 192.168.121.252 255.255.255.0 trust Any help would be welcome. Art ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] checking SQL statement/subexpression validity
I need to check whether a SQL subexpression (to be used in WHERE clause), e.g.: colname 200 or an entire SELECT statement, e.g.: SELECT * FROM t1, t2 WHERE colname 200 is syntactically valid. Is there a quick (and also safe) way to do this? I'm thinking of doing SELECT ... FROM ... WHERE SQLexpr LIMIT 0 for #1, but I'm not sure if it's 100% safe; and I don't know what to do with #2. AFAIK, in, say, Perl's DBI, $dbh-prepare() doesn't check SQL syntax and $sth-execute() actually executes the statement. -- dave ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] checking SQL statement/subexpression validity
I need to check whether a SQL subexpression (to be used in WHERE clause), e.g.: I've never tested it from Perl, but could you use 'explain select' to see if it parses? It won't actually execute it if it does. -- Mike Nolan ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Backup restore does not work
Using PGadminIII, I backup my database on linux machine, and try to restore it on the windows machine. Alway comes up with duplicate key on the first item. I drop all views and the only table in the database and try again. Still same thing. I do a refrresh, none of the views or table were dropped. What is the secret of backup-restore is pgadmin? Art ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Database permissions
More confused than ever. The pg_hba.conf file shown below was what I had originaly to get phppgadmin to work. Changed the local all all ident md5 to local all all trust and it started to working again with phppgadmin, but now, access via ODBC (with MSAccess)does not work. All I get is #DELETED for every oolumn and every row. could someone explain that to me? What should the pg_hba.conf file look like? Art Art Fore wrote: I had this working once before, but restarted the database and things went to hell. This user authentication for postgresql I will have to say is the most complex I have seen. Need a block diagram of how it works to understand it. Have .pgpass in postgres home directory, data directory is /home/postgres/data Per the md5 instructions. pg_shadow has postgres md5 password, and my password. Host computer is suse 9.2 at 192.168.121.252 I can acces phproject database from windows machine with no problem. I can access database with pgadmin3 from windows machine, no problem. Can no longer access via ODBC on windows machine Can no longer access from phppgadmin on windows machine Can no longer access from webmin on host machine. All I get is FATAL: IDENT authentication failed for user afore or for user postgres pg_hba.conf is below #local all all trust # IPv4-style local connections: host MPC all 192.168.121.0 255.255.255.0 trust # IPv6-style local connections: #hostall all ::1 :::::::trust #localall all ident sameuser local all all ident md5 host template1 all 192.168.121.0 255.255.255.0 trust host phprojekt all 192.168.121.252 255.255.255.0 trust host phpPgAdmin all 192.168.121.252 255.255.255.0 trust Any help would be welcome. Art ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] checking SQL statement/subexpression validity
Mike Nolan [EMAIL PROTECTED] writes: I need to check whether a SQL subexpression (to be used in WHERE clause), e.g.: I've never tested it from Perl, but could you use 'explain select' to see if it parses? It won't actually execute it if it does. Consider input along the line of SELECT true; DELETE FROM critical_table WHERE true The EXPLAIN nullifies the first part and then the second part destroys your table. I think that if you allow random possibly-hostile input to be sent to your SQL engine then you are going to get burnt :-( The V3 extended-query protocol allows only one SQL command per message --- so using that would prevent the more obvious possibilities for SQL command injection. But I'd still not have a lot of faith in it. The appropriately paranoid way to look at this is to allow through only the stuff you are sure is OK, not to try to filter out the stuff you are sure isn't OK. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] create temp table and on commit in 7.3.3
Hi all, This is giving error in 7.3.3. CREATE TEMP TABLE temptest3(col int PRIMARY KEY) ON COMMIT DELETE ROWS; ERROR: parser: parse error at or near ON at character 51 Is this supported. rgds Antony Paul ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Backup restore does not work
Art Fore wrote: Using PGadminIII, I backup my database on linux machine, and try to restore it on the windows machine. Alway comes up with duplicate key on the first item. I drop all views and the only table in the database and try again. Still same thing. I do a refrresh, none of the views or table were dropped. What is the secret of backup-restore is pgadmin? Well views are not what are going to cause the duplicate key. A duplicate key error is caused by a violation of a unique index or primary key. Do you have duplicate data? Or perhaps a partially restored table structure? Sincerely, Joshua D. Drake Art ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL begin:vcard fn:Joshua Drake n:Drake;Joshua org:Command Prompt, Inc. adr:;;PO Box 215 ;Cascade Locks;OR;97014;US email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 x-mozilla-html:FALSE url:http://www.commandprompt.com version:2.1 end:vcard ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Postgresql and Macintosh
Please tell me if Postgresql runns in an iMac. Thanks, Renato ---(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] pgpool simple feature request
TI == Tatsuo Ishii [EMAIL PROTECTED] writes: TI Yes, I thought about that too. Probably we need two kinds of TI initializations: TI 1) initilization for each new connection (as you requested) TI 2) initilization for each new client connection I think this latter one is important. Take the case where I do some set FOO BAR values to alter the query (eg, statement timeout or sequence scan disable) which should be reset on new client connection when it re-uses a backend connection. Or is this magically handled already? -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: khera@kciLink.com Rockville, MD +1-301-869-4449 x806 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] More concurent transaction over single connection ?
AFAIK (7.4.x) there is one limitation in persistant connections to postgresql from various frontends ( http://cz.php.net/manual/en/features.persistent-connections.php ), because it can not use transactions in situation where more concurent tasks use a single connection (execuse my wrong english) I suggest to add some sort of context identificator to frontend/backend protocol to overcome this limit. Ie frontend - ( like PHP for example ) make ONE persistant connection and different scripts are served over this connection. But frontend add for each instance of script a unique context identificator and postgresql server will treat different contexts as they was send by different connections. The results wil be sorted by context by frontend and feeded to apprpriate instance of the php script I think it may add some benefit to avoiding connection starting costs, especially in case where database and client are in greater network distance and/or need to use some expensive procedure to start connection and allow a relay simple and transparent connection pooling, may be a some type od spare servers like in Apache (MinSpareServers and Max SpareServers configuration directive ) What do you think about it ? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Safely Killing Backends
TFO == Thomas F O'Connell Thomas writes: TFO Which brings up a follow-up question: is it documented anywhere TFO exactly what goes on in recovery mode? If so, I've not found it. TFO When I've experienced this, it has seemed quicker just to stop and TFO restart postgres than to let recovery mode complete. Is that unsafe? The recovery has to happen at some point. What it is doing is bringing your DB to a known valid state based on the committed transactions, what's written to disk, and what's written to the pg_xlog files. A vacuum is probably in good order after this happens. Effectively, it does the moral equivalent of unplugging the power cord and restarting itself, without the bother of needing to reboot the whole machine :-) -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: khera@kciLink.com Rockville, MD +1-301-869-4449 x806 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---(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] Database permissions
local is for all local connections, phppgadmin i guess is local, if run on the same computer could be local, depending on your configuration. is access and odbc on a different computer? in that case you could add host all all ipnumber mask trust to allow access to all databeses as any user for connections coming from ipnumber. in this case ipnumber being the computer with access. if you use local and ident with sameuser and run php (like needed for phppgadmin) then your web user must be allowed to access that database, meaning you should have a postgresql user with the same name and with permission to access that database. this is the obvious, so have probably tried it already, but i can't think of other answer. javier On Tue, 08 Feb 2005 20:51:29 -0800, Art Fore [EMAIL PROTECTED] wrote: More confused than ever. The pg_hba.conf file shown below was what I had originaly to get phppgadmin to work. Changed the local all all ident md5 to local all all trust and it started to working again with phppgadmin, but now, access via ODBC (with MSAccess)does not work. All I get is #DELETED for every oolumn and every row. could someone explain that to me? What should the pg_hba.conf file look like? Art Art Fore wrote: I had this working once before, but restarted the database and things went to hell. This user authentication for postgresql I will have to say is the most complex I have seen. Need a block diagram of how it works to understand it. Have .pgpass in postgres home directory, data directory is /home/postgres/data Per the md5 instructions. pg_shadow has postgres md5 password, and my password. Host computer is suse 9.2 at 192.168.121.252 I can acces phproject database from windows machine with no problem. I can access database with pgadmin3 from windows machine, no problem. Can no longer access via ODBC on windows machine Can no longer access from phppgadmin on windows machine Can no longer access from webmin on host machine. All I get is FATAL: IDENT authentication failed for user afore or for user postgres pg_hba.conf is below #local all all trust # IPv4-style local connections: host MPC all 192.168.121.0 255.255.255.0 trust # IPv6-style local connections: #hostall all ::1 :::::::trust #localall all ident sameuser local all all ident md5 host template1 all 192.168.121.0 255.255.255.0 trust host phprojekt all 192.168.121.252 255.255.255.0 trust host phpPgAdmin all 192.168.121.252 255.255.255.0 trust Any help would be welcome. Art ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(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] Safely Killing Backends
My point/question is: when I've seen this before and recovery mode is taking on the order of minutes, I find that doing: pg_ctl stop -m i pg_ctl start is faster (on the order of seconds) than letting postgres finish recovery mode. So I wonder: 1. Is this safe from a data integrity point of view? 2. Why is it faster? Maybe the difference in time I've experienced is partially a result of the number of connections that come in (via PHP) during recovery mode, so recovering takes longer because the database is still in multi-user mode and receiving connections even if not fulfulling requests? -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Feb 8, 2005, at 2:57 PM, Vivek Khera wrote: TFO Which brings up a follow-up question: is it documented anywhere TFO exactly what goes on in recovery mode? If so, I've not found it. TFO When I've experienced this, it has seemed quicker just to stop and TFO restart postgres than to let recovery mode complete. Is that unsafe? The recovery has to happen at some point. What it is doing is bringing your DB to a known valid state based on the committed transactions, what's written to disk, and what's written to the pg_xlog files. A vacuum is probably in good order after this happens. Effectively, it does the moral equivalent of unplugging the power cord and restarting itself, without the bother of needing to reboot the whole machine :-) ---(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] pgpool simple feature request
TI == Tatsuo Ishii [EMAIL PROTECTED] writes: TI Yes, I thought about that too. Probably we need two kinds of TI initializations: TI 1) initilization for each new connection (as you requested) TI 2) initilization for each new client connection I think this latter one is important. Take the case where I do some set FOO BAR values to alter the query (eg, statement timeout or sequence scan disable) which should be reset on new client connection when it re-uses a backend connection. Or is this magically handled already? Yes. pgpool issues RESET ALL at the end of each client connection. See reset_query_list directive. -- Tatsuo Ishii ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Database permissions
Art, On Feb 8, 2005, at 11:51 PM, Art Fore wrote: More confused than ever. The pg_hba.conf file shown below was what I had originaly to get phppgadmin to work. Changed the local all all ident md5 to local all all trust and it started to working again with phppgadmin, but now, access via ODBC (with MSAccess)does not work. All I get is #DELETED for every oolumn and every row. could someone explain that to me? I assume phpPgAdmin now works because the web server is on the same machine as the database. Accepting the connection has nothing to do with the machine the browser is running on. Setting this to trust and using phpPgAdmin is not good from a security standpoint. You should look at the FAQ and INSTALL files in the pgpPgAdmin installation for further details on setting this up. With ODBC, do you see authentication errors in your PostgreSQL log? This might provide the clue you need to fix the problem. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Postgresql and Macintosh
On Feb 9, 2005, at 10:39 AM, renato.barrios wrote: Please tell me if Postgresql runns in an iMac. Sure, assuming your iMac is running OS X. You'll need to install Apple's free developer tools to compile it or perhaps you might want to use one of the freely available installers such as: http://www.entropy.ch/software/macosx/postgresql/ John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] create temp table and on commit in 7.3.3
On Wed, 2005-02-09 at 10:48 +0530, Antony Paul wrote: Hi all, This is giving error in 7.3.3. CREATE TEMP TABLE temptest3(col int PRIMARY KEY) ON COMMIT DELETE ROWS; ERROR: parser: parse error at or near ON at character 51 Is this supported. No. Looking at the manual would have made it pretty obvious this was added in 7.4 -Neil ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] create temp table and on commit in 7.3.3
On Wed, Feb 09, 2005 at 06:00:52PM +1100, Neil Conway wrote: On Wed, 2005-02-09 at 10:48 +0530, Antony Paul wrote: Hi all, This is giving error in 7.3.3. CREATE TEMP TABLE temptest3(col int PRIMARY KEY) ON COMMIT DELETE ROWS; ERROR: parser: parse error at or near ON at character 51 Is this supported. No. Looking at the manual would have made it pretty obvious this was added in 7.4 The 7.3 and 7.2 documentation for CREATE TABLE both mention ON COMMIT: http://www.postgresql.org/docs/7.3/static/sql-createtable.html#SQL-CREATETABLE-COMPATIBILITY http://www.postgresql.org/docs/7.2/static/sql-createtable.html#SQL-CREATETABLE-COMPATIBILITY Should those versions describe ON COMMIT if they don't support it? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] create temp table and on commit in 7.3.3
I learned that there is an on commit clause by looking at the Compaitiblity section of the 7.3.3 docs and I was confused whether it is supporting this or not rgds Antony Paul On Wed, 9 Feb 2005 00:19:41 -0700, Michael Fuhr [EMAIL PROTECTED] wrote: On Wed, Feb 09, 2005 at 06:00:52PM +1100, Neil Conway wrote: On Wed, 2005-02-09 at 10:48 +0530, Antony Paul wrote: Hi all, This is giving error in 7.3.3. CREATE TEMP TABLE temptest3(col int PRIMARY KEY) ON COMMIT DELETE ROWS; ERROR: parser: parse error at or near ON at character 51 Is this supported. No. Looking at the manual would have made it pretty obvious this was added in 7.4 The 7.3 and 7.2 documentation for CREATE TABLE both mention ON COMMIT: http://www.postgresql.org/docs/7.3/static/sql-createtable.html#SQL-CREATETABLE-COMPATIBILITY http://www.postgresql.org/docs/7.2/static/sql-createtable.html#SQL-CREATETABLE-COMPATIBILITY Should those versions describe ON COMMIT if they don't support it? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Postgresql and Macintosh
Am Mittwoch, den 09.02.2005, 16:39 +0100 schrieb renato.barrios: Please tell me if Postgresql runns in an iMac. IIRC postgres is even part of Mac OS X. (perhaps not the latest version) Regards Tino ---(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] Postgresql and Macintosh
I don't think it's a standard part of OS X. I think it comes with Apple Remote Desktop, and it might be in the Server edition, but it's not a part of the standard version. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Feb 9, 2005, at 1:34 AM, Tino Wildenhain wrote: Am Mittwoch, den 09.02.2005, 16:39 +0100 schrieb renato.barrios: Please tell me if Postgresql runns in an iMac. IIRC postgres is even part of Mac OS X. (perhaps not the latest version) Regards Tino ---(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
