Re: [GENERAL] The rule question before, request official documentation on the problem
Hmm. I just came up with a far more problematic case too and wonder if documentation is enough. Maybe we should warn about potential problems more loudly. Imagine the following case: insert into test_table (test) values (random()) where an insert rule propagates the changes faithfully to the next table. In short, all we are doing is inserting random numbers into different tables and generating them on each insert. In short, rules provide no guarantee of predictable behavior because queries can always mess with them. Let me put that a different way: rules can *only* be used where data integrity is not at stake. My own thinking is that it might be time to make an official recommendation that they are only safe for views. Best Wishes, Chris Travers Stuart Cooper wrote: My request at this point is to officially and clearly document this as a substantial limitation of rules. It is not obvious that this is how rules are supposed to behave in this case, and even assuming that the current behavior is desired, it would be nice to let us know this :-) It's documented. Section 35.3.1 of Postgresql 8.2 PDF docmentation, 2nd last paragraph: *** For any reference to NEW, the target list of the original query is searched for a corresponding entry. If found, that entry's expression replaces the reference. *** "expression" is the key term here. NEW.id is an expression, *not* a value. Cheers, Stuart. begin:vcard fn:Chris Travers n:Travers;Chris email;internet:[EMAIL PROTECTED] tel;work:509-888-0220 tel;cell:509-630-7794 x-mozilla-html:FALSE version:2.1 end:vcard ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] The rule question before, request official documentation on the problem
I just came up with a far more problematic case too and wonder if documentation is enough. Maybe we should warn about potential problems more loudly. Imagine the following case: insert into test_table (test) values (random()) where an insert rule propagates the changes faithfully to the next table. In short, all we are doing is inserting random numbers into different tables and generating them on each insert. In short, rules provide no guarantee of predictable behavior because queries can always mess with them. Rules mess with queries. For data copying/archiving kinds of tasks, triggers are a better bet, like you suggested in your original post. Let me put that a different way: rules can *only* be used where data integrity is not at stake. My own thinking is that it might be time to make an official recommendation that they are only safe for views. NEW and OLD mean different things in a PL/pgSQL context and a Rules context. In PL/pgSQL NEW and OLD are values, in Rules (which specifically mess with queries) they are expressions. The fact that the same words mean different things in different contexts is a bit unfortunate but not as messy as say using "NEWEXPR" in the Rules context would be. Once you appreciate the difference, there's no confusion. Cheers, Stuart. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Do I need serializable for this query?
On Tue, Apr 10, 2007 at 10:52:11PM +0200, Peter Eisentraut wrote: > William Garrison wrote: > > I have a table that keeps running totals. It is possible that this > > would get called twice simultaneously for the same UserID. Do I need > > to put this in a serializable transaction? > > Transaction isolation is only a question of interest if you have more > than one statement in a transaction. Not true; the isolation level is also relevant if you're doing concurrent updates or deletes (although, as the documentation mentions, PostgreSQL's serializable isolation level doesn't guarantee true serializability). create table test (id integer primary key, t text); insert into test (id, t) values (1, 'a'); T1: begin isolation level serializable; T2: begin isolation level serializable; T1: update test set t = 'b' where id = 1; T2: update test set t = 'c' where id = 1; -- blocks T1: commit; T2: ERROR: could not serialize access due to concurrent update -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] The rule question before, request official documentation on the problem
Rules mess with queries. For data copying/archiving kinds of tasks, triggers are a better bet, like you suggested in your original post. Let me put that a different way: rules can *only* be used where data integrity is not at stake. My own thinking is that it might be time to make an official recommendation that they are only safe for views. NEW and OLD mean different things in a PL/pgSQL context and a Rules context. In PL/pgSQL NEW and OLD are values, in Rules (which specifically mess with queries) they are expressions. The fact that the same words mean different things in different contexts is a bit unfortunate but not as messy as say using "NEWEXPR" in the Rules context would be. Since we now have UPDATE/INSERT/DELETE RETURNING, one could imagine the rules using these to access the actual rows and not the expressions... But there is a perfectly valid argument against that : - There already is a mechanism designed specifically for this purpose (triggers). - It works perfectly. - Rules are supposed to rewrite queries to do stuff like views. It should be mentioned in the docs, though : someone with an account on the PG site should copypaste this mail exchange in the comments field... ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Acces via applets
The bottom line question is can an applet served to a client machine other than the one the postrgres db resides on read that db? An applet I've written and tested on the same box as my database runs fine. When I try running the applet from another computer on the network I get an error trying to access the database. Even when I added the ip of that computer to the pg_hba.conf it still didn't work. Why does an applet served to the same box as the db work, but when served to another box not work? Is there a configuration setting I'm missing or is this simply not possible? The applet has been self signed.using the java's keytool and jarsigner programs. I've had this same setup working with SQL Server for years now. Self signed applet reads db on separate box. What is going on with PostgreSQL? Sorry for rambling, I'm just so frustrated right now. Thanks in advance. Marc
[GENERAL] COPY FROM file with zero-delimited fields
Hi all, I wonder if there's a way to use COPY FROM command when dealing with a data file containing records whose fields are delimited with zero- byte? Or I'd better change the delimiter? :) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] ORDER BY with UNION
Using Postgresql 8.2.3 The following query functions correctly: select lastname as name from person where lastname ='Smith' union select firstname as name from person where firstname = 'John' order by name; --- The following query generates an Error: (ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of the result columns SQL state: 0A000) select lastname as name from person where lastname ='Smith' union select firstname as name from person where firstname = 'John' order by upper(name); I would have thought that if the first query worked this query should have worked. The documentation for 8.2.3 indicated that the order by would function on an expression when used with a union. --- WorkAround: select * from (select lastname as name from person where lastname ='Smith' union select firstname as name from person where firstname = 'John') as whatever order by upper(name); Thanks ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Do I need serializable for this query? -- Part 2
This is part 2 of my "serializable" question from earlier. Thanks to everyone who answered the first part. (Hopefully there will be no part 3) When a user adds a record, I update a table with totals. But when a user deletes data, I subtract the totals. That presents a more interesting situation since it is theoretically possible to add a record and delete one at the same time: For simplicity sake, the two tables are something like: item(itemid int, data...) history(itemid int, versionid int, data...) -- This deletes the item and all the history records for the item CREATE FUNCTION DeleteItem(IN _UserID int, IN _ItemID int) RETURNS void AS $$ DECLARE mycount int; total bigint; BEGIN -- Cotal and total of data to be deleted SELECT COUNT(*), COALESCE(SUM(clientsize),0) INTO mycount,total FROM history WHERE userid= $1 AND itemid=$2; -- Potential problem point #1 -- Call the stored proc to update the count and total -- This is the one from my last email PERFORM sbp_UpdateTotals($1,-mycount,-total); -- Potential problem point #2 -- Delete the records DELETE FROM history WHERE userid = $1 AND itemid=$2; DELETE FROM items WHERE userid = $1 AND itemid=$2; END $$ LANGUAGE 'plpgsql' VOLATILE; Suppose the user adds another record to the history table with the same itemid as the one being deleted. If they do this at one of the two "problem points" then that history record will be deleted, but it will not be subtracted from the totals. Am I understanding this properly? In my system, this is highly unlikely, and probably not even something we care about very much. But I still want to know what to do. It seems to me I could a few things to fix this: 1) Make this serializable - I'm not even sure this will help, since adding a new history record won't change any records that this touches. Would PostgreSQL even realize that adding a history record would have changed the results of the select? 2) Table lock 3) Make the first select statement store the PK(itemid,versionid) of the history records and then only delete those records at the end. 4) Somehow calculate the count and total during the delete statement 5) Compare the # of records deleted with the # of records selected and throw/rollback if they mismatch. I'm curious which one I should do, and if my solutions are all valid. Also, I'm under the impression that I don't need to do any sort of begin/end transaction stuff in here, since PostgreSql does that implicitly with any stored procedure. Is that correct? Thanks to anyone who can assist. Florian G. Pflug wrote: William Garrison wrote: I have a table that keeps running totals. It is possible that this would get called twice simultaneously for the same UserID. Do I need to put this in a serializable transaction? Since this gets called often, could it be a performance problem if I make it serializable? CREATE FUNCTION UpdateTotals(IN UserID int, IN AddToCount int, IN AddToSize bigint) RETURNS void AS $$ BEGIN UPDATE Totals SET TotalSize = TotalSize + $2, TotalCount = TotalCount + $3 WHERE UserID = $1; END IF; END $$ LANGUAGE 'plpgsql' VOLATILE; Where is that stray "END IF;" comming from? Anyway, this should be safe, and work even more reliably in read-committed mode than in serializable mode. In serializeable mode, if the Total of the same user is updated by two transactions simultanously, you'll get a SerializationError. Read-Committed mode OTOH will make sure that it uses the latest version of the tuple for calculating the new values. Usually, you need serializable mode if you do updates based on the results of previous selects. greetings, Florian Pflug ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Do I need serializable for this query? -- Part 2
Should I just use a trigger to update these totals? That way concurrent deletes/updates would be guaranteed to update the totals. The only down side is that deleting 10 records would result in 10 updates to the totals. But deleting is rare enough that it probably isn't a problem. William Garrison wrote: This is part 2 of my "serializable" question from earlier. Thanks to everyone who answered the first part. (Hopefully there will be no part 3) When a user adds a record, I update a table with totals. But when a user deletes data, I subtract the totals. That presents a more interesting situation since it is theoretically possible to add a record and delete one at the same time: For simplicity sake, the two tables are something like: item(itemid int, data...) history(itemid int, versionid int, data...) -- This deletes the item and all the history records for the item CREATE FUNCTION DeleteItem(IN _UserID int, IN _ItemID int) RETURNS void AS $$ DECLARE mycount int; total bigint; BEGIN -- Cotal and total of data to be deleted SELECT COUNT(*), COALESCE(SUM(clientsize),0) INTO mycount,total FROM history WHERE userid= $1 AND itemid=$2; -- Potential problem point #1 -- Call the stored proc to update the count and total -- This is the one from my last email PERFORM sbp_UpdateTotals($1,-mycount,-total); -- Potential problem point #2 -- Delete the records DELETE FROM history WHERE userid = $1 AND itemid=$2; DELETE FROM items WHERE userid = $1 AND itemid=$2; END $$ LANGUAGE 'plpgsql' VOLATILE; Suppose the user adds another record to the history table with the same itemid as the one being deleted. If they do this at one of the two "problem points" then that history record will be deleted, but it will not be subtracted from the totals. Am I understanding this properly? In my system, this is highly unlikely, and probably not even something we care about very much. But I still want to know what to do. It seems to me I could a few things to fix this: 1) Make this serializable - I'm not even sure this will help, since adding a new history record won't change any records that this touches. Would PostgreSQL even realize that adding a history record would have changed the results of the select? 2) Table lock 3) Make the first select statement store the PK(itemid,versionid) of the history records and then only delete those records at the end. 4) Somehow calculate the count and total during the delete statement 5) Compare the # of records deleted with the # of records selected and throw/rollback if they mismatch. I'm curious which one I should do, and if my solutions are all valid. Also, I'm under the impression that I don't need to do any sort of begin/end transaction stuff in here, since PostgreSql does that implicitly with any stored procedure. Is that correct? Thanks to anyone who can assist. Florian G. Pflug wrote: William Garrison wrote: I have a table that keeps running totals. It is possible that this would get called twice simultaneously for the same UserID. Do I need to put this in a serializable transaction? Since this gets called often, could it be a performance problem if I make it serializable? CREATE FUNCTION UpdateTotals(IN UserID int, IN AddToCount int, IN AddToSize bigint) RETURNS void AS $$ BEGIN UPDATE Totals SET TotalSize = TotalSize + $2, TotalCount = TotalCount + $3 WHERE UserID = $1; END IF; END $$ LANGUAGE 'plpgsql' VOLATILE; Where is that stray "END IF;" comming from? Anyway, this should be safe, and work even more reliably in read-committed mode than in serializable mode. In serializeable mode, if the Total of the same user is updated by two transactions simultanously, you'll get a SerializationError. Read-Committed mode OTOH will make sure that it uses the latest version of the tuple for calculating the new values. Usually, you need serializable mode if you do updates based on the results of previous selects. greetings, Florian Pflug ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] 8.2.3 AutoVacuum not running
"Schwenker, Stephen" <[EMAIL PROTECTED]> writes: > It says it's on and I have also turned on all stats collecting. My guess is that it's actually running but is not choosing to do any vacuums for some reason. Try setting log_min_messages to DEBUG5 for awhile and trawling the postmaster log for evidence. You should at minimum see messages indicating that the postmaster is launching an autovac worker process once a minute; the next question is what that process is doing with itself. (You might want to adjust log_line_prefix to include PID so that you can track which messages come from which process. I find including a timestamp is often helpful also.) regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] COPY FROM file with zero-delimited fields
On 11 Apr 2007 00:25:50 -0700, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: Hi all, I wonder if there's a way to use COPY FROM command when dealing with a data file containing records whose fields are delimited with zero- byte? Or I'd better change the delimiter? :) you can always run it through tr or similar tools first. merlin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] What about SkyTools?
Hello. Have anybody used SkyTools in production environment? What's the impression? In practice - is it now more preferrable than Slony or not yet?
[GENERAL] Select taking excessively long; Request help streamlining.
If this message has already appeared on the list, I apologize. My system tried
to temporarily freeze up when I attempted to send this message a few minutes
ago, and I do not know if I hit send before it halted or not.
I am working with a php program that is designed to enter the database,
execute a rather convoluted select (statement seeks out all records in a
specific table that match the input criteria, then flows through the table
links [x.foo = y.bar] to gather all data related to the records it is looking
for), then display the results as a web page.
I admit that the primary table the select statement looks at has a large
number of records (~ 6 million) in it, but I still don't think it should take
as long to accomplish this task as it does. I suspect that the real problem
lies in the way I built the select statement, that it is somehow clunky and
unwieldy.
A copy of the statement and explain results on it appear below. Would
someone please assist me in figuring out how to more appropriately streamline
this statement?
attest=# EXPLAIN select substring(ttrans.tran_dt, 1, 10) as tran_dt,
ttrans.dist_id as dist_id, ttrans.cntrct_id as cntrct_id, cntrt.cntrtyp_cd as
cntrt_type, cntrt.actual_amt as cntrt_amt, acntrec.mth_reck as mth_reck,
persn.frst_nm as fnm, persn.lst_nm as lnm from ttrans, cntrt, acntrec, persn,
custm, addru where ttrans.tran_dt >= '2007-03-01' and ttrans.tran_dt <
'2007-03-31' and ttrans.cntrct_id = cntrt.cntrct_id and cntrt.cntrct_seq =
addru.cntrct_seq and addru.aunit_seq = acntrec.aunit_seq and (cntrt.cntrtyp_cd
= 255 or cntrt.cntrtyp_cd = 260) and cntrt.clnt_seq = custm.clnt_seq and
custm.person_seq = persn.person_seq and acntrec.cd_inst = 49 and acntrec.months
= 49 and cntrt.dow_flg1 = 'NO' order by ttrans.dist_id asc, cntrt.cntrtyp_cd
asc, cntrt.cntrct_id asc, cntrt.cntrct_id asc;
QUERY PLAN
--
Sort (cost=183688.49..183688.50 rows=1 width=125)
Sort Key: ttrans.dist_id, cntrt.cntrtyp_cd, cntrt.cntrct_id
-> Nested Loop (cost=0.00..183688.48 rows=1 width=125)
-> Nested Loop (cost=0.00..183683.87 rows=1 width=106)
Join Filter: (("inner".cntrct_id)::bpchar = "outer".cntrct_id)
-> Nested Loop (cost=0.00..21820.21 rows=1 width=48)
-> Nested Loop (cost=0.00..21815.45 rows=1 width=48)
-> Nested Loop (cost=0.00..21793.06 rows=4
width=43)
-> Seq Scan on cntrt (cost=0.00..21771.81
rows=4 width=43)
Filter: cntrtyp_cd)::text =
'255'::text) OR ((cntrtyp_cd)::text = '260'::text)) AND (dow_flg1 =
'NO'::bpchar))
-> Index Scan using fk_cntrct on addru
(cost=0.00..5.30 rows=1 width=8)
Index Cond: ("outer".cntrct_seq =
addru.cntrct_seq)
-> Index Scan using fk_aunit on acntrec
(cost=0.00..5.59 rows=1 width=13)
Index Cond: ("outer".aunit_seq =
acntrec.aunit_seq)
Filter: ((cd_inst = 49) AND ((months)::text =
'49'::text))
-> Index Scan using "pkeyCUSTM" on custm
(cost=0.00..4.75 rows=1 width=8)
Index Cond: ("outer".clnt_seq = custm.clnt_seq)
-> Seq Scan on ttrans (cost=0.00..161492.77 rows=29671
width=58)
Filter: ((tran_dt >= '2007-03-01 00:00:00-06'::timestamp
with time zone) AND (tran_dt < '2007-03-31 00:00:00-05'::timestamp with time
zone))
-> Index Scan using "pkeyPERSN" on persn (cost=0.00..4.59 rows=1
width=27)
Index Cond: ("outer".person_seq = persn.person_seq)
(21 rows)
Thank you for your consideration.
-
Need Mail bonding?
Go to the Yahoo! Mail Q&A for great tips from Yahoo! Answers users.
Re: [GENERAL] The rule question before, request official documentation on the problem
Listmail wrote: Since we now have UPDATE/INSERT/DELETE RETURNING, one could imagine the rules using these to access the actual rows and not the expressions... But there is a perfectly valid argument against that : - There already is a mechanism designed specifically for this purpose (triggers). - It works perfectly. - Rules are supposed to rewrite queries to do stuff like views. Agreed. I have narrowed the problem cases down to a subset I think should be mentioned in the docs. DO ALSO rules involving NEW are fundamentally dangerous to the integrity of data because NEW is not guaranteed to be internally consistent. DO INSTEAD rules are fine (there is only one NEW), as are any DO ALSO rules involving OLD. We already protect against programmers using unsafe and non-standard quote escapes. I have sent in my cases to a number of other people, some of which are deeply involved in PostgreSQL development, and the initial behavior was not properly predicted by any of them. This is why I say that if this is the defined behavior of rules, that a clear and obvious warning needs to be placed in the docs that this is dangerous and in every case I can think of, not something you want to use a rule for. Imagine, for example, that we have an application that is built. Uses DO ALSO rules with NEW to replicate user-supplied data from one table to an audit trail or the like, Everything works fine until someone decides to load up a database with random data. The programmer did not foresee this and put his trust in PostgreSQL's features for data integrity. Given the comments I found in the docs, I suspect that people *are* using DO ALSO rules frequently when these are dangerous. Since this can cause problems based on user-supplied input, this is a problem. One of the things that causes me to favor PostgreSQL for all my projects is the strong emphasis on data integrity by the community, perhaps better than any other RDBMS out there. Being unwilling to warn clearly and loudly about unsafe features does undermine that commitment. It should be mentioned in the docs, though : someone with an account on the PG site should copypaste this mail exchange in the comments field... For 90% of what I do, I use the local copy of the docs. My concern is that (at least in 8.1) there is no obvious warning about DO ALSO rules using NEW to be inherently nondeterministic. I checked the online 8.2 docs and while there was the bit about the expression substitution, there still was not a warning about this behavior being fundamentally nondeterministic. I would like to see a note in the section comparing triggers to rules explaining that this subset of rules is not deterministic. Best Wishes, Chris Travers begin:vcard fn:Chris Travers n:Travers;Chris email;internet:[EMAIL PROTECTED] tel;work:509-888-0220 tel;cell:509-630-7794 x-mozilla-html:FALSE version:2.1 end:vcard ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [pgsql-www] [GENERAL] programmatic way to fetch latest release for a given major.minor version
On Tue, Apr 10, 2007 at 12:18:52PM +0200, Magnus Hagander wrote: > On Tue, Apr 10, 2007 at 09:52:52AM +0100, Dave Page wrote: > > Magnus Hagander wrote: > > > 2) Create a new file with a specific schema. Something like: > > > > > > > > > > > > > > > This is the most lightweight solution. > > > > More like: > > > > > > > > > > > Ok, I've added one of these as well now, alongside the RSS feed. You can get the pg specific XML file at: http://www.postgresql.org/versions.xml If someone wants the schema change, react *now*. Later on we can only append to it, and not change it :) //Magnus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [pgsql-www] [GENERAL] programmatic way to fetch latest release for a given major.minor version
If someone wants the schema change, react *now*. Later on we can only append to it, and not change it :) Since I like to complain... Suppose you someday add another dot, or a "b" for beta, wouldn't it be better to have 823 ... or ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Transactions through JDBC
Hi all!
Actually I have a client-server application with one server and many
clients each one of which opens a different connection to the postgres
database. In order to avoid those known problems with the execution of
the different clients' operations at database, I implemented
everything in this way. Each operation is made so:
conn.executeUpdate("BEGIN");
conn.execute(...)
conn.execute(...)
conn.execute(...)
conn.executeUpdate("COMMIT");
May it be considered right, or am I making something wrong? I use JDBC
driver for postgres 8.1
Thanks!
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[GENERAL] indexes, and tables within tables
I was reading an interview with Chris Date the other day, which got me thinking about a problem I'm currently having: I have an application that keeps information in 6 denormalized tables, D1 through D6. To tie things together, all these tables have a common column, let's call it obj_id. There is an additional table P, that is generated from a query that uses D1 through D3 and JOINS them to another table, X. P also has this obj_id column. All these tables are pretty big, so I keep them indexed by obj_id. Table P in particular is a huge time series which I also need to keep indexed by date, so there is an index on P on (obj_id, date). Now, my problem: I need to keep P indexed, because I need to use it in queries very often, and I need them to be quick. However, due to the indexing, putting new rows in P takes forever, which it didn't used to when it wasn't indexed. I know that the typical solution is to drop the index, import the new data, and create the index again. However, this doesn't work for me, as the application is pretty interactive and users could be querying P and adding rows to it (on different obj_id's) simultaneously. The ideal situation would be if reindexing didn't reindex the whole table, but just those entries added with the given obj_id. This, as far as I know, is not possible. I don't really need table-wide indexes. The data in different obj_id's is not compared very often - what happens all the time is the cross-table relationships on the same obj_id. So what Date seems to wish for RDBMs to handle objects would work here: I would have a table of obj_id's, with columns D1 .. D6 and P. The object stored in a (row, column) would be a table with the appropriate entries, and in the case of P, an index on date. Retrieving all the related entries for a given obj_id would be trivial, as would inserting a new table object into the P column, for a given obj_id. Are there plans to handle table objects as possible values in other tables, as Date seems to propose? Is there currently a way of easing the design problem here? Any advice on redesigning the data? Thanks Jaime *** Bear Stearns is not responsible for any recommendation, solicitation, offer or agreement or any information about any transaction, customer account or account activity contained in this communication. Bear Stearns does not provide tax, legal or accounting advice. You should consult your own tax, legal and accounting advisors before engaging in any transaction. In order for Bear Stearns to comply with Internal Revenue Service Circular 230 (if applicable), you are notified that any discussion of U.S. federal tax issues contained or referred to herein is not intended or written to be used, and cannot be used, for the purpose of: (A) avoiding penalties that may be imposed under the Internal Revenue Code; nor (B) promoting, marketing or recommending to another party any transaction or matter addressed herein. *** ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] The rule question before, request official documentation on the problem
On Wed, Apr 11, 2007 at 09:21:46AM -0700, Chris Travers wrote: > DO ALSO rules involving NEW are fundamentally dangerous to the integrity > of data because NEW is not guaranteed to be internally consistent. DO > INSTEAD rules are fine (there is only one NEW), as are any DO ALSO rules > involving OLD. Huh? The entered data is exactly what was asked. The whole system is totally deterministic and works as designed. Really, I'd prefer a warning stating that people shouldn't use rules unless they absolutly know what they're doing. > One of the things that causes me to favor PostgreSQL for all my projects > is the strong emphasis on data integrity by the community, perhaps > better than any other RDBMS out there. Being unwilling to warn clearly > and loudly about unsafe features does undermine that commitment. The problem is that the are some things that really need rules. However, I think you can safely say: Unless what you want can only be done using rules, use triggers. They are far more obivous. Not to mention that using a rule for auditing is silly, since it won't record what actually went into the table. > For 90% of what I do, I use the local copy of the docs. My concern is > that (at least in 8.1) there is no obvious warning about DO ALSO rules > using NEW to be inherently nondeterministic. Wrong word. It's not non-deterministic, nor is it undocumented, it's just often misunderstood. Which brings you back to: if it doesn't have to be a rule, make it a trigger. Forget you ever heard about rules. Pretend they don't exist... Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
[GENERAL] Dumping part (not all) of the data in a database...methods?
I'm needing to do a partial dump on a database. All of the entries in the db can be marked as one of two groups, and I've been asked to create a dump of just the second group. It is possible to do a select statement based dump and just grab the one set of records in the output? - Food fight? Enjoy some healthy debate in the Yahoo! Answers Food & Drink Q&A.
Re: [GENERAL] Transactions through JDBC
You should use conn.setAutoCommit(false); conn.execute(...) conn.execute(...) conn.execute(...) conn.commit(); Thanks! jan Then, conn.setAutoCommit(false); has to be regarded as a begin statement? I had already put the autocommit flag to false soon after the creation of the connection, since I saw an improvement in the performances without that flag and moreover I wanted to make transactions on my own. I will change as you told me! Thanks! ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- -- Jan de Visser [EMAIL PROTECTED] Baruk Khazad! Khazad ai-menu! -- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [pgsql-www] [GENERAL] programmatic way to fetch latest release for a given major.minor version
On Wed, Apr 11, 2007 at 06:49:18PM +0200, Listmail wrote: > > > >If someone wants the schema change, react *now*. Later on we can only > >append to it, and not change it :) > > Since I like to complain... > > > > Suppose you someday add another dot, or a "b" for beta, wouldn't it > be better to have > > 823 IIRC, but not entirely sure, the order of items in XML is not guaranteed. So you'd need something like 82 etc etc I'm not sure, but I have some kind of memory of that ;-) As for beta, we're only going to be listing production versions in this one. It's there to list the latest available version in each released series. And if we add another dot, we can just add a v4="7" attribute. Adding is not a problem, only modifying. //Magnus ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Dumping part (not all) of the data in a database...methods?
On 4/11/07, Andrew Edson <[EMAIL PROTECTED]> wrote: I'm needing to do a partial dump on a database. All of the entries in the db can be marked as one of two groups, and I've been asked to create a dump of just the second group. It is possible to do a select statement based dump and just grab the one set of records in the output? you are aware you can dump a table at a time, right? pg_dump -t foo dumps table foo. A partial dumping scheme would probably involve using pg_dump with various flag in combination with a script that makes a list of things to dump. merlin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] 8.2.3 AutoVacuum not running
It says it's on and I have also turned on all stats collecting. -Original Message- From: Alvaro Herrera [mailto:[EMAIL PROTECTED] Sent: Monday, April 09, 2007 3:06 PM To: Schwenker, Stephen Cc: Tom Lane; pgsql-general@postgresql.org Subject: Re: [GENERAL] 8.2.3 AutoVacuum not running Schwenker, Stephen wrote: > Hey, > > I've also notice one difference between my 8.1 instance and my 8.2 > instance. I run a ps and on the 8.1 instance there is a 'stats buffer > process' and in the 8.2 instance there is no 'stats buffer instance' > > Does that give you anymore reasons as to why the autovacuum is not working? No -- the stats buffer process was removed in 8.2 on purpose. If you do a "show autovacuum", does it show as on? Maybe it was disabled due to misconfiguration. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Acces via applets
On Apr 11, 3:45 pm, [EMAIL PROTECTED] ("Marc") wrote:
> The bottom line question is can an applet served to a client machine other
> than the one the postrgres db resides on read that db?
Certainly. But the applet needs to be signed
by the distributor, and trusted by the end-user.
> Why does an applet served to the same box as the db work, but when served to
> another box not work?
One reason is this..
If unsigned applets could pull resources off
other sites, a lot of developers would be tempted
to use them so they could 'hot link' to resources
on *other* sites, while pretending to deliver the
content from their *own* site.
If the user is prompted to 'trust this code',
it makes it more obvious to the end user that
something beyond a standard applet is happening.
As an aside, this is more of a Java problem,
than one relating to SQL. I do not generally
read this group, so if you wish to pursue the
matter, I suggest taking it up on the
comp.lang.java.help usenet newsgroup.
HTH
Andrew T.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[GENERAL] Evaluate only one CASE WHEN in a select
Hi everybody, I'm implementing something like this: SELECT CASE WHEN add_numbers(t1.main_number,t2.main_number)>100 THEN t1.description1 ELSE t2.description1 END AS number_description1, CASE WHEN add_numbers(t1.main_number,t2.main_number)>100 THEN t1.description2 ELSE t2.description2 END AS number_description2 FROM table1 t1, table2 t2; Is there a way to evaluate the 'CASE WHEN' only once? Thanks Daniel ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Acces via applets
On Apr 11, 3:45 pm, [EMAIL PROTECTED] ("Marc") wrote:
..
> The applet has been self signed. ..
I did miss that bit on my initial reading,
but like I said earlier - best to continue
it in a discussion on a Java group.
Andrew T.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Dumping part (not all) of the data in a database...methods?
I am aware of this, yes, but the data in question is all (both sets) contained on a single table. That's why I was looking for a way to do a 'dump where (select foo where bar = 'criteria')' structure. Merlin Moncure <[EMAIL PROTECTED]> wrote: On 4/11/07, Andrew Edson wrote: > I'm needing to do a partial dump on a database. All of the entries in the > db can be marked as one of two groups, and I've been asked to create a dump > of just the second group. It is possible to do a select statement based > dump and just grab the one set of records in the output? you are aware you can dump a table at a time, right? pg_dump -t foo dumps table foo. A partial dumping scheme would probably involve using pg_dump with various flag in combination with a script that makes a list of things to dump. merlin - Looking for earth-friendly autos? Browse Top Cars by "Green Rating" at Yahoo! Autos' Green Center.
Re: [GENERAL] Dumping part (not all) of the data in a database...methods?
Andrew Edson wrote: I am aware of this, yes, but the data in question is all (both sets) contained on a single table. That's why I was looking for a way to do a 'dump where (select foo where bar = 'criteria')' structure. What if you do that select into a new table, then pg_dump just that table? - John Burger MITRE ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] SQL - finding next date
Hi all, This is probably a very simple one, but I just can't see the answer and it's driving me nuts. I have a table holding details of academic terms, and I need an SQL query such that for any given term I want to find the next term by starting date (or just NULL if there isn't one). Here's the table - CREATE TABLE terms ( term_id serial NOT NULL, term_name character varying(40) NOT NULL, term_starts date NOT NULL, term_ends date NOT NULL, . ) - so, supposing I have the following data - term_id | term_name | term_starts | ... -+-+-+-- 1 | Spring 2007 | 2007-01-10 | ... 2 | Autumn 2007 | 2007-09-01 | ... 6 | Spring 2008 | 2008-01-06 | ... - then for term '1' I'd like to return '2', for term '2' I'd like to return '6', and so on. The closest I've got is getting ALL terms that start after a given one, but I run into trouble after thatany help will be appreciated! Thanks in advance, Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Dumping part (not all) of the data in a database...methods?
Andrew Edson <[EMAIL PROTECTED]> writes: > I am aware of this, yes, but the data in question is all (both sets) > contained on a single table. That's why I was looking for a way to do a > 'dump where (select foo where bar = 'criteria')' structure. pg_dump is not in the business of editorializing on your data. However, as of 8.2 there is COPY (SELECT ...) TO ... which might serve your purpose. If you're on an older release I think it's temporary table time. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Select taking excessively long; Request help streamlining.
Andrew Edson <[EMAIL PROTECTED]> writes: > A copy of the statement and explain results on it appear below. Would > someone please assist me in figuring out how to more appropriately streamline > this statement? The lack of any applicable index on ttrans seems to be the biggest problem. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Tomcat question/problem
Check the URLs below. It might be what you are looking for: http://www.postgresql.org/communityfiles/27.pdf http://tomcat.apache.org/tomcat-5.5-doc/printer/jndi-datasource-examples-howto.html Dhaval On 4/9/07, Marc <[EMAIL PROTECTED]> wrote: Hi there. I've written an applet that connects to a PostgreSQL database which works fine in development. I'm now trying to deploy it and having some trouble. I'm running on a windows XP Pro box with an Apache Tomcat/5.5.23 web server and am using PostgreSQL 8.2. There's a white paper listed at this link http://www.postgresql.org/docs/techdocs.71 that sounds like what I need but nothing appears. The displayed web page is blank. So I would really appreciate some help with as much detail as possible. I figured I'm not the only working with these pieces and hopefully someone with more experience can/will provide some guidance. Thanks in advance, you time and effort is REALLY appreciated! Marc -- Dhaval Shah ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] SQL - finding next date
On 4/11/07, Raymond O'Donnell <[EMAIL PROTECTED]> wrote: Hi all, This is probably a very simple one, but I just can't see the answer and it's driving me nuts. I have a table holding details of academic terms, and I need an SQL query such that for any given term I want to find the next term by starting date (or just NULL if there isn't one). Here's the table - CREATE TABLE terms ( term_id serial NOT NULL, term_name character varying(40) NOT NULL, term_starts date NOT NULL, term_ends date NOT NULL, . ) - so, supposing I have the following data - term_id | term_name | term_starts | ... -+-+-+-- 1 | Spring 2007 | 2007-01-10 | ... 2 | Autumn 2007 | 2007-09-01 | ... 6 | Spring 2008 | 2008-01-06 | ... - then for term '1' I'd like to return '2', for term '2' I'd like to return '6', and so on. The closest I've got is getting ALL terms that start after a given one, but I run into trouble after thatany help will be appreciated! Thanks in advance, Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- SELECT main.term_name, main.term_starts mts, next.term_name, next.term_starts nts FROM terms main LEFT JOIN terms NEXT ON main.term_starts < NEXT.term_starts WHERE NOT EXISTS (SELECT 1 FROM terms t WHERE t.term_starts > main.term_starts AND t.term_starts < NEXT.term_starts) It's kind of a sneak attack way of getting at the min(term_starts) without using an aggregate. Jeff
[GENERAL] pg_standby: Unremovable Trigger File
I've been testing pg_standby as a helper application for a warm standby setup. So far, so good. When the environment is controlled and everything happens as expected, I'm able to operate a basic primary/standby setup. (This is all using 8.2.3 on Solaris x86, btw.) One thing I noticed in early testing, though, was the scenario where the trigger file can't be removed by pg_standby. I touched a trigger file as root, which made it unremovable by postgres. So this tripped the relevant error condition in pg_standby. I had a little difficulty understanding in what state this left the recovery process, and I'm not helping myself much by reading the code. Doesn't the non-zero exit from CheckForExternalTrigger mean that pg_standby will be signaling to the standby server a file-not- found scenario? -- Thomas F. O'Connell optimizing modern web applications : for search engines, for usability, and for performance : http://o.ptimized.com/ 615-260-0005
Re: [GENERAL] What about SkyTools?
On Wednesday 11 April 2007 12:08, Dmitry Koterov wrote:
> Hello.
>
> Have anybody used SkyTools in production environment?
> What's the impression? In practice - is it now more preferrable than Slony
> or not yet?
Well, skype using them in production... I think the general consensus of the
postgresql community is that slony is still the preferred choice, but on
number of deployments and general community knowledge, assuming you need
master/slave style replication. Everything else is still considered fairly
green technology, though that's no reason not to test it in your environment.
IMHO YMMV
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[GENERAL] is there a way to determine the attributes of anyelement
I have written a simple procedure that accepts anyarray, and concatinates the elements separated by a space and returns the result to anyelement. I know when I call this function I will always be passing varchars. If the length of the resultant concatination is longer than the maximum length of the return varchar I want to truncate at the max. Since I don't know the max length of the return varchar I now get an error in this situation: ERROR: value too long for type character varying(10) is there a way to determine the maximum length of the varchar of anyelement inside the function? Here is my simple function: create or replace function concat_arr( p_array anyarray ) returns anyelement as \$\$ DECLARE out_char ALIAS FOR \$0; BEGIN out_char := '' ; FOR i IN 1..array_upper(p_array, 1) LOOP if i <> 1 then out_char := out_char || ' ' || p_array[i] ; else out_char := out_char || p_array[i] ; end if ; END LOOP; return (out_char) ; END; \$\$ LANGUAGE plpgsql ; -- View this message in context: http://www.nabble.com/is-there-a-way-to-determine-the-attributes-of-anyelement-tf3562903.html#a9951488 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] SQL - finding next date
Is something like this too simple? select term_id from terms where term_id > 2 order by term_starts limit 1; or select term_id from terms where term_starts > '2007-09-01' order by term_starts limit 1; depending on whether you have the term_id or the term_starts date. Susan Cassidy Raymond O'Donnell <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 04/11/2007 12:41 PM Please respond to [EMAIL PROTECTED] To 'PostgreSQL' cc Subject [GENERAL] SQL - finding next date Hi all, This is probably a very simple one, but I just can't see the answer and it's driving me nuts. I have a table holding details of academic terms, and I need an SQL query such that for any given term I want to find the next term by starting date (or just NULL if there isn't one). Here's the table - CREATE TABLE terms ( term_id serial NOT NULL, term_name character varying(40) NOT NULL, term_starts date NOT NULL, term_ends date NOT NULL, . ) - so, supposing I have the following data - term_id | term_name | term_starts | ... -+-+-+-- 1 | Spring 2007 | 2007-01-10 | ... 2 | Autumn 2007 | 2007-09-01 | ... 6 | Spring 2008 | 2008-01-06 | ... - then for term '1' I'd like to return '2', for term '2' I'd like to return '6', and so on. The closest I've got is getting ALL terms that start after a given one, but I run into trouble after thatany help will be appreciated! Thanks in advance, Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Simply protected storage solutions ensure that your information is automatically safe, readily available and always there, visit us at http://www.overlandstorage.com --
[GENERAL] INSERT..RETURNING on partitioned table
Hello, I'm attempting to convert a big table into smaller tables; I currently do a lot of INSERT .. RETURNING calls on the big table, which works perfectly. To convert the table into smaller tables, I have set up a test case of 3 tables, based on a 'CHECK ( hashtext(field) % 2 ) = -1' (or 0 or 1). Now, even this works perfectly - data is inserted into the correct table according to this value, and 'SET constraint_exclusion TO on' even makes the SELECT () calls work properly. However, I'm not stuck with my INSERT .. RETURNING: basically, I have three conditional rules at the moment, and need to make these rules return the ID of the inserted row. But postgres tells me this: 'ERROR: RETURNING lists are not supported in conditional rules' So my question is, is there any way that postgres supports INSERT .. RETURNING with partitioned tables, where the subtable to insert to is not know at application level ? I know I could write a stored procedure for this, which SELECT ()s the id from a subtable after it has been INSERTed, but this will put more stress on the database server, and it sounds silly that INSERT .. RETURNING would not be supported in my use case. Any ideas/suggestions ? Thanks in advance! Regards, Leon Mergen ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] hashtext () and collisions
Hello,
Okay, I have some troubles trying to determine how to most efficiently
store a database which will contain a couple of huge tables (think
5bil+ rows). These tables each have a bigint id and a character
varying value. Now, I'm currently partitioning these tables based on
the hashtext (value) % 1000, to determine which subtable a certain
value should be stored in.
However, I often also need to find a value for an id; instead of using
the sequential numbering that a BIGSERIAL would provide, I am
thinking: wouldn't it make some kind of sense if I used the value of
hashtext('value') to determine the id ? Then, if I need to determine
the value that belongs to a certain id, I can just % 1000 the value
and know which subtable the value is stored in, reducing the amount of
tables to search with a factor 500.
Now, my question is: how big is the chance that a collision happens
between hashes ? I noticed that the function only returns a 32 bit
number, so I figure it must be at least once in the 4 billion values.
If this approach is not recommended (using hashes as keys), any other
suggestions on how to make the subtable name derivable from an
identification number ?
--
Leon Mergen
http://www.solatis.com
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] 8.2.3 AutoVacuum not running
Hi folks I Agree with Stephen ,before update to 8.2.3 version i can see "vacuum database xx " tailing the file log, off course log statements i'ts in 'all' . After update don't see anymore, plus pgAdmin reclaim to me for vacuum databases. best regards MDC --- "Schwenker, Stephen" <[EMAIL PROTECTED]> escribió: > It says it's on and I have also turned on all stats > collecting. > > > -Original Message- > From: Alvaro Herrera > [mailto:[EMAIL PROTECTED] > Sent: Monday, April 09, 2007 3:06 PM > To: Schwenker, Stephen > Cc: Tom Lane; pgsql-general@postgresql.org > Subject: Re: [GENERAL] 8.2.3 AutoVacuum not running > > Schwenker, Stephen wrote: > > Hey, > > > > I've also notice one difference between my 8.1 > instance and my 8.2 > > instance. I run a ps and on the 8.1 instance > there is a 'stats buffer > > > process' and in the 8.2 instance there is no > 'stats buffer instance' > > > > Does that give you anymore reasons as to why the > autovacuum is not > working? > > No -- the stats buffer process was removed in 8.2 on > purpose. > > If you do a "show autovacuum", does it show as on? > Maybe it was > disabled due to misconfiguration. > > -- > Alvaro Herrera > http://www.CommandPrompt.com/ > PostgreSQL Replication, Consulting, Custom > Development, 24x7 support > > ---(end of > broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq > __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Evaluate only one CASE WHEN in a select
dcrespo wrote: Hi everybody, I'm implementing something like this: SELECT CASE WHEN add_numbers(t1.main_number,t2.main_number)>100 THEN t1.description1 ELSE t2.description1 END AS number_description1, CASE WHEN add_numbers(t1.main_number,t2.main_number)>100 THEN t1.description2 ELSE t2.description2 END AS number_description2 FROM table1 t1, table2 t2; Is there a way to evaluate the 'CASE WHEN' only once? Sure, see the implementation of CASE here: http://www.postgresql.org/docs/8.2/static/functions-conditional.html Not sure what you are looking for, though, since your condition in both CASEs above is exactly the same. -- Guy Rouillier ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] hashtext () and collisions
Hello,
Okay, I have some troubles trying to determine how to most efficiently
store a database which will contain a couple of huge tables (think
5bil+ rows). These tables each have a bigint id and a character
varying value. Now, I'm currently partitioning these tables based on
the hashtext (value) % 1000, to determine which subtable a certain
value should be stored in.
However, I often also need to find a value for an id; instead of using
the sequential numbering that a BIGSERIAL would provide, I am
thinking: wouldn't it make some kind of sense if I used the value of
hashtext('value') to determine the id ? Then, if I need to determine
the value that belongs to a certain id, I can just % 1000 the value
and know which subtable the value is stored in, reducing the amount of
tables to search with a factor 500.
Now, my question is: how big is the chance that a collision happens
between hashes ? I noticed that the function only returns a 32 bit
number, so I figure it must be at least once in the 4 billion values.
If this approach is not recommended (using hashes as keys), any other
suggestions on how to make the subtable name derivable from an
identification number ?
--
Leon Mergen
http://www.solatis.com
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] hashtext () and collisions
On 2007-04-11, "Leon Mergen" <[EMAIL PROTECTED]> wrote: > Now, my question is: how big is the chance that a collision happens > between hashes ? I noticed that the function only returns a 32 bit > number, so I figure it must be at least once in the 4 billion values. Assuming it's a uniform random hash, 32 bits long, then if you have 65536 values, you have a ~40% chance of at least one collision. Any defects in the hash function only increase that probability. This is a result of what's known as the "birthday paradox" (so-called because in a group of 23 people, there is a better than even chance that two of them share a birthday). The number of rows needed to have an approximately even chance of at least one collision grows as the _square root_ of the number of hash buckets; or to put it another way, you always need _more than twice as many bits_ in your hash value than you think you do. (e.g. using md5(), which is a 128-bit hash) -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] hashtext & collisions
Hello,
Okay, I have some troubles trying to determine how to most efficiently
store a database which will contain a couple of huge tables (think
5bil+ rows). These tables each have a bigint id and a character
varying value. Now, I'm currently partitioning these tables based on
the hashtext (value) % 1000, to determine which subtable a certain
value should be stored in.
However, I often also need to find a value for an id; instead of using
the sequential numbering that a BIGSERIAL would provide, I am
thinking: wouldn't it make some kind of sense if I used the value of
hashtext('value') to determine the id ? Then, if I need to determine
the value that belongs to a certain id, I can just % 1000 the value
and know which subtable the value is stored in, reducing the amount of
tables to search with a factor 500.
Now, my question is: how big is the chance that a collision happens
between hashes ? I noticed that the function only returns a 32 bit
number, so I figure it must be at least once in the 4 billion values.
If this approach is not recommended (using hashes as keys), any other
suggestions on how to make the subtable name derivable from an
identification number ?
--
Leon Mergen
http://www.solatis.com
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] SQL - finding next date
You'll need to do something like this, called a correlated subquery: Select t1.term_id, t1.term_name, t1.term_starts, t2.term_id as next_term From term t1, term t2 where t2.term_starts = (select min(t3.term_starts) from term t3 where t3.term_starts > t1.term_starts) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Raymond O'Donnell Sent: Wednesday, April 11, 2007 3:40 PM To: 'PostgreSQL' Subject: [GENERAL] SQL - finding next date Hi all, This is probably a very simple one, but I just can't see the answer and it's driving me nuts. I have a table holding details of academic terms, and I need an SQL query such that for any given term I want to find the next term by starting date (or just NULL if there isn't one). Here's the table - CREATE TABLE terms ( term_id serial NOT NULL, term_name character varying(40) NOT NULL, term_starts date NOT NULL, term_ends date NOT NULL, . ) - so, supposing I have the following data - term_id | term_name | term_starts | ... -+-+-+-- 1 | Spring 2007 | 2007-01-10 | ... 2 | Autumn 2007 | 2007-09-01 | ... 6 | Spring 2008 | 2008-01-06 | ... - then for term '1' I'd like to return '2', for term '2' I'd like to return '6', and so on. The closest I've got is getting ALL terms that start after a given one, but I run into trouble after thatany help will be appreciated! Thanks in advance, Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Transactions through JDBC
On Wednesday 11 April 2007 12:49:49 Albert wrote:
> Hi all!
>
> Actually I have a client-server application with one server and many
> clients each one of which opens a different connection to the postgres
> database. In order to avoid those known problems with the execution of
> the different clients' operations at database, I implemented
> everything in this way. Each operation is made so:
>
> conn.executeUpdate("BEGIN");
> conn.execute(...)
> conn.execute(...)
> conn.execute(...)
> conn.executeUpdate("COMMIT");
>
> May it be considered right, or am I making something wrong? I use JDBC
> driver for postgres 8.1
You should use
conn.setAutoCommit(false);
conn.execute(...)
conn.execute(...)
conn.execute(...)
conn.commit();
>
> Thanks!
jan
>
>
> ---(end of broadcast)---
> TIP 1: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to [EMAIL PROTECTED] so that your
>message can get through to the mailing list cleanly
--
--
Jan de Visser [EMAIL PROTECTED]
Baruk Khazad! Khazad ai-menu!
--
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[GENERAL] table partitioning and plpgsql functions in 8.2.3
Overview: plpgsql functions seem to ignore partitioning, even with constraint_exclusion on. Description: Version is 8.2.3 on RHEL 4, constraint_exlusion is on. I have an events table (fw_events) partitioned by an int and a date (fw_id, fw_date for discussion) following the recommendations outlined in 5.9 of the manual. FWIW, each partition holds around 1M rows. There are presently about 250 partitions (2 ids, around 4+ months of dates). explain select count(*) from fw_events where fw_id = 1 and fw_date = '2007-04-08' shows that the single partition table is examined, and results are snappy when executed. I created a function to do the same count, and it took orders of magnitude longer. I then created a plpgsql function to return the explain plan instead, which seemed to indicate the plpgsql function scans all the partitions. Shouldn't the plpgsql function honor the partitioning and only examine the single partition in the above example? Thanks in advance, Paul ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Dumping part (not all) of the data in a database...methods?
On Wednesday 11. April 2007 19:50, Andrew Edson wrote: >I'm needing to do a partial dump on a database. All of the entries in > the db can be marked as one of two groups, and I've been asked to > create a dump of just the second group. It is possible to do a > select statement based dump and just grab the one set of records in > the output? I had a similar problem with my genealogy database, of which I'm making regular exports to the Web. In order to not publish data on living people, I've got a boolean flag is_public. For me, the easiest way to export a "washed" subset of the data, was writing a custom Python script. It also enabled me to transform the data in other ways, as the script is reading from special views and makes a "flattened" image of the database, more suitable for Web -- or at least for the Swedish dolphin stuff which I'm still using on my Website :-) As I'm no Python guru, the code is rather simplistic. But it does its job well enough for me. If you want a copy of the script, just drop me a mail. -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE My Jazz Jukebox: http://www.last.fm/user/leifbk/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Kill session in PostgreSQL
You can use kill PID - Original Message - From: Ashish Karalkar To: pggeneral Sent: Tuesday, April 10, 2007 8:01 AM Subject: [GENERAL] Kill session in PostgreSQL Hello all, is there any command just like ORACLE Uses kill session to kill a particular session . tried withh linux kill -9 PID but it also kills all other sessions or am I just giving wrong signal to command kill? Thanks in advance With Regards ashish -- Check out what you're missing if you're not on Yahoo! Messenger -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.446 / Virus Database: 269.0.0/754 - Release Date: 09/04/2007 22:59
Re: [GENERAL] Transactions through JDBC
On Wednesday 11 April 2007 14:01:55 Alberto Molteni wrote: > >You should use > > > > conn.setAutoCommit(false); > > conn.execute(...) > > conn.execute(...) > > conn.execute(...) > > conn.commit(); > > > > > > Thanks! > > jan > > Then, conn.setAutoCommit(false); has to be regarded as a begin statement? I think BEGIN is implicitly send when you execute the first statement in a new transaction. You shouldn't worry about it. > I had already put the autocommit flag to false soon after the creation of > the connection, since I saw an improvement in the performances without that > flag and moreover I wanted to make transactions on my own. Makes sense. > > I will change as you told me! > > Thanks! jan -- -- Jan de Visser [EMAIL PROTECTED] Baruk Khazad! Khazad ai-menu! -- ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] SQL - finding next date
Raymond O'Donnell wrote: > This is probably a very simple one, but I just can't see the answer and > it's driving me nuts. I have a table holding details of academic terms, > and I need an SQL query such that for any given term I want to find the > next term by starting date (or just NULL if there isn't one). Here's one approach given your table def. select t.*, ( select term_id from terms where term_starts > t.term_ends order by term_starts asc limit 1 ) as next_term_id from terms t order by t.term_starts asc; -Jon -- Senior Systems Developer Media Matters for America http://mediamatters.org/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Acces via applets
Marc wrote: what version of PostgreSQL? what version of the jdbc driver? The bottom line question is can an applet served to a client machine other than the one the postrgres db resides on read that db? I can't see why not. An applet I’ve written and tested on the same box as my database runs fine. When I try running the applet from another computer on the network I get an error trying to access the database. what error? Turn on appropriate logging for PostgreSQL and then what does the PostgreSQL log show? Even when I added the ip of that computer to the pg_hba.conf it still didn’t work. did you restart? Why does an applet served to the same box as the db work, but when served to another box not work? need more/better info. Is there a configuration setting I’m missing or is this simply not possible? configuration => perhaps not possible => no, it's possible The applet has been self signed.using the java’s keytool and jarsigner programs. I’ve had this same setup working with SQL Server for years now. Self signed applet reads db on separate box. What is going on with PostgreSQL? What is going on with PostgreSQL? => likely nothing. Sorry for rambling, I’m just so frustrated right now. Please provide more detailed information... Thanks in advance. Marc ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Unable to get a database connection while deleting rows
I have a java application receiving data from a thousand devices on periodic basis. When receiving data the application gets a database connection, inserts a row and closes the connection again. If this process takes more than 15 seconds, the device assumes the connection dead and makes a new one. Sometimes a device is taken out of production and the data from it is deleted. Deleting ex. 3 rows of a total of around 30 mill. takes about 45 seconds. I expect this to be a row locking process and there is also no problem with inserting rows while this process is running. The problem is that getting the database connection can take from 1 to the full 45 seconds. There is nothing in the log telling me what's going on except from a lot of "unexpected EOF on client connection" Can anyone bring a light on what resource that can be the bottleneck ? The system is "PostgreSQL 8.1.8 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.0.gcc-opt (GCC) 4.0.3 (Ubuntu 4.0.3-1ubuntu5)" Thanks in advance, Poul ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] ERROR: XLogFlush request 0/240169BC is not satisfied
java.sql.SQLException: ERROR: XLogFlush: request 0/240169BC is not satisfied --- flushed only to 0/23FFC01C This error I was in the logs of a java process that was failing to get some data. But using psql I was able to connect and query all the data. Thus this may indicate some connection / statement isolated problem but I am not sure about that. I would like to understand; When/why do we get this error? And can we avoid or detect this? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] hashtext () and collisions
Hello,
Okay, I have some troubles trying to determine how to most efficiently
store a database which will contain a couple of huge tables (think
5bil+ rows). These tables each have a bigint id and a character
varying value. Now, I'm currently partitioning these tables based on
the hashtext (value) % 1000, to determine which subtable a certain
value should be stored in.
However, I often also need to find a value for an id; instead of using
the sequential numbering that a BIGSERIAL would provide, I am
thinking: wouldn't it make some kind of sense if I used the value of
hashtext('value') to determine the id ? Then, if I need to determine
the value that belongs to a certain id, I can just % 1000 the value
and know which subtable the value is stored in, reducing the amount of
tables to search with a factor 500.
Now, my question is: how big is the chance that a collision happens
between hashes ? I noticed that the function only returns a 32 bit
number, so I figure it must be at least once in the 4 billion values.
If this approach is not recommended (using hashes as keys), any other
suggestions on how to make the subtable name derivable from an
identification number ?
--
Leon Mergen
http://www.solatis.com
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] newid() in postgres
Here's a PL/pgsql implementation I wrote.I'm sure critics will be able to improve upon it: CREATE or REPLACE FUNCTION "common"."newid"() RETURNS "pg_catalog"."varchar" AS $BODY$ DECLARE v_seed_value varchar(32); BEGIN select md5( inet_client_addr()::varchar || timeofday() || inet_server_addr()::varchar || to_hex(inet_client_port()) ) into v_seed_value; return (substr(v_seed_value,1,8) || '-' || substr(v_seed_value,9,4) || '-' || substr(v_seed_value,13,4) || '-' || substr(v_seed_value,17,4) || '-' || substr(v_seed_value,21,12)); END; $BODY$ LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER; ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] digest data types?
Does anyone have postgresql types for message digests, especially md5 and sha1? Obviously I could store these as text (as I currently do), but I'm particularly interested in custom types that store digests as binary blobs and provide conversion to/from text. Am I correct in assuming that the space saved by storing digests as binary (1/2 size of hex) will substantially impact index ins/upd/del performance or when the digest itself is a large fraction of the rest of the row size? Thanks, Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0
[GENERAL] seeking: advice on reordering table
I've drastically altered a few tables in a major schema change. because of this, some columns that i'd really like to be 'leftmost' are rightmost. can anyone suggest a good way to reorder the table ? everything that i can think of involves creating a new table which means I'd have to redo all the constraints . // Jonathan Vanasco | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | SyndiClick.com | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | FindMeOn.com - The cure for Multiple Web Personality Disorder | Web Identity Management and 3D Social Networking | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | RoadSound.com - Tools For Bands, Stuff For Fans | Collaborative Online Management And Syndication Tools | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Unable to get a database connection while deleting rows
Hello Poul, I can't advise specifically regarding your problem (I'm sure some one will chime in soon). I will offer some general advice regarding java and jdbc connections. You are probably already aware of this Typically connections between java and database take a relatively long time to establish (not 45 seconds though!!) so it is common practice to use a connection pool that actually maintains a set of connections ready-to-go. This avoids the need for connection setup time and can dramatically increase through put in many situations. There are many free production quality libraries that may be used, even in commercial application. We use C3PO, but I know there are several others such as Apache's DBCP. I suspect that this is unlikely to address your situation, but for future googlers it may be handy -Damian On 4/9/07, Poul Møller Hansen <[EMAIL PROTECTED]> wrote: I have a java application receiving data from a thousand devices on periodic basis. When receiving data the application gets a database connection, inserts a row and closes the connection again. If this process takes more than 15 seconds, the device assumes the connection dead and makes a new one. Sometimes a device is taken out of production and the data from it is deleted. Deleting ex. 3 rows of a total of around 30 mill. takes about 45 seconds. I expect this to be a row locking process and there is also no problem with inserting rows while this process is running. The problem is that getting the database connection can take from 1 to the full 45 seconds. There is nothing in the log telling me what's going on except from a lot of "unexpected EOF on client connection" Can anyone bring a light on what resource that can be the bottleneck ? The system is "PostgreSQL 8.1.8 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.0.gcc-opt (GCC) 4.0.3 (Ubuntu 4.0.3-1ubuntu5)" Thanks in advance, Poul ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] INSERT..RETURNING on a partitioned table
Hello, I'm attempting to convert a big table into smaller tables; I currently do a lot of INSERT .. RETURNING calls on the big table, which works perfectly. To convert the table into smaller tables, I have set up a test case of 3 tables, based on a 'CHECK ( hashtext(field) % 2 ) = -1' (or 0 or 1). Now, even this works perfectly - data is inserted into the correct table according to this value, and 'SET constraint_exclusion TO on' even makes the SELECT () calls work properly. However, I'm not stuck with my INSERT .. RETURNING: basically, I have three conditional rules at the moment, and need to make these rules return the ID of the inserted row. But postgres tells me this: 'ERROR: RETURNING lists are not supported in conditional rules' So my question is, is there any way that postgres supports INSERT .. RETURNING with partitioned tables, where the subtable to insert to is not know at application level ? I know I could write a stored procedure for this, which SELECT ()s the id from a subtable after it has been INSERTed, but this will put more stress on the database server, and it sounds silly that INSERT .. RETURNING would not be supported in my use case. Any ideas/suggestions ? Thanks in advance! Regards, Leon Mergen ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Acces via applets
On Wednesday 11 April 2007 Marc's cat, walking on the keyboard, wrote: > The bottom line question is can an applet served to a client machine other > than the one the postrgres db resides on read that db? > > An applet I've written and tested on the same box as my database runs fine. Marc's, due to security restriction an applet cannot connect to a server different from the one it has been dowloaded. Exceptions are signed applets. You can provide more functionalities with a n-tier server, for example a servlet running on your web machine that connects to the database server (another machine) and provides data to the applet (that can connect only to the web server). I read below that you have signed the applet and that you've done the same thing with sql server.have you tried such applet on your sql server configuration (if possible) to ensure that it works and is a postgresql only related problem and not a java one? Could you be more specific on the problem you have? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] INSERT..RETURNING on a partitioned table
Hello, I'm attempting to convert a big table into smaller tables; I currently do a lot of INSERT .. RETURNING calls on the big table, which works perfectly. To convert the table into smaller tables, I have set up a test case of 3 tables, based on a 'CHECK ( hashtext(field) % 2 ) = -1' (or 0 or 1). Now, even this works perfectly - data is inserted into the correct table according to this value, and 'SET constraint_exclusion TO on' even makes the SELECT () calls work properly. However, I'm not stuck with my INSERT .. RETURNING: basically, I have three conditional rules at the moment, and need to make these rules return the ID of the inserted row. But postgres tells me this: 'ERROR: RETURNING lists are not supported in conditional rules' So my question is, is there any way that postgres supports INSERT .. RETURNING with partitioned tables, where the subtable to insert to is not know at application level ? I know I could write a stored procedure for this, which SELECT ()s the id from a subtable after it has been INSERTed, but this will put more stress on the database server, and it sounds silly that INSERT .. RETURNING would not be supported in my use case. Any ideas/suggestions ? Thanks in advance! Regards, Leon Mergen ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Providing user based previleges to Postgres DB
Hi All, Currently in one of the projects we want to restrict the unauthorized users to the Postgres DB. Here we are using Postgres version 8.2.0 Can anybody tell me how can I provide the user based previleges to the Postgres DB so that, we can restrict the unauthorized users as well as porivde the access control to the users based on the set previleges by the administrator. Thanks and Regards, Ramac The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com
