Re: [SQL] Select table from other database
On Mon, Oct 21, 2002 at 11:54:53AM +0100, Tiago Moitinho wrote: > Hi, > > I would like to know if this is possible: > > I have a database A, with a table X. > Is it possible, being logged in another database (B, for instance), to make > queries using table X from database A? > (something like "SELECT * FROM A.X ...") See the dblink module in the PostgreSQL contrib tree (available via ftp. packages available). -Roberto -- +|Roberto Mello -http://www.brasileiro.net/ |--+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + Sigh. I like to think it's just the Linux people who want to be on the "leading edge" so bad they walk right off the precipice. -- Craig E. Groeschel ---(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
Fwd: Re: [SQL] Can I search for an array in csf?
Hi, Richard, Thanks for your response and see below. 10/21/2002 3:13:57 AM, Richard Huxton <[EMAIL PROTECTED]> wrote: >On Friday 18 Oct 2002 1:58 pm, Vernon Wu wrote: >> One field of a table stores an array of characters in a string fromat as >> "a,b,c,d". Is anyway to apply a select statement without using stored >> procedure? >> >> Thanks for your input. > >Not really, and I can't think any way of accessing an index on this either. >Are you sure you wanted the characters stored this way? Even if the overhead >of a separate table isn't woth it, you might want to look into arrays and the >intarray stuff in contrib/ > The reason I use this format for an array is that the array is dynamic. I have quite few cases of this type of situation. The maximize length in some cases is known, is unknown in others. I have learnt the comment separated format is one way to solve the problem. Someone also suggested to store the array as an object. I am not sure whether it works or not. The application is written in Java, by the way. I have taken a look at intarray by searching on the postgres.org web site as well as in google. (I use cypwin and unable to find the contrib directory). My impression is it isn't a standard SQL data type. And its element is integer only. It is my first time doing DB table design. Any helps will be gracfully appreciated. Thanks, Vernon >-- > Richard Huxton > >---(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 > End of forwarded message ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Delete/Replace Bug in Functions?
Folks, I seem to be experiencing a rather elusive bug in 7.2.3, and I wanted to talk it over before submitting it to pgsql-bugs, to see if anyone else has experienced anything similar. 1. I have a function (fn_save_order) which is a long and complex data verification and dependant data updating procedure. 2. One of the pieces of dependant data that fn_save_order creates is 3 records in order_spec. If fn_save_order is saving an existing order record, first it deletes the 3 exisiting records in order_spec and replaces them with new ones. Here's the relevant code: IF v_usq > 0 THEN -- Note: v_usq is > 0 for all existing records DELETE FROM order_spec WHERE order_usq = v_usq; END IF; WHILE numbers[spec_loop] is not null LOOP v_offset := spec_loop - 1; SELECT job_type INTO current_type FROM job_types WHERE in_use = TRUE ORDER BY job_type LIMIT 1 OFFSET v_offset; INSERT INTO order_spec ( job_type, order_usq, no_needed, start_time, end_time, pay_rate, bill_rate ) VALUES ( current_type, new_usq, numbers[spec_loop], sttimes[spec_loop], endtimes[spec_loop], pays[spec_loop], bills[spec_loop] ); spec_loop := spec_loop + 1; END LOOP; 3. This all works fine, *unless* the 3 records being deleted are identical to the 3 records replacing them aside from the table's SERIAL index. Then, for some reason, Postgres does not seem to delete the exisiting records but rather keeps both the old and the new records, doubling the number of order_spec records to 6. I tailed the log, and the delete statement is getting passed to the parser ... it just seems to be ignored. 4. Establishing a unique index on several of the data columns of order_spec seems to have "cured" the problem, but I'm worried that it's an indication of a more serious MVCC issue with deleting and inserting records within a function. Thoughts? Regrettably, the function and table spec is extremely dense; I'm trying to set up a test case. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Delete/Replace Bug in Functions?
Josh Berkus <[EMAIL PROTECTED]> writes: > ones. Here's the relevant code: > IF v_usq > 0 THEN -- Note: v_usq is > 0 for all existing records > DELETE FROM order_spec WHERE order_usq = v_usq; > END IF; > ... > INSERT INTO order_spec ( job_type, order_usq, no_needed, start_time, > end_time, pay_rate, bill_rate ) > VALUES ( current_type, new_usq, numbers[spec_loop], >sttimes[spec_loop], > endtimes[spec_loop], > pays[spec_loop], bills[spec_loop] ); Um, might v_usq be different from new_usq? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Delete/Replace Bug in Functions?
Tom, > > INSERT INTO order_spec ( job_type, order_usq, no_needed, start_time, > > end_time, pay_rate, bill_rate ) > > VALUES ( current_type, new_usq, numbers[spec_loop], >sttimes[spec_loop], > > endtimes[spec_loop], > > pays[spec_loop], bills[spec_loop] ); > > Um, might v_usq be different from new_usq? No, actually; there's a declaration earlier that assigns them the same value for existing records. Plus "new_usq" is the link to the orders record, so if they were different I wouldn't ever see the extra records. I just tried to set up a test case for this issue, based on much simpler schema. Unfortunately, the bug is not reproduceable in this simple case. Any suggestions on how to reproduce it without dumping you an enitre copy of my *confidential* database design? -- -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Row Locking?
I have a long running process that performs outside actions on the content of a table. The actions could all be done in parallel (if I had n processors), but I need to ensure that the process is attempted exactly one time per applicable row. My current design for one thread is the following (simplified to a test case) create table foo (pending boolean, done boolean, idx serial); select * from foo where pending='f' and done='f' limit 1; update foo set pending='t' where idx=[[returned idx]]; commit; do stuff outside database update foo set pending='f', done='t' where idx=[[returned idx]]; commit; Extending this to multiple threads if proving problematic. No locking leads to a race condition between the select and update. If I change the select to a SELECT ... FOR UPDATE it apparently locks the table against all other select for updates, then when the update is committed, the second thread returns nothing, even when there are other rows in the table that could be returned. Is there a single row locking against select? Or can I effeciently do the equivalent of update set pending, then select the row that I just updated to get the contents? (perhaps without doing a table scan to find the oid of the row that I just updated). I can't afford to lock the entire table. eric ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: Fwd: Re: [SQL] Can I search for an array in csf?
Vernon, > >> One field of a table stores an array of characters in a string fromat as > >> "a,b,c,d". Is anyway to apply a select statement without using stored > >> procedure? > The reason I use this format for an array is that the array is dynamic. I have quite few cases of this type of situation. The > maximize length in some cases is known, is unknown in others. I have learnt the comment separated format is one way > to solve the problem. Someone also suggested to store the array as an object. I am not sure whether it works or not. > The application is written in Java, by the way. You should store this data in a sub-table linked through a foriegn key. Period. Messing with arrays will only lead you to heartache ... Try the book "Database Design For Mere Mortals" for a primer on SQL DB design. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] isAutoIncrement and Postgres
John, > > > Do any existing drivers / database version combinations support > the > > > isAutoIncrement method? > > > > What programming language are you referring to? VB? Delphi? > > > > Java / JDBC In that case, post your question on [EMAIL PROTECTED] You are unlikely to get an answer on this list. -Josh Berkus ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
