Re: [GENERAL] PostgreSQL 7.4.3 Now Available ...
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Tue, 15 Jun 2004, Tom Lane wrote: > I will push out a Fedora Core package within the next few days as well. I've already built (S)RPMS for FC 2 by just modifying spec file and modifying some patches for rpm build. All are tested and they are working fine. Oh, also updated to latest jdbc drivers. If anyone needs, I've uploaded them to: http://www.gunduz.org/postgresql/packages-7.4.3/ SRPM and -debuginfo will be uploaded tonight, since they are big files and my DSL is a bit slow. The UPLOAD-NOT-FINISHED file will be removed after these packages will be uploaded. BTW, spec file has been uploaded, too. Regards, - -- Devrim GUNDUZ devrim~gunduz.org devrim.gunduz~linux.org.tr http://www.tdmsoft.com http://www.gunduz.org -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQFA0UL3tl86P3SPfQ4RAr/PAJ4ti736mtBzfqAdJ4LCAedDQK9wRwCg0Un3 KjtXD4LFbZ+PfjWIgVpd5wA= =2XCL -END PGP SIGNATURE- ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Tracking down deadlocks
Ben, My personal solution is to patch the postgres sources so the foreign key check does NOT lock the parent record. This has the disadvantage that in some situations the foreign key semantics are not guarrantied, but for my application works just fine. I think there was a patch posted to do exactly this, but I didn't use that one. Note that the postgres regression test was still running through fine after disabling the parent record locking, so the basic FK functionality is not affected by that. Now this patch worked for me, but I won't post it, cause it's just too dangerous, and I only have it for one postgres version and won't maintain it. If you want to take your data at danger, patch for yourself ;-) Hint: take a look at src/backend/utils/adt/ri_triggers.c in the postgres sources, that file contains the foreign key logic. Cheers, Csaba. On Wed, 2004-06-16 at 20:11, Ben wrote: > So is everybody simply accepting the chance of deadlocks, thanks to > their foreign keys? Given what I know about why this problem exists, it > doesn't seem to have an easy solution but from my naive perspective > it seems like something that we shouldn't have to just live with, > either. > > On Jun 16, 2004, at 10:50 AM, Joshua D. Drake wrote: > > > Hello, > > > > Deferred checks can greatly reduce the deadlock chance because of the > > timing of the foreign key check. I won't say it can eliminate them, > > and I don't think anyone here would suggest that you don't use Foreign > > keys. > > > > Sincerely, > > > > Joshua D. Drake > > > > Ben wrote: > >> Thanks for the quick reply (and summary!). > >> According to the messages I've found on the list, basically the > >> answer seems to be, "don't do this." On the other hand, pretty much > >> every message on the subject is pre-7.4. There is some mention of > >> using deferred foreign keys to reduce the chance for a deadlock, but > >> nothing says doing that actually eliminates the chance. > >> Is this just a known limitation? In this particular instance, I > >> probably could get rid of my foreign keys and if things go bad it > >> wouldn't hurt anything but I make heavy use of foreign keys > >> throughout the rest of my schema, which are useful for the programs > >> that aren't doing data mining. I wouldn't want to get rid of those > >> foreign keys. > >> On Jun 16, 2004, at 8:54 AM, Csaba Nagy wrote: > >>> Hi Ben, > >>> > >>> Check this mailing list for "foreign keys" and "deadlock". > >>> Short info: > >>> Postgres exclusively locks the referenced records of a foreign key > >>> relationship when the child record is updated, so multiple runs (in > >>> different transactions) of one insert query could cause deadlock if > >>> they > >>> update rows which reference the same parent keys in reverse order. > >>> Check your foreign keys... > >>> > >>> HTH, > >>> Csaba. > >>> > >>> On Wed, 2004-06-16 at 17:33, Ben wrote: > >>> > I'm doing a bunch of data mining against a postgres database and > have > run into an interesting problem with deadlocks. The problem is, > postgres is detecting them and then wacking the offending process, > and > I can't figure out what's causing them. I have a ton of select > queries > (but none for update), and then a single query to insert into a > table. > Nothing selects from that table. So where could the deadlock be? > > pg_stat_activity has a column named current_query, which would seem > useful in tracking this down, but it's not being populated. > > Oh, I'm running 7.4.2. > > > ---(end of > broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > >>> > >>> > >> ---(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 > > > > > > -- > > 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 > > Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL > > > > > ---(end of broadcast)--- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Join a varchar array
Hi,
I have a field in which I save the follow:
{{8,0.58},{9,972420},{10,239544},{6,0.49},{7,0.63}}
The first field is a reference to an id in another table and the second
field is a value.
Can these values be used somehow in a select query to join the other
table?
I don't mind getting them on different rows but I would prefer to
get them back as an array.
regards
Robin
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] why no answer? [Fwd: backup and restore just with use
hello scott, this is good news (for me ;-) )! what kind of client should be available (if i search for a good hoster in near future)? greetings hans Scott Marlowe schrieb: On Wed, 2004-06-16 at 23:28, [EMAIL PROTECTED] wrote: hello, thank you for your short - but informative - answers! i don't know why this very very important thing isn't implemented by the jdbc (and other...) driver. it shouldn't only be managable by those sitting in front of the machine but also via my program to handle some critical situation in the way i want it to be done (and if i hoste my webapp somewhere in another continent, i won't be able to sit in front of the servers console-screen). to recode pg_dump is far off my time and (maybe) skills. why isn't it implemented for easier access? Well, pg_dump doesn't have to be run on the server, it can be run on a client halfway around the world from the server and work just fine. ---(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] index with LIKE
hello scott, disable enable_seqscan still does no force the backend to use indexes. so it looks like a locale problem, right? I checked lc_* vars on both servers: typemainserver slave lc_collate Cde_DE.UTF-8 lc_ctype[EMAIL PROTECTED] de_DE.UTF-8 lc_messages [EMAIL PROTECTED] de_DE.UTF-8 I guest "lc_collate" is the problem, isn't it? [EMAIL PROTECTED] wrote: > 1: Is index capable of being used on the slaves, or are they > just making > bad decisions? Try disabling seq scans on the slave servers > for testing > and see if they can use the index. Use "set enable_seqscan = FALSE;" > > If they can't use the index then, then they are likely in the wrong > locale, and you'll need to re initdb them to get them to use an index. > > If they can use the index then we have tuning issue. > > > ---(end of > broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to > [EMAIL PROTECTED]) -- Mit freundlichem Gruß Henrik Steffen Geschäftsführer top concepts Internetmarketing GmbH Am Steinkamp 7 - D-21684 Stade - Germany http://www.topconcepts.de Tel. +49 1805 9977 501* mail: [EMAIL PROTECTED]Fax. +49 1805 9977 502* SMS Versand ab 9.9 Cent: http://sms-gw.topconcepts.de Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563 *) EUR 0,12/Min. (CNS24) ---(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] index with LIKE
Henrik Steffen wrote: hello scott, disable enable_seqscan still does no force the backend to use indexes. so it looks like a locale problem, right? I checked lc_* vars on both servers: typemainserver slave lc_collate Cde_DE.UTF-8 lc_ctype[EMAIL PROTECTED] de_DE.UTF-8 lc_messages [EMAIL PROTECTED] de_DE.UTF-8 I guest "lc_collate" is the problem, isn't it? If it's an encoding issue, then you may need to change the index operator type as suggested in one of the previous replies: Check out the link to the indexes-opclass below, and try recreating one of the indexes in the slave with a different index operator, and see if the index starts getting used. Of course it's a pain because the schemas are then slightly different... but then so is the encoding... Hope that helps. If it does please let us know. Thanks. John Sidney-Woollett Martijn van Oosterhout wrote: The classic issue is what encoding are the databases. Anything other than C and like won't use indexes. Unless you use text_pattern_ops. See http://www.postgresql.org/docs/7.4/static/indexes-opclass.html ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Join a varchar array
Robin Ericsson wrote:
Hi,
I have a field in which I save the follow:
{{8,0.58},{9,972420},{10,239544},{6,0.49},{7,0.63}}
The first field is a reference to an id in another table and the second
field is a value.
Can these values be used somehow in a select query to join the other
table?
You appear to be using the array as a replacement for a table. Don't do
that. From the little information you've provided, it looks like you
want a separate table (a_ref, b_ref, float_val) where a_ref references
the current table, b_ref the other table.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] Join a varchar array
On Thu, 2004-06-17 at 11:38, Richard Huxton wrote:
> Robin Ericsson wrote:
> > Hi,
> >
> > I have a field in which I save the follow:
> > {{8,0.58},{9,972420},{10,239544},{6,0.49},{7,0.63}}
> >
> > The first field is a reference to an id in another table and the second
> > field is a value.
> >
> > Can these values be used somehow in a select query to join the other
> > table?
>
> You appear to be using the array as a replacement for a table. Don't do
> that. From the little information you've provided, it looks like you
> want a separate table (a_ref, b_ref, float_val) where a_ref references
> the current table, b_ref the other table.
Thats correct, I choose this way because there will be a lot of entries
and it will grow pretty quickly, but maybe postgres will be able to
handle this?
If I went for a separate table it will contain over 3 milj. entries
within in a couple of weeks and still grow after that but not at the
same speed as the first weeks.
regards
Robin
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] Join a varchar array
Robin Ericsson wrote: On Thu, 2004-06-17 at 11:38, Richard Huxton wrote: You appear to be using the array as a replacement for a table. Don't do that. From the little information you've provided, it looks like you want a separate table (a_ref, b_ref, float_val) where a_ref references the current table, b_ref the other table. Thats correct, I choose this way because there will be a lot of entries and it will grow pretty quickly, but maybe postgres will be able to handle this? If it can't handle the data in tables, why should it handle it in arrays? You're using a relational database, stick to relations. If I went for a separate table it will contain over 3 milj. entries within in a couple of weeks and still grow after that but not at the same speed as the first weeks. There are people here with hundreds of millions of rows in some tables. Keep your design simple and clean, apply indexes once you have some idea of what activity your system will see and subscribe to the performance list. PG has some odd corner-cases where performance could be better, but on the whole it does an excellent job of coping with large numbers of users and large amounts of data. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] [Fwd: Postfix SMTP server: errors from avalanche.netmar.com[198.69.224.177]]
Can someone tell me if I'm blocking spam here of if I'm somehow blocking some of the emails from this list? I'm definitely getting email, but I keep getting these messages popping up on my servers. Does anyone know why? Original Message Return-Path: <[EMAIL PROTECTED]> X-Original-To: postmaster Delivered-To: [EMAIL PROTECTED] Received: by cling.tacocat.net (Postfix) id 6D9B34C07A; Wed, 16 Jun 2004 19:19:33 -0400 (EDT) Date: Wed, 16 Jun 2004 19:19:33 -0400 (EDT) From: [EMAIL PROTECTED] (Mail Delivery System) To: [EMAIL PROTECTED] (Postmaster) Subject: Postfix SMTP server: errors from avalanche.netmar.com[198.69.224.177] Message-Id: <[EMAIL PROTECTED]> Transcript of session follows. Out: 220 cling.tacocat.net ESMTP Postfix (Debian/GNU) In: EHLO avalanche.technically-sound.com Out: 250-cling.tacocat.net Out: 250-PIPELINING Out: 250-SIZE 1024 Out: 250-VRFY Out: 250-ETRN Out: 250 8BITMIME In: MAIL FROM:<[EMAIL PROTECTED]> SIZE=3112 Out: 250 Ok In: RCPT TO:<[EMAIL PROTECTED]> Out: 450 <[EMAIL PROTECTED]>: Sender address rejected: unverified address: Address verification in progress In: DATA Out: 554 Error: no valid recipients In: RSET Out: 250 Ok In: QUIT Out: 221 Bye ---(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] index with LIKE
hello john, thanks for your email! changing the index type to "text_pattern_ops" solved the problem. I didn't quite get the point, when Joseph Shraibman first sent the link regarding operator classes. My apologies. However, I would not fancy to change all (hundrets) of indexes now. Would changing the lc_collate setting to 'C' solve this issue as well? Thanks again, -- Mit freundlichem Gruß Henrik Steffen Geschäftsführer top concepts Internetmarketing GmbH Am Steinkamp 7 - D-21684 Stade - Germany http://www.topconcepts.de Tel. +49 1805 9977 501* mail: [EMAIL PROTECTED]Fax. +49 1805 9977 502* SMS Versand ab 9.9 Cent: http://sms-gw.topconcepts.de Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563 *) EUR 0,12/Min. (CNS24) > -Ursprüngliche Nachricht- > Von: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Im Auftrag von > John Sidney-Woollett > Gesendet: Donnerstag, 17. Juni 2004 11:36 > An: Henrik Steffen > Cc: 'Scott Marlowe'; pgsql > Betreff: Re: [GENERAL] index with LIKE > > > Henrik Steffen wrote: > > >hello scott, > > > >disable enable_seqscan still does no force the backend > >to use indexes. > > > >so it looks like a locale problem, right? > > > >I checked lc_* vars on both servers: > > > >typemainserver slave > >lc_collate Cde_DE.UTF-8 > >lc_ctype[EMAIL PROTECTED] de_DE.UTF-8 > >lc_messages [EMAIL PROTECTED] de_DE.UTF-8 > > > > > >I guest "lc_collate" is the problem, isn't it? > > > > > > If it's an encoding issue, then you may need to change the index > operator type as suggested in one of the previous replies: > > Check out the link to the indexes-opclass below, and try > recreating one > of the indexes in the slave with a different index operator, > and see if > the index starts getting used. Of course it's a pain because > the schemas > are then slightly different... but then so is the encoding... > > Hope that helps. If it does please let us know. Thanks. > > John Sidney-Woollett > > Martijn van Oosterhout wrote: > > > The classic issue is what encoding are the databases. Anything other > > than C and like won't use indexes. > > Unless you use text_pattern_ops. See > http://www.postgresql.org/docs/7.4/static/indexes-opclass.html > > > ---(end of > broadcast)--- > TIP 5: Have you checked our extensive FAQ? > http://www.postgresql.org/docs/faqs/FAQ.html ---(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] [PERFORM] Visual Explain
On 17/06/2004 12:10 Adam Witney wrote: Will this run on other platforms? OSX maybe? It's a Java app so it runs on any any platform with a reasonably modern Java VM. -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Off Topic: Book about High available / Load Balancing / Cluster
Hi All, Someone will can say me anything about the Tony Bourke's book "Server Load Balancing"? I need study these subjects. Any recommendation? Thanks in advance. Renato Cramer. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] index with LIKE
Henrik
Thanks for the info.
What encoding you should use depends on your data, and how you want
records sorted etc. You'll have to figure out what is more suitable for
you - I cannot answer that for you.
To solve your master/slave index problem, why not rebuild the slave
databases using the C encoding instead of your de_DE.UTF-8 encoding?
Otherwise create a SQL statement to extract all the (text) indexes from
your database where a like operation will be used, and use it to drop
and re-create the index.
Something along these lines can be used to drop your indexes ( you need
to execute the results from the query)
SELECT 'drop index '||n.nspname||'.'||c.relname||';'
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('i','')
AND n.nspname IN ('customer', 'photo')
AND c.relname NOT LIKE '%_pkey';
But you'll need to be more specific about which schemas and indexes to
drop and re-create. The SQL to regenerate the new indexes, I'll leave to
you to figure out! :)
Good luck.
John Sidney-Woollett
Henrik Steffen wrote:
hello john,
thanks for your email!
changing the index type to "text_pattern_ops" solved the problem.
I didn't quite get the point, when Joseph Shraibman first sent the link
regarding operator classes. My apologies.
However, I would not fancy to change all (hundrets) of indexes now.
Would changing the lc_collate setting to 'C' solve this issue as well?
Thanks again,
--
Mit freundlichem Gruß
Henrik Steffen
Geschäftsführer
top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
http://www.topconcepts.de Tel. +49 1805 9977 501*
mail: [EMAIL PROTECTED]Fax. +49 1805 9977 502*
SMS Versand ab 9.9 Cent: http://sms-gw.topconcepts.de
Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
*) EUR 0,12/Min. (CNS24)
-Ursprüngliche Nachricht-
Von: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Im Auftrag von
John Sidney-Woollett
Gesendet: Donnerstag, 17. Juni 2004 11:36
An: Henrik Steffen
Cc: 'Scott Marlowe'; pgsql
Betreff: Re: [GENERAL] index with LIKE
Henrik Steffen wrote:
hello scott,
disable enable_seqscan still does no force the backend
to use indexes.
so it looks like a locale problem, right?
I checked lc_* vars on both servers:
typemainserver slave
lc_collate Cde_DE.UTF-8
lc_ctype[EMAIL PROTECTED] de_DE.UTF-8
lc_messages [EMAIL PROTECTED] de_DE.UTF-8
I guest "lc_collate" is the problem, isn't it?
If it's an encoding issue, then you may need to change the index
operator type as suggested in one of the previous replies:
Check out the link to the indexes-opclass below, and try
recreating one
of the indexes in the slave with a different index operator,
and see if
the index starts getting used. Of course it's a pain because
the schemas
are then slightly different... but then so is the encoding...
Hope that helps. If it does please let us know. Thanks.
John Sidney-Woollett
Martijn van Oosterhout wrote:
The classic issue is what encoding are the databases. Anything other
than C and like won't use indexes.
Unless you use text_pattern_ops. See
http://www.postgresql.org/docs/7.4/static/indexes-opclass.html
---(end of
broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
---(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] MySQL to Postgres.
Dear my friends... I am planning to migrate my database from MySQL to Postgres since the MySQL does not have subquery except it 4.1. And I can not wait until the 4.1 stable version released and included in SuSE distro. I did mysqldump (into a file, basisdata.txt) and I am going to do its query qith pgaccess or pgsql. I tried to import the database (do the query of basisdata.txt) with pgaccess but I couldn't. Because pgaccess support only one by one importing table. I tried with pgsql, look for the suitable command to do that with "\h" in the pgsql console. But I could not find the suitable command. Anybody would be so nice to tell me how I can do that with pgaccess and/or pgsql ? Thank you very much in advance. __ Do you Yahoo!? New and Improved Yahoo! Mail - 100MB free storage! http://promotions.yahoo.com/new_mail ---(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] Database accesss using plperl
On Wed, 2004-06-16 at 19:05, Paul Thomas wrote: > On 16/06/2004 21:27 Robert Fitzpatrick wrote: > > I have plperl installed my PostgreSQL 7.4.2 server, but from what I > > understand in chapter 39.3 of the docs, you cannot access the databases > > without DBD::PgSPI. According to the readme for that module, it will > > only run on the untrusted plperlu. > > > > Is this the only way to run queries (SELECT, INSERT, UPDATE) using > > plperl? > > Are you talking about writing functions/stored procedures in PERL or > accessing the database from a PERL program because what you have read is > specific to functions/stored procedures. (sorry for the bad/absent > punctuation but I've just come back from the pub after a generous quantity > of Old Speckled Hen) I'm talking about writing PostgreSQL stored procedures using Perl that access the database via queries (like pl/pgsql, maybe using DBI:Pg) for SELECTs, INSERTs, UPDATEs, etc. -- Robert ---(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
