Betr: Re: [GENERAL] Question insert data
That's what I tought the problem was, but I created a table afterwards without inheritence. Could it have something to do with the max size of the schema or oid's? -- Oorspronkelijk bericht -- Date: Sat, 19 Mar 2005 14:55:50 -0800 (PST) From: Stephan Szabo [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Question insert data On Sat, 19 Mar 2005 [EMAIL PROTECTED] wrote: Hi There, I've a problem with inserting data and I can't figure out what the problem is: what did I do: 1. I've a master table containing about 4 records. A count(*) provides me the exact number. 2. I've create a table based on from the master. I copied a fraction from the master into the new table using a where clause (insert into ... select * from ... where a = b . The number of records copied is about 2553. Define based on. Do you mean CREATE TABLE AS or perhaps that the new table inherits from the old one? If the latter, you will see the rows in the parent as well as the child if you do a select from the parent. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster _ 12move ADSL vanaf 18,95 euro! GRATIS modem, GEEN aansluitkosten en GEEN datalimiet! Ga nu naar http://adsl.12move.nl ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: Betr: Re: [GENERAL] Question insert data
On Sun, 20 Mar 2005 [EMAIL PROTECTED] wrote: That's what I tought the problem was, but I created a table afterwards without inheritence. Could it have something to do with the max size of the schema or oid's? I can't think of a reason it would, so can you send a self-contained full example? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] pg_dump fails with socket_not_open
Hi there Tom, thanks for your reply. pg_dump: socket not open pg_dump: SQL command to dump the contents of table activity_log failed: PQendcopy() failed. pg_dump: Error message from server: socket not open pg_dump: The command was: COPY public.activity_log (bunch of columns TO stdout Is this repeatable? What shows up in the postmaster's log when it happens? What platform is this on, and what version of Postgres? This is postgresql-7.4.6-1.FC2.2 running on RedHat Fedora Core 2. The logs don't reveal anything, and it happens consistently! It is a little disconcerting..by the sounds of things this is a fairly rare thing to see? Sarah. ---(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] plpython function problem workaround
Sorry it took me so long to respond. I've been out for a couple days. While certain things may be permissible in a language, I think it is also important to look at the context at which the language is applied and make a determination if it will practically turn up in relevant code. If the answer is no, then I think it would be acceptable to add a note that the postgresql python implementation does not accept implicit newlines and any newlines must be explicit (the text \r\n) That being said, the only place I can see wanting to use a newline is for a situation where you would want to either do a select statement with a multiline where such as results = plpy.execute(...where textfield= 'multi line string'...) or if you returned the results and wanted to do the same type of thing. if results[0][textfield]==multi line string: ... However, this example assumes that the client writing the functions and the client inserting the data are always using the same OS, because if a linux client inserted the data and a windows client had written the function, it wouldn't receive any results, because postgresql sees the newline as valid characters. hat being the case I would say that while it is entirely possible to put multiline quoted text in python code, it would be inappropriate in a postgresql environment. Sim Tom Lane [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Sim Zacks [EMAIL PROTECTED] writes: I've been looking at the possibility of having a planned CR in the source code and I don't see a case where it would happen. Does python actually disallow newlines in string literals? That is x = 'foo bar' Whether you think this is good style is not the question --- is it allowed by the language? regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(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] Installing PostgreSQL in Debian
Michael Ben-Nes wrote: I recomend you to compile PG from source so you can use the new 8.0.1 PostgreSQL 8.0.1 is available in the Debian experimental suite, package name postgresql-8.0. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] question about 8.1 and stored procedures
Tony Caduto wrote: Hi, I read in a article/interview on http://madpenguin.org/cms/html/62/3677.html that work was being done on improving/adding support for sql standard compliant stored procs/functions Does anyone know exactly what that means? Does it mean that Postgres will have stored procs that can have input and output params? Yes. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Encoding-related errors when moving from 7.3 to 8.0.1
Hi Alvaro, thanks for your reply! Alvaro Herrera wrote: psql:db_backup.sql:1548: ERROR: invalid byte sequence for encoding UNICODE: 0xe12020 CONTEXT: COPY country, line 5, column namespanish: Canad? Hmm. The sequence looks like latin1 interpreted as utf8. This seems the inverse of the problem reported (and solved) here http://archives.postgresql.org/pgsql-es-ayuda/2005-03/msg00491.php Maybe you should try sticking a SET client_encoding TO latin1; at the beggining of the dump file. One thing worries me, though. With all of the previous versions of postgresql (I think when we started to use it in our system, it was version 7.1), I have never worried about any encoding issues. Our users are mostly Spanish-speaking, and they register to our system via web-based interfaces; virtually 100% of them use Windows (and perhaps most of them Windows in Spanish, with a Spanish keyboard). So, our system (CGI's written in C++ running on a Linux server) simply takes whatever the user gives (properly validated and escaped) and throws it in the database. We've never encountered any problem (well, or perhaps it's the opposite? Perhaps we've always been living with the problem without realizing it?) I worry now that if I needed to put a set client_encoding statement to make the insert or COPY statements work, does that mean that I should modify each and every program that I have that interacts with the database, and add a set client encoding statement before whatever other statement(s) we execute? Or is this client_encoding setting something that gets attached to the database (or the tables) itself? Where can I find more documentation on these issues? I'd like to get a deeper understanding, to avoid any future problems. Why are you using CHAR(n) fields anyway? It should probably be better if you used VARCHAR(n) ... Una de esas cosas que pasan hasta en las mejores familias ;-) (I was also surprised when noticing the bunch of spaces at the end -- I would have thought that we were using varchars in fields like that one) Thanks again! Cheers, Carlos -- PS: I have a strict white-list anti-spam filter in place, which is why a direct e-mail would be rejected -- let me know if you want to write directly through e-mail, so that I can add you to the white list file. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Betr: Re: Betr: Re: [GENERAL] Question insert data
Hi Stephan, I figured out what happened: The master table contained duplicates, but the insert statement seems to be very smart by selecting just the unique ones. Peter -- Oorspronkelijk bericht -- Date: Sun, 20 Mar 2005 01:46:19 -0800 (PST) From: Stephan Szabo [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Subject: Re: Betr: Re: [GENERAL] Question insert data On Sun, 20 Mar 2005 [EMAIL PROTECTED] wrote: That's what I tought the problem was, but I created a table afterwards without inheritence. Could it have something to do with the max size of the schema or oid's? I can't think of a reason it would, so can you send a self-contained full example? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster _ 12move ADSL vanaf 18,95 euro! GRATIS modem, GEEN aansluitkosten en GEEN datalimiet! Ga nu naar http://adsl.12move.nl ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] question about 8.1 and stored procedures
On Sun, 20 Mar 2005, Bruce Momjian wrote: Tony Caduto wrote: Hi, I read in a article/interview on http://madpenguin.org/cms/html/62/3677.html that work was being done on improving/adding support for sql standard compliant stored procs/functions Does anyone know exactly what that means? Does it mean that Postgres will have stored procs that can have input and output params? Yes. I think that keyword here is 'sql standard compliant', not stored procedures itself, because we have them for a long time and with support of dozen languages. Or I miss something ? Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] question about 8.1 and stored procedures
Oleg Bartunov wrote: On Sun, 20 Mar 2005, Bruce Momjian wrote: Tony Caduto wrote: Hi, I read in a article/interview on http://madpenguin.org/cms/html/62/3677.html that work was being done on improving/adding support for sql standard compliant stored procs/functions Does anyone know exactly what that means? Does it mean that Postgres will have stored procs that can have input and output params? Yes. I think that keyword here is 'sql standard compliant', not stored procedures itself, because we have them for a long time and with support of dozen languages. Or I miss something ? What I read from this is, when will PostgreSQL have stored procedures like Oracle. Thus the IN/OUT parameter statement. My understanding is that 8.1 will have a much more mature implementation of stored procedures versus UDFs (Which we have had forever). Sincerely, Joshua D. Drake Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL begin:vcard fn:Joshua Drake n:Drake;Joshua org:Command Prompt, Inc. adr:;;PO Box 215 ;Cascade Locks;OR;97014;US email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 x-mozilla-html:FALSE url:http://www.commandprompt.com version:2.1 end:vcard ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] pg_dump fails with socket_not_open
Sarah Ewen [EMAIL PROTECTED] writes: Is this repeatable? What shows up in the postmaster's log when it happens? What platform is this on, and what version of Postgres? This is postgresql-7.4.6-1.FC2.2 running on RedHat Fedora Core 2. The logs don't reveal anything, and it happens consistently! The 7.4 RPMs' startup script sends the postmaster stderr to /dev/null :-(. To find out what the server sees as the problem, you need to either hack the startup script to send stderr someplace more useful, or adjust the configuration to send the postmaster's log messages to syslog. I would recommend doing one or the other since it's quite likely that the server-side view of the problem is different from what the client sees. It is a little disconcerting..by the sounds of things this is a fairly rare thing to see? Yes. I would like to get to the bottom of it. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] question about 8.1 and stored procedures
On Sun, 20 Mar 2005, Joshua D. Drake wrote: Oleg Bartunov wrote: On Sun, 20 Mar 2005, Bruce Momjian wrote: Tony Caduto wrote: Hi, I read in a article/interview on http://madpenguin.org/cms/html/62/3677.html that work was being done on improving/adding support for sql standard compliant stored procs/functions Does anyone know exactly what that means? Does it mean that Postgres will have stored procs that can have input and output params? Yes. I think that keyword here is 'sql standard compliant', not stored procedures itself, because we have them for a long time and with support of dozen languages. Or I miss something ? What I read from this is, when will PostgreSQL have stored procedures like Oracle. Thus the IN/OUT parameter statement. I mean original Josh's interview An example of what people are working on right now is SQL standard compliant stored procedures. We have procedures now, but they're not compliant with the standard syntax. Nothing about Oracle unless Oracle has standard compliant stored procedures. My understanding is that 8.1 will have a much more mature implementation of stored procedures versus UDFs (Which we have had forever). What's the difference between UDF and stored procedure ? Sincerely, Joshua D. Drake Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] 26h Query.
hi, I'm having a bit of trouble with my SQL query. It takes about 26h to run on a 3Ghz PC. I'd really like to speed this up. I put this query in a loop to iterate over 20 tables (each table including summary has 400k records), each time the table name changes. I this case it's m_alal. Each table has an identical primary key ECP_TAG but the RATING column is different. What I doing is: Classifying RATING has High, Moderate, Low No Data or Nill. Then counting how many tables classify the PK in one of these categories. This result is kept in my summary table. I end up with a summary table like: ECP_CODE | HIGH | MODERATE | LOW ... M3456 | 3 | 4 | 7 .. Because I have 20 tables, if you were to add up along each row it would sum up to 20. Anyone have some suggestion on speeding this up. update public.summary set MAX_VAL = CASE WHEN public.m_alal.RATING public.summary.MAX_VAL THEN public.m_alal.RATING ELSE public.summary.MAX_VAL END, MIN_VAL = CASE WHEN public.m_alal.RATING public.summary.MIN_VAL THEN public.m_alal.RATING ELSE public.summary.MIN_VAL END, NO_DATA = CASE WHEN public.m_alal.RATING = 0 THEN public.summary.NO_DATA + 1 END, NILL = CASE WHEN public.m_alal.RATING = -1 THEN public.summary.NILL + 1 ELSE public.summary.NILL END, HIGH = CASE WHEN public.m_alal.RATING 75 THEN public.summary.HIGH + 1 ELSE public.summary.HIGH END, MODERATE = CASE WHEN public.m_alal.RATING 30 THEN public.summary.MODERATE + 1 ELSE public.summary.MODERATE END, LOW = CASE WHEN public.m_alal.RATING 25 THEN public.summary.LOW + 1 ELSE public.summary.LOW END FROM public.m_alal WHERE public.summary.ECP_TAG = public.m_alal.ECP_TAG AND public.m_alal.RATING IS NOT NULL; ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] question about 8.1 and stored procedures
My understanding is that 8.1 will have a much more mature implementation of stored procedures versus UDFs (Which we have had forever). What's the difference between UDF and stored procedure ? Here are a couple of GGIYF references: http://builder.com.com/5100-6388-1045463.html http://blogs.pingpoet.com/vbguru/archive/2004/04/29/535.aspx They are similar but they offer different functionality. At least in the sense of the other databases. Sincerely, Joshua D. Drake Sincerely, Joshua D. Drake Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL begin:vcard fn:Joshua Drake n:Drake;Joshua org:Command Prompt, Inc. adr:;;PO Box 215 ;Cascade Locks;OR;97014;US email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 x-mozilla-html:FALSE url:http://www.commandprompt.com version:2.1 end:vcard ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] question about 8.1 and stored procedures
On Sun, 20 Mar 2005, Joshua D. Drake wrote: My understanding is that 8.1 will have a much more mature implementation of stored procedures versus UDFs (Which we have had forever). What's the difference between UDF and stored procedure ? Here are a couple of GGIYF references: http://builder.com.com/5100-6388-1045463.html http://blogs.pingpoet.com/vbguru/archive/2004/04/29/535.aspx They are similar but they offer different functionality. At least in the sense of the other databases. Hmm, the only real difference I see - is that SP are precompiled. I think we should clearly outline what is SP and what is UDF and do we are working on SP or just improving and extending our functions. Sincerely, Joshua D. Drake Sincerely, Joshua D. Drake Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] question about 8.1 and stored procedures
Oleg Bartunov oleg@sai.msu.su writes: Hmm, the only real difference I see - is that SP are precompiled. I think we should clearly outline what is SP and what is UDF and do we are working on SP or just improving and extending our functions. AFAIR, the only person who's actually stated any intention to work on this for 8.1 was me, and what I intend to do is just enough to support OUT and INOUT parameters in plpgsql. This is mostly because Red Hat wants to run some test suites that require those capabilities. There was some discussion of other ideas in the pgsql-hackers list a few weeks ago (see the archives) but I don't think anyone is stepping up to the plate to do them. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] question about 8.1 and stored procedures
Oleg Bartunov wrote: On Sun, 20 Mar 2005, Joshua D. Drake wrote: My understanding is that 8.1 will have a much more mature implementation of stored procedures versus UDFs (Which we have had forever). What's the difference between UDF and stored procedure ? Here are a couple of GGIYF references: http://builder.com.com/5100-6388-1045463.html http://blogs.pingpoet.com/vbguru/archive/2004/04/29/535.aspx They are similar but they offer different functionality. At least in the sense of the other databases. Hmm, the only real difference I see - is that SP are precompiled. I think we should clearly outline what is SP and what is UDF and do we are working on SP or just improving and extending our functions. I always thought that the big difference was that a SP can start and end top level transactions whereas UDFs must execute within the scope of a transaction started by the caller. The above article doesn't mention this at all. Regards, Thomas Hallgren ---(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] Tracking row updates
[I've tried to send this message to pgsql-general several times now,
but even though I'm subscribed to it I never saw the message show up
in the mailing list, so I'm trying to send it from a different account
now. If you get several copies of this message, I apologize.]
I'm working on an application where we have a central database server
and a bunch of clients that are disconnected most of the time, need to
maintain a local copy of the central database. The client databases are
based on One$DB since it has to be lightweight. The client does not
access the server database directly, but through a server application.
Periodically (about once a week) the clients will connect to the central
database and retrieve updates made to the central database so that their
local database will be up-to-date, as well as send local updates back to
the server. A lot of these clients will be connecting through a dial-up
connection, and some of the tables can get quite large, so just
retrieving all rows in a table is not an option when synchronizing.
This is how I currently have things designed on the server side:
Each table has a revision column, which is set to a sequence value every
time a row is inserted or updated (deletes are translated into updates
that hide the row by setting an enabled column to false), as you can
see in the following statements:
CREATE TABLE codes (
id SERIAL PRIMARY KEY,
created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
updated TIMESTAMP,
revision SERIAL,
enabled BOOLEAN DEFAULT TRUE NOT NULL,
name VARCHAR(8) NOT NULL,
description VARCHAR(50)
) WITHOUT OIDS;
CREATE INDEX codes_revision_idx ON codes (revision);
CREATE FUNCTION row_update_codes() RETURNS trigger AS '
BEGIN
NEW.revision = nextval(''codes_revision_seq'');
NEW.updated = current_timestamp;
RETURN NEW;
END;
' LANGUAGE plpgsql;
CREATE TRIGGER update_row BEFORE INSERT OR UPDATE ON codes
FOR EACH ROW EXECUTE PROCEDURE row_update_codes();
CREATE RULE delete_row AS
ON DELETE TO codes DO INSTEAD
UPDATE codes SET enabled = FALSE WHERE id = OLD.id;
To fetch all updates since the last synchronization, the client would
calculated a value for $lastrevision by running this query on its local
database:
SELECT max(revision) AS lastrevision FROM codes;
It would then fetch all updated rows by running this query against the
server:
SELECT * FROM codes WHERE revision $lastrevision;
This seems to work, except there exists a race condition. Consider the
following series of events (in chronological order):
1. Initially, in the codes table there's a row with id=1, revision=1,
and a row with id=2, revision=2
2. Client A begins a transaction, and updates the row with id=1,
resulting in revision=3 for that row
3. Client B begins a transaction, and updates the row with id=2,
resulting in revision=4 for that row
4. Client B commits the transaction
5. Client C (which has $lastrevision=2 in its local database)
synchronizes with the database by doing SELECT * FROM codes WHERE
revision 2; and retrieves client B's update to the row with
id=2, revision=4 (it doesn't yet see the update from client A)
6. Client A commits the transaction
7. Some time later, Client C synchronizes with the database again.
$lastrevision for its database is now 4, so doing SELECT * FROM
codes WHERE revision 4; does not retrieve any rows. So client C
never sees client A's update to the row with id=1
Essentially, the race condition occurs when the order of clients
committing transactions (i.e. the updates becoming visible to other
clients) differs from the order of clients generating sequence values.
Do you guys have any suggestions on how to avoid this race condition, or
maybe a more elegant way to synchronize the clients with the server?
I was thinking about doing the following to solve this problem. I'd
create a new table, e.g. codes_active_txns, with a sequence_value
column. Before the server application starts any transaction involving
an insert/update/delete to codes, it would retrieve
nextval('codes_revision_seq') as $seq_val, insert it into
codes_active_txns, commit, begin another transaction, and do its
inserts/updates, setting revision = $seq_val for each row that it
touches. Once it's done with the table updates, it would commit the
transaction, and delete the row containing $seq_val from
codes_active_txns, and commit that.
The server would calculate $lastrevision with:
SELECT max(revision) FROM codes WHERE revision (SELECT
COALESCE(min(sequence_value), 2147483647) FROM
codes_active_txns);
and send this to the client. $lastrevision would contain the highest
revision for which there are no active (non-committed) transactions with
a revision lower than $lastrevision. The client would save this value,
and pass it back to the server during the next
Re: [GENERAL] 26h Query.
On Sun, Mar 20, 2005 at 10:10:14AM -0800, Jason Leach wrote: hi, I'm having a bit of trouble with my SQL query. It takes about 26h to run on a 3Ghz PC. I'd really like to speed this up. I put this query in a loop to iterate over 20 tables (each table including summary has 400k records), each time the table name changes. I this case it's m_alal. Each table has an identical primary key ECP_TAG but the RATING column is different. What I doing is: You're going to need to post the EXPLAIN ANALYZE output if you expect any meaningful response... -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpd5G1ChQeQJ.pgp Description: PGP signature
[GENERAL] pg_dump fails with socket_not_open
Hi there folks, I've just had pg_dump fail on me for the first time ever, and I'm not sure why. It generates 24MB of dump before bombing out with: pg_dump: socket not open pg_dump: SQL command to dump the contents of table activity_log failed: PQendcopy() failed. pg_dump: Error message from server: socket not open pg_dump: The command was: COPY public.activity_log (bunch of columns TO stdout Are there any common reasons for seeing this? I'm fairly sure that the machine is ok hardware wise - any pointers appreciated. Thanks, Sarah. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Question insert data
Dnia Sat, Mar 19, 2005 at 06:36:27PM +0100, [EMAIL PROTECTED] napisal(a/o): 1. I've a master table containing about 4 records. A count(*) provides me the exact number. 2. I've create a table based on from the master. I copied a fraction from the master into the new table using a where clause (insert into ... select * from ... where a = b . The number of records copied is about 2553. Issueing the same select statement on the master table gives me a list of 5106 which is twice the number of copied records. select only (read queries-table-expressions.html from PostgreSQL documentation, then read rest of documentation). Did someone have this problem before? I'm using 8.0 Windows XP en the pgAdmin III , r1.2.0 I don't have any XP problem ; (sorry, lame joke) I even tried the same on the command line! me too : -- ojciec ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] 3rd RFD: comp.databases.postgresql (was: comp.databases.postgresql.*)
tm [EMAIL PROTECTED] wrote in news:[EMAIL PROTECTED]: Woodchuck Bill [EMAIL PROTECTED] wrote: The proponent certainly left a bad taste in my mouth after his little ... Too much information. LOL. Get your mind out of the gutter. ;-) -- Bill ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] CFV: comp.databases.postgresql
FIRST CALL FOR VOTES (of 2) unmoderated group comp.databases.postgresql Newsgroups line: comp.databases.postgresql PGSQL Relational Database Management System. Votes must be received by 23:59:59 UTC, 9 Apr 2005. This vote is being conducted by a neutral third party. Questions about the proposed group should be directed to the proponent. Proponent: Mike Cox [EMAIL PROTECTED] Votetaker: Bill Aten [EMAIL PROTECTED] RATIONALE: comp.databases.postgresql To provide a Big-Eight usenet group for users of the PostgreSQL Relational Database Management System. Currently there are mailing lists gated to the public pgsql.* hierarchy. Issues with mailing list gateways, and the much larger distribution of the Big-Eight hierarchy make it extremely desirable to have a PostgreSQL group in the Big Eight. CHARTER: comp.databases.postgresql The newsgroup comp.databases.postgresql is a usenet discussion group for the PostgreSQL Relational Database Management System. Topics of discussion may include: GENERAL * General discussions of PostgreSQL. * Assisting beginners in using the PostgreSQL Relational Database Management system. * Help answer basic questions. ADMINISTRATION * Discussions pertaining to the administration, compilation and installation of PostgreSQL. * Announcements of new versions of PostgreSQL, PostgreSQL related software, and documentation. * Administration of PostgreSQL interfaces, including JDBC and ODBC. * Administration and installation of the Contrib packages. * Using PostgreSQL Point in Time Recovery. * Startup scripts, or scripts that automate PostgreSQL related tasks. DEVELOPING WITH POSTGRESQL * Programming using PostgreSQL. * Developing with PostgreSQL interfaces, including JDBC and ODBC. SCALABILITY * PostgreSQL performance, benchmarking and related topics. * How to use Replication in PostgreSQL. * Hardware, Software configurations and recommendations for optimal PostgreSQL operation. Examples are x86-64, NUMA, etc. SECURITY * Announcements of patches that fix vulnerabilities. * PostgreSQL Best practices to design and keep PostgreSQL secure. * SSH, SSL, and the Crypto Contrib packages. SQL * Normalization and theory as it applies to PostgreSQL. * Transactions, Indices, Cursors, Triggers, and Nested Transactions. * PostgreSQL specific topics such as Objects. END CHARTER. HOW TO VOTE: In order to vote on this proposal you will first need to request a registered ballot. This is accomplished by sending an email to the address specified below from the email account that you intend to use when you submit the ballot for processing. The Subject: and body of the message does not matter. They can both be blank (preferred) if your software will allow that. PLEASE, do not send this entire message back to me as this mail is archived. Mail your ballot request to: [EMAIL PROTECTED] Just replying to this message should work, but check the To: line. When your email message is received, a reply message will be sent to you with further instructions regarding how to vote. You will also receive a copy of the CFV which will contain a ballot that is registered for use only for this CFV, and only when submitted from the exact same address that originally requested it. IMPORTANT VOTING PROCEDURE NOTES: Standard Guidelines for voting apply. Only one vote per person, no more than one vote per account. Votes must be mailed directly from the voter to the votetaker. Anonymous, forwarded, or proxy votes are not valid. Votes mailed by WWW/HTML/CGI forms are considered to be anonymous votes. Vote counting is automated. Failure to follow these directions may mean that your vote does not get counted. If you do not receive an acknowledgment of your vote within three days, contact the votetaker about the problem. It's your responsibility to make sure your vote is registered correctly. Duplicate votes are resolved in favor of the most recent valid vote. Names, addresses, and votes of all voters will be published in the final voting RESULT posting. DO NOT redistribute this CFV in any manner whatsoever. The purpose of a Usenet vote is to determine the genuine interest of persons who would read a proposed newsgroup. Soliciting votes from disinterested parties defeats this purpose. Only the votetaker, the news.announce.newgroups moderator, and the proponent (if specifically authorized by the votetaker) are permitted to distribute copies of this CFV. Distribution of pre-marked or otherwise modified copies of this CFV is generally considered voting fraud and should be reported immediately to the votetaker or the UVV [EMAIL PROTECTED]. In cases where voting fraud is determined to have occurred, it is standard operating procedure to delete ALL votes submitted by the violator. When in doubt, ask the votetaker. DISTRIBUTION: The only official sources for copies of this CFV are the locations listed below, the UVV web site at http://www.uvv.org/, and the
Re: [GENERAL] Statistics with PostgreSQL
Mean is just sum(col)/count(col) You can also just use avg(col). Either way, be careful because nulls may not be treated as you want for such calculations. The stats package R can access Postgres databases, and can be used for robust statistical analyses of the data. See: http://sourceforge.net/projects/rdbi/ Cheers, Brent Wood ---(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] Tsearch2 index silently fails on PG 7.3.2
The short question is why does this:
select to_tsvector('default', coalesce(name, '') ||' '||
coalesce(description, '') ||' '|| coalesce(keywords,'')) from link_items;
give different results than this:
update link_items set linksfti=to_tsvector('default', coalesce(name, '')
||' '|| coalesce(description, '') ||' '|| coalesce(keywords,''));
select linksfti from link_items;
Here are more details:
I am working with Tsearch2 on a server with version string:
PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC 2.96
I have a table with the following schema:
CREATE TABLE link_items
(
link_id int4,
name varchar(255),
url varchar(255),
description text,
spanish int4,
spanishurl varchar(255),
lastmod date,
visible int4,
state varchar(25),
promisepractice int4,
keywords text,
linksfti tsvector
)
WITH OIDS;
ALTER TABLE link_items OWNER TO gate;
I want linksfti to hold the search engine's indexing data (indexed on
'name', 'description', and 'keywords'), so I run the following command:
update link_items set linksfti=to_tsvector('default', coalesce(name, '')
||' '|| coalesce(description, '') ||' '|| coalesce(keywords,''));
The results are pretty empty. Most have empty strings for data, other
only index one or two items in the 3 input columns.
For example, after running, my table looks like:
name;description;keywords;linksfti
American Occupational Therapy Association (AOTA) ;Nationally recognized
professional association for over 60,000 occupational therapists and
occupational therapy assistants. ;Rehabilitation Professional
Associations and Councils;'60':1 '000':2
American Physical Therapy Association (APTA);Represents more than
70,000 physical therapists, physical therapist assistants, and students of
physical therapy. ;Rehabilitation Professional Associations and
Councils;'70':1 '000':2
U.S. Deaf Ski Snowboard Association;Winter sports for people who are
deaf relevant links.;Recreation Winter Sports;'u.s':1
Texas Adaptive Aquatics;Adaptive water skiing program for people with
physical and/or mental disabilities. ;Recreation Water
Sports;'and/or':1
World T.E.A.M. Sports;Inclusive sports activities.;Recreation Team
Sports;'t.e.a.m':1
Tennessee;Official State Web Site;Legal State Agencies;
Project Vote Smart;By entering zip code, users get list of all their
elected officials. Links to elected officials' and candidates' web sites,
etc. ;Government / Public Policy General;
TRIPOD Captioned Films;Captioned Films for people who are deaf or hard
of hearing.;Recreation Captioned Movies;
When don't do it as an UPDATE and just print the contents to the screen, I
get the full expected output:
select name, description, keywords, to_tsvector('default', coalesce(name,
'') ||' '|| coalesce(description, '') ||' '|| coalesce(keywords,'')) from
link_items;
United States of America Deaf Track and Field;Promotes training of
track athletes who are deaf and coaches who are deaf and hearing.
;Recreation Track;'of':3,11 'and':7,17,22 'are':15,20 'who':14,19
'deaf':5,16,21 'hear':23 'unit':1 'coach':18 'field':8 'state':2
'track':6,12,25 'train':10 'athlet':13 'promot':9 'america':4
'recreat':24
Adventure Pursuit, Inc.;Adventure Pursuit is a group of volunteers who
like spending time with all kinds of people and focus on adventure sports
like kayaking.;Recreation Water Sports;'a':7 'is':6 'of':9,18 'on':22
'all':16 'and':20 'inc':3 'who':11 'kind':17 'like':12,25 'time':14
'with':15 'focus':21 'group':8 'kayak':26 'peopl':19 'spend':13
'sport':24,29 'water':28 'volunt':10 'pursuit':2,5 'recreat':27
'adventur':1,4,23
Using pgAdminIII, I copied (default backup/restore) the database from our
production server and put in on my personal desktop (Windows 2000, PgSQL
8.0.0) and re-ran the update query and it gave proper results.
Is it a known issue with 7.3.2, and is there a workaround without
upgrading the server to 8.0.0? We will upgrade in a few months, but we
can't take the server offline now because we have too many websites that
depend on it.
--
Justin Kennedy
Systems Analyst I
---(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] no IF - am I missing something ?
Hello all. In the tail end of converting an app from MySQL to psql. I have this code : snip IF(days_complete = -120, job_price,0) AS Days_120, IF(days_complete BETWEEN -119 AND -90, job_price,0) AS Days_90, IF(days_complete BETWEEN -89 AND -60, job_price,0) AS Days_60, IF(days_complete BETWEEN -59 AND -30, job_price,0) AS Days_30, IF(days_complete BETWEEN -29 AND 0, job_price,0) AS current snip It builds an aged debt report, and there are similar versions that SUM(IF ..) to give me debt by customer etc. All the questions I've seen about IF end up with people saying use CASE and I'm sure it would work. But do you lot really use 5 lines for each IF ? Doesn't it seem kind of ugly ? Or do you all secretly write a quick IF function ? I'm interested, and I half suspect that I'm missing a more elegant solution. thanks Rich ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Install error at rpm package + Fedora Core3
Folks, I'm a kind new bye with linux related stuffs. I'm trying to install PG on a FC3 using "postgresql-8.0.1-2PGDG.i686.rpm", but after to issue rpm -ivh and the rpm file I've got a message telling it "error: Failed dependencies: libpq.so.3 is need". I take a look at the documentation and it seems the libpq is the PG API, so shouldn't be built at install time? How can I solve that or where should I look for in order to find out an answer? Thank you so much. Edgar Amorim Network Consultant __Converse com seus amigos em tempo real com o Yahoo! Messenger http://br.download.yahoo.com/messenger/
Re: [GENERAL] 3rd RFD: comp.databases.postgresql (was: comp.databases.postgresql.*)
Vern [EMAIL PROTECTED] wrote in news:[EMAIL PROTECTED]: Marc G. Fournier wrote in Msg [EMAIL PROTECTED]: it can't *hurt* to have the group ... I respectfully disagree with you, Marc. :) The PGSQL* hierarchy is now well distributed, and there is no need for a comp.* group. If anything, the ungated comp.* group will confuse newbies into thinking that that is the best forum for PostGreSQL advice ... instead of the PGSQL.* hierarchy. None of the developers and power users of these lists will be answering questions in the comp.* group, if created, so it would be better to not create the group at all. I still haven't decided which way to vote. I'm lingering in between NO and ABSTAIN. I was originally in favor of a single, non-gated Postgresql newsgroup in the comp* hierarchy. I'm no longer sure if it would be a good thing or not. The proponent certainly left a bad taste in my mouth after his little crossposting stunt - but I will still vote on the *proposal*, and not the *proponent*. -- Bill ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Need Help on ODBC Driver testing........
Hello I need Help on ODBC driver testing. We have written ODBC driver for our new SQL engine. I have to test ODBC driver in linux only. I need some web links where i can get some free codes in linux for ODBC testing. Can you refer me any sites or weblinks where i can get some ODBC driver test codes? Can i get it from Postgresql? I downloaded some source codes from pgsql, but it was regression,performance testing and all. But i couldnt get any ODBC driver test code. Can i get it from postgresql? Plz let me know. Its very urgent and your any types of sugession will be very much helpfull to me. Waiting for yur mail. Bhabani _ Expressions unlimited! http://server1.msn.co.in/sp04/messenger/ The all new MSN Messenger! ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Tracking row updates - race condition
I'm working on an application where we have a central database server
and a bunch of clients that are disconnected most of the time, need to
maintain a local copy of the central database. The client databases are
based on One$DB since it has to be lightweight. The client does not
access the server database directly, but through a server application.
Periodically (about once a week) the clients will connect to the central
database and retrieve updates made to the central database so that their
local database will be up-to-date, as well as send local updates back to
the server. A lot of these clients will be connecting through a dial-up
connection, and some of the tables can get quite large, so just
retrieving all rows in a table is not an option when synchronizing.
This is how I currently have things designed on the server side:
Each table has a revision column, which is set to a sequence value every
time a row is inserted or updated (deletes are translated into updates
that hide the row by setting an enabled column to false), as you can
see in the following statements:
CREATE TABLE codes (
id SERIAL PRIMARY KEY,
created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
updated TIMESTAMP,
revision SERIAL,
enabled BOOLEAN DEFAULT TRUE NOT NULL,
name VARCHAR(8) NOT NULL,
description VARCHAR(50)
) WITHOUT OIDS;
CREATE INDEX codes_revision_idx ON codes (revision);
CREATE FUNCTION row_update_codes() RETURNS trigger AS '
BEGIN
NEW.revision = nextval(''codes_revision_seq'');
NEW.updated = current_timestamp;
RETURN NEW;
END;
' LANGUAGE plpgsql;
CREATE TRIGGER update_row BEFORE INSERT OR UPDATE ON codes
FOR EACH ROW EXECUTE PROCEDURE row_update_codes();
CREATE RULE delete_row AS
ON DELETE TO codes DO INSTEAD
UPDATE codes SET enabled = FALSE WHERE id = OLD.id;
To fetch all updates since the last synchronization, the client would
calculated a value for $lastrevision by running this query on its local
database:
SELECT max(revision) AS lastrevision FROM codes;
It would then fetch all updated rows by running this query against the
server:
SELECT * FROM codes WHERE revision $lastrevision;
This seems to work, except there exists a race condition. Consider the
following series of events (in chronological order):
1. Initially, in the codes table there's a row with id=1, revision=1,
and a row with id=2, revision=2
2. Client A begins a transaction, and updates the row with id=1,
resulting in revision=3 for that row
3. Client B begins a transaction, and updates the row with id=2,
resulting in revision=4 for that row
4. Client B commits the transaction
5. Client C (which has $lastrevision=2 in its local database)
synchronizes with the database by doing SELECT * FROM codes WHERE
revision 2; and retrieves client B's update to the row with
id=2, revision=4 (it doesn't yet see the update from client A)
6. Client A commits the transaction
7. Some time later, Client C synchronizes with the database again.
$lastrevision for its database is now 4, so doing SELECT * FROM
codes WHERE revision 4; does not retrieve any rows. So client C
never sees client A's update to the row with id=1
Essentially, the race condition occurs when the order of clients
committing transactions (i.e. the updates becoming visible to other
clients) differs from the order of clients generating sequence values.
Do you guys have any suggestions on how to avoid this race condition, or
maybe a more elegant way to synchronize the clients with the server?
I was thinking about doing the following to solve this problem. I'd
create a new table, e.g. codes_active_txns, with a sequence_value
column. Before the server application starts any transaction involving
an insert/update/delete to codes, it would retrieve
nextval('codes_revision_seq') as $seq_val, insert it into
codes_active_txns, commit, begin another transaction, and do its
inserts/updates, setting revision = $seq_val for each row that it
touches. Once it's done with the table updates, it would commit the
transaction, and delete the row containing $seq_val from
codes_active_txns, and commit that.
The server would calculate $lastrevision with:
SELECT max(revision) FROM codes WHERE revision (SELECT
COALESCE(min(sequence_value), 2147483647) FROM codes_active_txns);
and send this to the client. $lastrevision would contain the highest
revision for which there are no active (non-committed) transactions with
a revision lower than $lastrevision. The client would save this value,
and pass it back to the server during the next synchronization session
so that the server can figure out what rows it needs to send to the
client with SELECT * FROM codes WHERE revision $lastrevision;.
Occasionally the same row might be sent to the client twice during two
synchronization sessions, but that's no big deal because that number
should be low and the client can
Re: [GENERAL] New user: Windows, Postgresql, Python
Michael Fuhr wrote: On Tue, Mar 15, 2005 at 10:46:09PM +, Paul Moore wrote: The long and short of it is that I believe you just use \n to delimit lines on Windows, just like anywhere else. Many thanks -- your test results contain the info we've been seeking. Thanks a lot Paul. Micheal, you were right. It seems python documentation is plain wrong, or I'm not able to read it at all: http://docs.python.org/ref/physical.html A physical line ends in whatever the current platform's convention is for terminating lines. On Unix, this is the ASCII LF (linefeed) character. On Windows, it is the ASCII sequence CR LF (return followed by linefeed). On Macintosh, it is the ASCII CR (return) character. This is the language _reference_ manual, btw. I'm very surprised to hear python on windows is so broken. Anyway, that makes life simpler for us. plpython programs are \n separated, no matter what platform the server runs on. Client applications just need to conply, which is what I suggested some time ago. I'm glad to hear there's nothing to change on the server side. .TM. -- / / / / / / Marco Colombo ___/ ___ / / Technical Manager / / / ESI s.r.l. _/ _/ _/ [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Windows and Pg8
Title: Message I use AS Tcl 8.4.9 on Windows and I would like to use Pg8 (since it is now native to Windows). What do I need on the Tcl side of things? Robert HicksNorthrop Grumman Mission Systems Defense Mission SystemsSystems Administrator (LIMS)304.264.7939 (Office) 304.264.2664 (Fax)[EMAIL PROTECTED] [EMAIL PROTECTED]"The contents of this message are mine personally and do not reflect any position of the Government, The Department of Homeland Security, or Coast Guard." "A little consideration, a little thought for others, makes all the difference."
[GENERAL] Copression
It's a possible to compress traffic between server and client while server returns query result? It's a very actually for dial-up users. What is solution? ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] java.lang.OutOfMemoryError
Hi, I'm need to be able to insert a byte[] of size upto 25MB. With Heap size upto 512m this is failing, with a java.lang.OutOfMemoryError Any help resolving this issue will be greatly appreciated. Thanks, Suma ---(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] Tsearch vector not stored by update/set
The short question is why does this:
select to_tsvector('default', coalesce(name, '') ||' '||
coalesce(description, '') ||' '|| coalesce(keywords,'')) from link_items;
give different results than this:
update link_items set linksfti=to_tsvector('default', coalesce(name, '')
||' '|| coalesce(description, '') ||' '|| coalesce(keywords,''));
select linksfti from link_items;
Here are more details:
I am working with Tsearch2 on a server with version string:
PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC 2.96
I have a table with the following schema:
CREATE TABLE link_items
(
link_id int4,
name varchar(255),
url varchar(255),
description text,
spanish int4,
spanishurl varchar(255),
lastmod date,
visible int4,
state varchar(25),
promisepractice int4,
keywords text,
linksfti tsvector
)
WITH OIDS;
ALTER TABLE link_items OWNER TO gate;
I want linksfti to hold the search engine's indexing data (indexed on
'name', 'description', and 'keywords'), so I run the following command:
update link_items set linksfti=to_tsvector('default', coalesce(name, '')
||' '|| coalesce(description, '') ||' '|| coalesce(keywords,''));
The results are pretty empty. Most have empty strings for data, other
only index one or two items in the 3 input columns.
For example, after running, my table looks like:
name;description;keywords;linksfti
American Occupational Therapy Association (AOTA) ;Nationally recognized
professional association for over 60,000 occupational therapists and
occupational therapy assistants. ;Rehabilitation Professional
Associations and Councils;'60':1 '000':2
American Physical Therapy Association (APTA);Represents more than
70,000 physical therapists, physical therapist assistants, and students of
physical therapy. ;Rehabilitation Professional Associations and
Councils;'70':1 '000':2
U.S. Deaf Ski Snowboard Association;Winter sports for people who are
deaf relevant links.;Recreation Winter Sports;'u.s':1
Texas Adaptive Aquatics;Adaptive water skiing program for people with
physical and/or mental disabilities. ;Recreation Water
Sports;'and/or':1
World T.E.A.M. Sports;Inclusive sports activities.;Recreation Team
Sports;'t.e.a.m':1
Tennessee;Official State Web Site;Legal State Agencies;
Project Vote Smart;By entering zip code, users get list of all their
elected officials. Links to elected officials' and candidates' web sites,
etc. ;Government / Public Policy General;
TRIPOD Captioned Films;Captioned Films for people who are deaf or hard
of hearing.;Recreation Captioned Movies;
When don't do it as an UPDATE and just print the contents to the screen, I
get the full expected output:
select name, description, keywords, to_tsvector('default', coalesce(name,
'') ||' '|| coalesce(description, '') ||' '|| coalesce(keywords,'')) from
link_items;
United States of America Deaf Track and Field;Promotes training of
track athletes who are deaf and coaches who are deaf and hearing.
;Recreation Track;'of':3,11 'and':7,17,22 'are':15,20 'who':14,19
'deaf':5,16,21 'hear':23 'unit':1 'coach':18 'field':8 'state':2
'track':6,12,25 'train':10 'athlet':13 'promot':9 'america':4
'recreat':24
Adventure Pursuit, Inc.;Adventure Pursuit is a group of volunteers who
like spending time with all kinds of people and focus on adventure sports
like kayaking.;Recreation Water Sports;'a':7 'is':6 'of':9,18 'on':22
'all':16 'and':20 'inc':3 'who':11 'kind':17 'like':12,25 'time':14
'with':15 'focus':21 'group':8 'kayak':26 'peopl':19 'spend':13
'sport':24,29 'water':28 'volunt':10 'pursuit':2,5 'recreat':27
'adventur':1,4,23
Using pgAdminIII, I copied (default backup/restore) the database from our
production server and put in on my personal desktop (Windows 2000, PgSQL
8.0.0) and re-ran the update query and it gave proper results.
Is it a known issue with 7.3.2, and is there a workaround without
upgrading the server to 8.0.0? We will upgrade in a few months, but we
can't take the server offline now because we have too many websites that
depend on it.
--
Justin Kennedy
HEADLINE: GAY GUY NOT AROUSED BY UGLY, BITCHY GIRLS
-ram
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Questions about the use of largeobject functions from inside SQL
Hi all. We have a setup with Zope and a remote Postgresql server. We're storing blobs in largeobject files. What we need to do is to be able to do the transfer of blobs between Zope and postgres. I thought it was possible to use lo_* functions, by creating a largeobject, and then sending the data using an lo_write, but I'm unable to figure how to do this in SQL. Any suggestions? Note: 1) NFS in order to share disk space between the two servers is not an option (in that case, I would be able to use lo_import/lo_export); 2) We're using ZPsycopgDA on the client side 3) I'm not on the list, so please CC to me directly Regards Marco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] prelimiary performance comparison pgsql vs mysql
Mark Rae wrote: I would say that doing the concurrency tests is probably the most important factor in comparing other databases against MySQL, as MySQL will almost always win in single-user tests. E.g. here are some performance figures from tests I have done in the past. This is with a 6GB databse on a 4CPU Itanium system running a mixture of read-only queries, but it is fairly typical of the behaviour I have seen. The Oracle figures also scaled in a similar way to postgres. Clients 1 2 3 4 6 812163264 128 --- mysql-4.1.11.00 1.41 1.34 1.16 0.93 1.03 1.01 1.00 0.94 0.86 0.80 pg-7.4.1 0.65 1.27 1.90 2.48 2.45 2.50 2.48 2.51 2.49 2.39 2.38 Would be interesting to know about the tuning of the MySQL, I guess that buffers for indexing and sort is well setup, but what about thread caching? Knowing that will once in a while you will have a connection burst you can tell mysql to cache thread so that it can save time next time it needs them. -- Robin Ericsson http://robin.vill.ha.kuddkrig.nu/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] prelimiary performance comparison pgsql vs mysql
In article [EMAIL PROTECTED], Rick Schumeyer [EMAIL PROTECTED] writes: These results are for a single process populating a table with 934k rows, and then performing some selects. I also compared the effect of creating indexes on some of the columns. I have not yet done any testing of transactions, multiple concurrent processes, etc. Bad. That's where things begin to get interesting. I did not make any changes to the default config settings. Bad. On modern hardware MySQL performs quite good with its default settings; PostgreSQL performs horribly without some tuning. I used pg 8.0.1 and mysql 5.0.2 alpha. Bad. As you noticed, MySQL 5.x is Alpha and not very stable. I'd suggest using MySQL 4.1.10 instead. I compiled pg from source, but I downloaded an binary for mysql. Good. Since MySQL is multithreaded, it's much harder to compile than PostgreSQL. The MySQL guys actually recommend using their binaries. select count(*) from data where fid=2 and rid=6; count = 100 select count(*) from data where x 5000 and x 5500; count = 35986 select count(*) from data where x 5000 and x 5020; count = 1525 Bad. These queries are exactly the sore point of PostgreSQL and MySQL/InnoDB, whereas MySQL/MyISAM really shines. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Using sequence name depending on other column
I have table containing different types of documents (type A, B and C). Each document type must have separate sequential ID starting at 1 ID of first inserted record of type A must be set to 1 ID of first inserted record of type B must be also set to 1 ID of second record of type A must be set to 2 etc. If you are happy with the fact that a sequence may leave a whole in the numbers. You are probably best to no set a default value for an integer, or big integer. Then run a before trigger for each row. That trigger will assign a value to the column based on the value given for the type. Russell, thank you. I'm a new to Postgres. Is there any sample how to write such trigger ? Before inserting each row it should set document id from sequence corresponding to insertable document type. Andrus. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] pg_restore: [custom archiver] could not uncompress data: incorrect data check
Hi, i dumped my database on server1 with pg_dump -Fc ..., copied the dump to server2, both same pgsql version 7.4.6 pg_restore says pg_restore: [custom archiver] could not uncompress data: incorrect data check But it seems that almost any data was restored. What does this error mean. I didn't found anything in the archives (searched google with 'pg_restore incorrect data check'). Just one unanswered message ( http://archives.postgresql.org/pgsql-general/2003-08/msg01035.php ) kind regards, janning -- PLANWERK 6 websolutions Herzogstraße 85, 40215 Düsseldorf Tel.: 0211-6015919 Fax: 0211-6015917 http://www.planwerk6.de/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Question about accessing current row data inside trigger
Hello, This issue is resolved. I was using the wrong struct. Peter Tom Lane wrote: peter Willis [EMAIL PROTECTED] writes: I have a trigger function written in C. ... Since the trigger is called after each row update the actual row data should be available in some way to the trigger. Sure: tg_trigtuple or tg_newtuple depending on which state you want. See http://www.postgresql.org/docs/8.0/static/trigger-interface.html regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] psql variables
I use a bash script (similar to following example) to update tables. psql -v passed_in_var=\'some_value\' -f script_name Is it possible to pass a value back from psql to the bash script? Thanks, Paul Cunningham ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Localization problems between Access and PostgresSQL 8
Hello, I've dumped the content of MS-Access 2002 SP3tables on a PC with Windows XP Pro in French localization.Then I COPY these files, on the same PC hosting an PostgreSQL 8.0.1 database.I've problems with the accents !? Why ? What kind of encoding must I use to create the PG database under Win XP pro French ?Thanks for all your commentsLuc
Re: [GENERAL] Question about accessing current row data inside trigger
Hello, I resolved this issue already. The trigger now works fine. I was looking at the wrong structure. Thanks, Peter Michael Fuhr wrote: On Tue, Mar 08, 2005 at 11:37:14AM -0800, peter Willis wrote: I have a trigger function written in C. The trigger function is called via: CREATE TRIGGER after_update AFTER UPDATE ON some_table FOR EACH ROW EXECUTE PROCEDURE my_trigger_function(); Since the trigger is called after each row update the actual row data should be available in some way to the trigger. What functionality (SPI ?) do I use to use the column values from the current row in the actual trigger? See Writing Trigger Functions in C and C-Language Functions in the documentation. Here are links to documentation for the latest version of PostgreSQL: http://www.postgresql.org/docs/8.0/interactive/trigger-interface.html http://www.postgresql.org/docs/8.0/interactive/xfunc-c.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] Copression
Stanislaw Tristan wrote: It's a possible to compress traffic between server and client while server returns query result? It's a very actually for dial-up users. What is solution? No, unless SSL compresses automatically. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Copression
Bruce Momjian wrote: Stanislaw Tristan wrote: It's a possible to compress traffic between server and client while server returns query result? There are a couple of solutions. 1. Mammoth PostgreSQL supports this for libpq, and jdbc based clients. 2. You can use a web services model that uses something like mod_deflate 3. You can use redirection with SSH Sincerely, Joshua D. Drake It's a very actually for dial-up users. What is solution? No, unless SSL compresses automatically. -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL begin:vcard fn:Joshua Drake n:Drake;Joshua org:Command Prompt, Inc. adr:;;PO Box 215 ;Cascade Locks;OR;97014;US email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 x-mozilla-html:FALSE url:http://www.commandprompt.com version:2.1 end:vcard ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Copression
Stanislaw Tristan wrote: It's a possible to compress traffic between server and client while server returns query result? It's a very actually for dial-up users. What is solution? You could use an SSH tunnel with compression to achieve this. -Neil ---(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] Tracking row updates - race condition
To fetch all updates since the last synchronization, the client would calculated a value for $lastrevision by running this query on its local database: SELECT max(revision) AS lastrevision FROM codes; It would then fetch all updated rows by running this query against the server: SELECT * FROM codes WHERE revision $lastrevision; How about SELECT * FROM codes WHERE revision $lastrevision - 100 You could use another number other than 100. As you said, the client can handle duplicates. Vincent ---(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] java.lang.OutOfMemoryError
On Tue, 15 Mar 2005, Suma Bhat wrote: I'm need to be able to insert a byte[] of size upto 25MB. With Heap size upto 512m this is failing, with a java.lang.OutOfMemoryError You need to use an 8.0 JDBC driver and a 7.4 or 8.0 server. Kris Jurka ---(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] Tracking row updates
Alex Adriaanse [EMAIL PROTECTED] writes This seems to work, except there exists a race condition. Consider the following series of events (in chronological order): 1. Initially, in the codes table there's a row with id=1, revision=1, and a row with id=2, revision=2 2. Client A begins a transaction, and updates the row with id=1, resulting in revision=3 for that row 3. Client B begins a transaction, and updates the row with id=2, resulting in revision=4 for that row 4. Client B commits the transaction 5. Client C (which has $lastrevision=2 in its local database) synchronizes with the database by doing SELECT * FROM codes WHERE revision 2; and retrieves client B's update to the row with id=2, revision=4 (it doesn't yet see the update from client A) 6. Client A commits the transaction 7. Some time later, Client C synchronizes with the database again. $lastrevision for its database is now 4, so doing SELECT * FROM codes WHERE revision 4; does not retrieve any rows. So client C never sees client A's update to the row with id=1 Essentially, the race condition occurs when the order of clients committing transactions (i.e. the updates becoming visible to other clients) differs from the order of clients generating sequence values. Do you guys have any suggestions on how to avoid this race condition, or maybe a more elegant way to synchronize the clients with the server? In my understanding, you are doing something like a CVS does. Say if you don't check out a file and you make a revision on the version you now see(say version 1), then when you want to commit, you will probabaly receive a merge required notice. Since in this interval, the file may have already updated by another user (to version 2) - he is free to do so since nobody knows that you might commit an update. To avoid this, you have to check out the file, i.e., lock the file to prevent other changes, then you are free of any merge requirement. The cost is that you locked the file and nobody could change it. So the only options are merge or lock. Regards, Qingqing ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Copression
On Mon, 21 Mar 2005 02:50 pm, Bruce Momjian wrote: Stanislaw Tristan wrote: It's a possible to compress traffic between server and client while server returns query result? It's a very actually for dial-up users. What is solution? There is always the possibility of using SSH to tunnel the connection. You get encryption and compression. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Using sequence name depending on other column
On Tue, 15 Mar 2005 08:39 pm, Andrus wrote:
I have table containing different types of documents (type A, B and C).
Each document type must have separate sequential ID starting at 1
ID of first inserted record of type A must be set to 1
ID of first inserted record of type B must be also set to 1
ID of second record of type A must be set to 2
etc.
If you are happy with the fact that a sequence may leave a whole in
the numbers. You are probably best to no set a default value for an
integer, or big integer. Then run a before trigger for each row. That
trigger will assign a value to the column based on the value given for
the type.
Russell,
thank you.
I'm a new to Postgres.
Is there any sample how to write such trigger ?
CREATE FUNCTION seq_trig() RETURNS trigger
AS $$BEGIN
IF NEW.type = 'A' THEN
NEW.sequence = nextval('a');
END IF;
IF NEW.type = 'B' THEN
NEW.sequence = nextval('b');
END IF;
RETURN NEW;
END$$
LANGUAGE plpgsql STRICT;
Something like that this may work.
Before inserting each row it should set document id from sequence
corresponding to insertable document type.
Andrus.
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Tsearch2 index silently fails on PG 7.3.2
I don't remember such problem ? What's your tsearch2 setup ?
Oleg
On Thu, 17 Mar 2005, Justin L. Kennedy wrote:
The short question is why does this:
select to_tsvector('default', coalesce(name, '') ||' '||
coalesce(description, '') ||' '|| coalesce(keywords,'')) from link_items;
give different results than this:
update link_items set linksfti=to_tsvector('default', coalesce(name, '')
||' '|| coalesce(description, '') ||' '|| coalesce(keywords,''));
select linksfti from link_items;
Here are more details:
I am working with Tsearch2 on a server with version string:
PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC 2.96
I have a table with the following schema:
CREATE TABLE link_items
(
link_id int4,
name varchar(255),
url varchar(255),
description text,
spanish int4,
spanishurl varchar(255),
lastmod date,
visible int4,
state varchar(25),
promisepractice int4,
keywords text,
linksfti tsvector
)
WITH OIDS;
ALTER TABLE link_items OWNER TO gate;
I want linksfti to hold the search engine's indexing data (indexed on
'name', 'description', and 'keywords'), so I run the following command:
update link_items set linksfti=to_tsvector('default', coalesce(name, '')
||' '|| coalesce(description, '') ||' '|| coalesce(keywords,''));
The results are pretty empty. Most have empty strings for data, other
only index one or two items in the 3 input columns.
For example, after running, my table looks like:
name;description;keywords;linksfti
American Occupational Therapy Association (AOTA) ;Nationally recognized
professional association for over 60,000 occupational therapists and
occupational therapy assistants. ;Rehabilitation Professional
Associations and Councils;'60':1 '000':2
American Physical Therapy Association (APTA);Represents more than
70,000 physical therapists, physical therapist assistants, and students of
physical therapy. ;Rehabilitation Professional Associations and
Councils;'70':1 '000':2
U.S. Deaf Ski Snowboard Association;Winter sports for people who are
deaf relevant links.;Recreation Winter Sports;'u.s':1
Texas Adaptive Aquatics;Adaptive water skiing program for people with
physical and/or mental disabilities. ;Recreation Water
Sports;'and/or':1
World T.E.A.M. Sports;Inclusive sports activities.;Recreation Team
Sports;'t.e.a.m':1
Tennessee;Official State Web Site;Legal State Agencies;
Project Vote Smart;By entering zip code, users get list of all their
elected officials. Links to elected officials' and candidates' web sites,
etc. ;Government / Public Policy General;
TRIPOD Captioned Films;Captioned Films for people who are deaf or hard
of hearing.;Recreation Captioned Movies;
When don't do it as an UPDATE and just print the contents to the screen, I
get the full expected output:
select name, description, keywords, to_tsvector('default', coalesce(name,
'') ||' '|| coalesce(description, '') ||' '|| coalesce(keywords,'')) from
link_items;
United States of America Deaf Track and Field;Promotes training of
track athletes who are deaf and coaches who are deaf and hearing.
;Recreation Track;'of':3,11 'and':7,17,22 'are':15,20 'who':14,19
'deaf':5,16,21 'hear':23 'unit':1 'coach':18 'field':8 'state':2
'track':6,12,25 'train':10 'athlet':13 'promot':9 'america':4
'recreat':24
Adventure Pursuit, Inc.;Adventure Pursuit is a group of volunteers who
like spending time with all kinds of people and focus on adventure sports
like kayaking.;Recreation Water Sports;'a':7 'is':6 'of':9,18 'on':22
'all':16 'and':20 'inc':3 'who':11 'kind':17 'like':12,25 'time':14
'with':15 'focus':21 'group':8 'kayak':26 'peopl':19 'spend':13
'sport':24,29 'water':28 'volunt':10 'pursuit':2,5 'recreat':27
'adventur':1,4,23
Using pgAdminIII, I copied (default backup/restore) the database from our
production server and put in on my personal desktop (Windows 2000, PgSQL
8.0.0) and re-ran the update query and it gave proper results.
Is it a known issue with 7.3.2, and is there a workaround without
upgrading the server to 8.0.0? We will upgrade in a few months, but we
can't take the server offline now because we have too many websites that
depend on it.
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [GENERAL] no IF - am I missing something ?
The number of lines depends merely on where you place your line breaks. IF(days_complete = 120, job_price, 0)AS Days_120 could be written as: CASE WHEN days_complete = 120 THEN job_price ELSE 0 END AS Days_120 There might be somewhat less syntactic sugar, but this is not a five line expression and, to me, is more readable than a comma-delimited list where position alone indicates function in the expression. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Mar 17, 2005, at 1:57 PM, Richard Crawley wrote: Hello all. In the tail end of converting an app from MySQL to psql. I have this code : snip IF(days_complete = -120, job_price,0) AS Days_120, IF(days_complete BETWEEN -119 AND -90, job_price,0) AS Days_90, IF(days_complete BETWEEN -89 AND -60, job_price,0) AS Days_60, IF(days_complete BETWEEN -59 AND -30, job_price,0) AS Days_30, IF(days_complete BETWEEN -29 AND 0, job_price,0) AS current snip It builds an aged debt report, and there are similar versions that SUM(IF ..) to give me debt by customer etc. All the questions I've seen about IF end up with people saying use CASE and I'm sure it would work. But do you lot really use 5 lines for each IF ? Doesn't it seem kind of ugly ? Or do you all secretly write a quick IF function ? I'm interested, and I half suspect that I'm missing a more elegant solution. thanks Rich ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] weird error on installing postgresql in Windows XP
Hi, I have installed previous version of postgresql. 8.x. I uninstalled it. Then remove the superuser in My Computer ( right click ) -- Properties -- in tab Advanced -- Settings button. I try to install postgresql 8.0.1. In service configuration, I checked the install as service checkbox. Filled the form. After suggesting me to change the weak password, it said that Internal account lookup failure: No mapping between account names and security IDs was done. What is going on here Thank you. ---(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] Test
Test
Re: [GENERAL] no IF - am I missing something ?
On Mon, Mar 21, 2005 at 12:35:22AM -0600, Thomas F.O'Connell wrote: The number of lines depends merely on where you place your line breaks. IF(days_complete = 120, job_price, 0)AS Days_120 could be written as: CASE WHEN days_complete = 120 THEN job_price ELSE 0 END AS Days_120 There might be somewhat less syntactic sugar, but this is not a five line expression and, to me, is more readable than a comma-delimited list where position alone indicates function in the expression. CASE is also standard SQL, whereas IF isn't (unless I've overlooked it in the SQL:2003 draft). -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] PostgreSQL v7.4.7 support platform?
Hi! Could you please answer me whether PostgreSQL v7.4.7 (on x86 platform) is compatible with FreeBSD v5.3 or its safer to use FreeBSD v4.11? Excuse me for my English. Thank you in advance!
