[GENERAL] function with multiple return values
I've created the following function: CREATE OR REPLACE FUNCTION latest ( lot_id int4, condition text, OUT perc smallint, OUT entry_date date ) RETURNS SETOF record AS ' BEGIN RETURN QUERY SELECT t1.perc, t1.entry_date FROMt1, t2 WHERE t1.condition_id=t2.id and t1.lot_id = $1 and t2.code = $2 ORDER BY entry_date DESC LIMIT 1; END; ' language 'plpgsql' VOLATILE; It works for this: select (latest(38787,'IP')).* returning perc and entry_date each in it's own column. Problem is: select (latest(38787,'IP')).*, (latest(38787,'FI')).*; returns 4 columns: perc, entry_date, perc, entry_date Tried: select perc as p1, perc as perc2 from ( select (latest(38787,'IP')).*, (latest(38787,'FI')).* ) as foo; just to see -- it says perc is ambiguous... well yes it is! :) Ideas on how to uniquely name the first and second set of perc, entry_date? Or maybe there is a different way to return 2 values from a function? Thanks, Scott -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Run postgresql\9.0\data on a RAID-1 disk on my Local Area Network (LAN)
Hello I´m trying to install PostgreSQL and PostGIS asking PostgreSQL to store data on my new Netgear NAS Duo LAN-disk in a Windows XP sp2 environment. In the Setup Wizard I accept C:\Program\PostgreSQL\9.0 and I change C:\Program\PostgreSQL\9.0\data to either //R1/aa_db/PostgreSQL\9.0\data or P:\PostgreSQL\9.0\data where P:\ = //R1/aa_db/ on the computer from which I´m installing Postgresql. R1 is the name of the Netgear disk och aa_db the Share/folder I want to store the PostgreSQL data in. I have tried every possible combination I can think of. Sometimes the installation seems to work, but in pgAdmin Connected? says No. If I try to install PostGIS afterwards I crashes during the EnterpriseDB Stackbuilder installation. After several hours I can´t find any useful information in the manual or on the FORUMs. If anybody could give me a hint where I should look I would be so happy. Regards Anders Söderman Stockholm Sweden
Re: [GENERAL] function with multiple return values
Scott Serr se...@theserrs.net wrote: I've created the following function: CREATE OR REPLACE FUNCTION latest ( lot_id int4, condition text, OUT perc smallint, OUT entry_date date ) RETURNS SETOF record AS ' BEGIN RETURN QUERY SELECT t1.perc, t1.entry_date FROMt1, t2 WHERE t1.condition_id=t2.id and t1.lot_id = $1 and t2.code = $2 ORDER BY entry_date DESC LIMIT 1; END; ' language 'plpgsql' VOLATILE; It works for this: select (latest(38787,'IP')).* returning perc and entry_date each in it's own column. Problem is: select (latest(38787,'IP')).*, (latest(38787,'FI')).*; returns 4 columns: perc, entry_date, perc, entry_date Tried: select perc as p1, perc as perc2 from ( select (latest(38787,'IP')).*, (latest(38787,'FI')).* ) as foo; just to see -- it says perc is ambiguous... well yes it is! :) Ideas on how to uniquely name the first and second set of perc, entry_date? Or maybe there is a different way to return 2 values from a function? You can use alias-names for the 2 queries, like: test=*# select * from foo(); a | b ---+--- 1 | 2 (1 row) Time: 0.279 ms test=*# select foobar.a as x, foobar.b as y, bar.* from (select * from foo()) foobar, (select * from foo()) bar ; x | y | a | b ---+---+---+--- 1 | 2 | 1 | 2 (1 row) Now you have unique column names. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly. (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Looking for a Generic lightweight job queueing (stack) implementation.
Hi, I am looking for an easy to use job queueing system. Where a job is a record in a table and several aggressive worker processes (from multiple instances of a client application) each can take a single job at a time. A job may only be collected only once. The job queue will act like a stack and take care of the concurrency issues. The job queue will have no prior knowledge of the consumers, probably an event based (job firing) system may not be appropriate in this case. Allan. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need magic to clean strings from unconvertible UTF8
Am 07.11.2010 06:54, schrieb John R Pierce: On 11/06/10 9:35 PM, Andreas wrote: somehow there have unconvertible characters sneaked into my DB. Very probaply they came in via Imports from MS-Access. Access doesn't complain but when I try to export stuff with pgAdmin to csv I get an error that some char is not representable in the local charset. I can find the problematic rows. How could I delete every char in a string that can't be converted to WIN1252? One idea that comes to my mind issue a SET CLIENT_ENCODING 'C'; then find and fix any problems with SQL. The C aka Posix encoding lets you directly manipulate the characters as binary. or set the client_encoding to whatever the database encoding is, and find the characters that you know aren't compatible with WIN1252 and change them Actually that's the problem. How would I delete everything, that is not win1252 compatible. I'm certain those are junk in my case so I'd rather get rid of them and not convert them to anything. In some cases they even are illegal UTF8 codes that must have been created on the way when the data was transferred between a couple of file formats among which was excel and who knows what. As I said, I can find the rows with such junk but I don't know how to clean up the text-fields without doing it manually. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] It is possible to update more than 1 table in the same update statement?
Hi, I need to update various tables in the same update. It is possible to do it? Best Regards,
Re: [GENERAL] It is possible to update more than 1 table in the same update statement?
On 7 November 2010 15:58, Andre Lopes lopes80an...@gmail.com wrote: Hi, I need to update various tables in the same update. It is possible to do it? Best Regards, Could you describe a little bit more what you want to achieve? regards Szymon
Re: [GENERAL] It is possible to update more than 1 table in the same update statement?
On Nov 7, 2010, at 7:58 AM, Andre Lopes wrote: need to update various tables in the same update. It is possible to do it? Transactions??? -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] It is possible to update more than 1 table in the same update statement?
Sorry for not explain well. I mean update more than one table at the same time with something like this: update table1, table2 set table1.f1 = 'aaa', table2.date = '2001-01-01' where table1.id = 'x1' and table2.id = 'x1' Something like this is possible? It is the best way to do it? Best Regards, On Sun, Nov 7, 2010 at 3:11 PM, Scott Ribe scott_r...@killerbytes.comwrote: On Nov 7, 2010, at 7:58 AM, Andre Lopes wrote: need to update various tables in the same update. It is possible to do it? Transactions??? -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice
Re: [GENERAL] It is possible to update more than 1 table in the same update statement?
On Nov 7, 2010, at 8:20 AM, Andre Lopes wrote: I mean update more than one table at the same time with something like this: Why? Use a transaction. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Looking for a Generic lightweight job queueing (stack) implementation.
On 7 November 2010 15:51, Allan Kamau kamaual...@gmail.com wrote: Hi, I am looking for an easy to use job queueing system. Where a job is a PgQ may be? http://wiki.postgresql.org/wiki/PGQ_Tutorial Allan. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sergey Konoplev Blog: http://gray-hemp.blogspot.com / Linkedin: http://ru.linkedin.com/in/grayhemp / JID/GTalk: gray...@gmail.com / Skype: gray-hemp -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] It is possible to update more than 1 table in the same update statement?
On 7 November 2010 16:20, Andre Lopes lopes80an...@gmail.com wrote: Sorry for not explain well. I mean update more than one table at the same time with something like this: update table1, table2 set table1.f1 = 'aaa', table2.date = '2001-01-01' where table1.id = 'x1' and table2.id = 'x1' Something like this is possible? It is the best way to do it? Best Regards, This is not possible, however you could do two updates in one transaction. regards Szymon
Re: [GENERAL] Run postgresql\9.0\data on a RAID-1 disk on my Local Area Network (LAN)
2010/11/7 Anders Söderman anders.soder...@gisassistans.se: Hello – I´m trying to install PostgreSQL and PostGIS ”asking” PostgreSQL to store data on my new Netgear NAS Duo LAN-disk in a Windows XP sp2 environment. Note that storing the PostgreSQL data on a SMB mount is *not* safe and not supported. I don't know how your specific disk does it, but if it uses SMB or NFS, this is not safe. If it's an iSCSI mount, for example, it should be safe. But it certainly looks like an SMB mount - in which case you should rethink your strategy. I would bet your problems are caused by this. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] It is possible to update more than 1 table in the same update statement?
The only way I can guarantee a transaction is in a Function or there are other ways? Best Regards, On Sun, Nov 7, 2010 at 3:22 PM, Szymon Guz mabew...@gmail.com wrote: On 7 November 2010 16:20, Andre Lopes lopes80an...@gmail.com wrote: Sorry for not explain well. I mean update more than one table at the same time with something like this: update table1, table2 set table1.f1 = 'aaa', table2.date = '2001-01-01' where table1.id = 'x1' and table2.id = 'x1' Something like this is possible? It is the best way to do it? Best Regards, This is not possible, however you could do two updates in one transaction. regards Szymon
Re: [GENERAL] It is possible to update more than 1 table in the same update statement?
On Nov 7, 2010, at 8:37 AM, Andre Lopes wrote: The only way I can guarantee a transaction is in a Function or there are other ways? http://www.postgresql.org/docs/9.0/interactive/tutorial-transactions.html -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] function with multiple return values
Andreas Kretschmer akretsch...@spamfence.net writes: Scott Serr se...@theserrs.net wrote: Ideas on how to uniquely name the first and second set of perc, entry_date? You can use alias-names for the 2 queries, like: test=*# select foobar.a as x, foobar.b as y, bar.* from (select * from foo()) foobar, (select * from foo()) bar ; You don't really need the sub-selects: you can put aliases on functions in FROM. select * from foo(...) as f1(a,b), foo(...) as f2(x,y); regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] function with multiple return values
Tom Lane t...@sss.pgh.pa.us wrote: Andreas Kretschmer akretsch...@spamfence.net writes: Scott Serr se...@theserrs.net wrote: Ideas on how to uniquely name the first and second set of perc, entry_date? You can use alias-names for the 2 queries, like: test=*# select foobar.a as x, foobar.b as y, bar.* from (select * from foo()) foobar, (select * from foo()) bar ; You don't really need the sub-selects: you can put aliases on functions in FROM. select * from foo(...) as f1(a,b), foo(...) as f2(x,y); Right, thx, blackout ... Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly. (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Finding rank of a single record
Sorry Rob, but it fails with: pref= select * from ( select u.id, u.first_name, u.city, m.money, rank() over (order by money desc) from pref_users u, pref_money m where m.yw=to_char(current_timestamp, '-IW') ) all_ranks ar where ar.id='OK138239987797'; ERROR: syntax error at or near ar LINE 9: ) all_ranks ar ^ Do you have a doc pointer for me here? I've forgotten to mention that I'm using PostgreSQL 8.4.5 Regards Alex P.S.: Here again my tables, I'm trying to get a rank for 1 user there: pref= \d pref_users; Table public.pref_users Column |Type | Modifiers +-+--- id | character varying(32) | not null first_name | character varying(32) | last_name | character varying(32) | female | boolean | avatar | character varying(128) | city | character varying(32) | lat| real| lng| real| last_login | timestamp without time zone | default now() last_ip| inet| medals | smallint| Indexes: pref_users_pkey PRIMARY KEY, btree (id) Check constraints: pref_users_lat_check CHECK ((-90)::double precision = lat AND lat = 90::double precision) pref_users_lng_check CHECK ((-90)::double precision = lng AND lng = 90::double precision) pref_users_medals_check CHECK (medals 0) Referenced by: TABLE pref_misere CONSTRAINT pref_misere_id_fkey FOREIGN KEY (id) REFERENCES pref_users(id) TABLE pref_money CONSTRAINT pref_money_id_fkey FOREIGN KEY (id) REFERENCES pref_users(id) TABLE pref_pass CONSTRAINT pref_pass_id_fkey FOREIGN KEY (id) REFERENCES pref_users(id) TABLE pref_rate CONSTRAINT pref_rate_obj_fkey FOREIGN KEY (obj) REFERENCES pref_users(id) TABLE pref_rate CONSTRAINT pref_rate_subj_fkey FOREIGN KEY (subj) REFERENCES pref_users(id) pref= \d pref_money; Table public.pref_money Column | Type |Modifiers +---+- id | character varying(32) | money | integer | not null yw | character(7) | default to_char(now(), '-IW'::text) Indexes: pref_money_yw_index btree (yw) Foreign-key constraints: pref_money_id_fkey FOREIGN KEY (id) REFERENCES pref_users(id) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Finding rank of a single record
And: pref= select * from ( select u.id, u.first_name, u.city, m.money, rank() over (order by money desc) from pref_users u, pref_money m where m.yw=to_char(current_timestamp, '-IW') ) ar where ar.id='OK138239987797'; works, but delivers 800 different ranks: id | first_name |city | money | rank ++-+---+- OK138239987797 | Иван | Новосибирск | 2169 | 1 OK138239987797 | Иван | Новосибирск | 1955 |3479 OK138239987797 | Иван | Новосибирск | 1948 |6957 OK138239987797 | Иван | Новосибирск | 1060 | 10435 OK138239987797 | Иван | Новосибирск | 1034 | 13913 OK138239987797 | Иван | Новосибирск | 1012 | 17391 OK138239987797 | Иван | Новосибирск | 929 | 20869 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] function with multiple return values
On 11/07/2010 08:53 AM, Tom Lane wrote: Andreas Kretschmerakretsch...@spamfence.net writes: Scott Serrse...@theserrs.net wrote: Ideas on how to uniquely name the first and second set of perc, entry_date? You can use alias-names for the 2 queries, like: test=*# select foobar.a as x, foobar.b as y, bar.* from (select * from foo()) foobar, (select * from foo()) bar ; You don't really need the sub-selects: you can put aliases on functions in FROM. select * from foo(...) as f1(a,b), foo(...) as f2(x,y); regards, tom lane Thanks Tom, Andreas, and Osvaldo... I've found I really need these on the Select part rather than the From. select otherstuff.*, foo(...) as f1(a,b), foo(...) as f2(x,y) from otherstuff; ...won't work. It says: subquery must return only one column Funny thing is select (foo(...)).*, (foo(...)).*; ...works fine, just has duplicate column names, so they are hard to get at. I modeled this after examples here http://www.postgresonline.com/journal/index.php?/archives/129-Use-of-OUT-and-INOUT-Parameters.html I really only want a single record back from my function, but the multi-record return looked easier. I need some kind of control of how the columns are named per call. Maybe this isn't possible... not alot of docs in this area. Thanks, Scott -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] function with multiple return values
2010/11/7 Scott Serr se...@theserrs.net: On 11/07/2010 08:53 AM, Tom Lane wrote: Andreas Kretschmerakretsch...@spamfence.net writes: Scott Serrse...@theserrs.net wrote: Ideas on how to uniquely name the first and second set of perc, entry_date? You can use alias-names for the 2 queries, like: test=*# select foobar.a as x, foobar.b as y, bar.* from (select * from foo()) foobar, (select * from foo()) bar ; You don't really need the sub-selects: you can put aliases on functions in FROM. select * from foo(...) as f1(a,b), foo(...) as f2(x,y); regards, tom lane Thanks Tom, Andreas, and Osvaldo... I've found I really need these on the Select part rather than the From. select otherstuff.*, foo(...) as f1(a,b), foo(...) as f2(x,y) from otherstuff; ...won't work. It says: subquery must return only one column Funny thing is select (foo(...)).*, (foo(...)).*; ...works fine, just has duplicate column names, so they are hard to get at. Attention: this syntax is great, but function is evaluated for every column one times! Regards Pavel Stehule I modeled this after examples here http://www.postgresonline.com/journal/index.php?/archives/129-Use-of-OUT-and-INOUT-Parameters.html I really only want a single record back from my function, but the multi-record return looked easier. I need some kind of control of how the columns are named per call. Maybe this isn't possible... not alot of docs in this area. Thanks, Scott -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Libpq is very slow on windows but fast on linux.
Thanks, I will a look at that latter, but I am not too hopeful as I
get the same results on Vista as XP. One thought that has occurred
to me is could it be libpq.dll versions? I am not able to test until
later on today, but the server is postgresql 8.3 and the XP machine
is using libpq frim the 8.4 and the vista is using 9.0
Any way, I will try that this afternoon.
On Sun, Nov 7, 2010 at 4:36 PM, John R Pierce pie...@hogranch.com wrote:
On 11/06/10 6:13 PM, Rob Brown-Bayliss wrote:
Ok, So I did that, in the windows capture file are many many lines of
Red text on a black background, I assume thats a bad thing. It's
every second or third line vs just a handful on the linux capture.
Most of these lines are like:
postgresql [ACK] Seq=5429 Ack=# Win=65700 Len=0
Where the ACK number is always different.
As I said before I really don't know what I am looking at.
While I come from a purely software background, over the years I've had to
debug exactly these sorts of situations, and determined that I needed a
better understanding of TCP/IP. I found the book Internetworking with
TCP/IP by Douglas Comer et al extremely useful, particularlly volume II.
Understanding how the underlying network protocols function has been handy
time after time. I don't pretend to be a network engineer, but I can
communicate with them in their own language and thats been extremely helpful
too.
In Wireshark, the color coding itself is pretty arbitrary, and sometimes
excessively lurid :)
every IP (internet protocol) packet has a source IP address, a destination
IP address, and a protocol. TCP is protocol #6 UDP is 17, etc.
every TCP packet has an IP packet header (above) plus a source port, a
destination port, a sequence number, a ack value, a offset, some flags, a
checksum, etc.
once a TCP socket session has been fully established, the general sequence
is, one side sends data packets, and the other side acknowleges them[1].
Data packets should be around 1500 bytes each unless a smaller MTU has been
negotiated. If one 1500 byte packet was sent and the server then had to
wait for an acknowlege before sending another, you'd only be able to send
one data packet every (PING TIME) milliseconds, which in yoru case here
would be *excruciatingly* slow, so instead, TCP keeps sending data up to
RWIN (TCP Recieve Window Size) bytes before it requires an ACK be sent back.
That value on the ACK is what data its acknowledging, and undoubtably
increases with each ACK. The RWIN value is negotiated between the client
and the server, and Windows is notorious for using values too small for a
wide area long pingtime fast network like this.
If you're seeing a lot of ACKs it suggests that the RWIN value may be too
low. Of particular importance is the timing of the packets coming from
the remote server, do you get several fast, then a delay, or do they come in
back to back at wire speed? Its actually quite OK and common for a
client to send ACKs back 'early', as soon as it gets a packet to keep the
data flowing, the key is if the sender is waiting for those ACKs, then RWIN
is too small.
You might have 1Mbyte/sec download bandwidth between this server and your
client (arbitrary value I picked for sake of discussion).
your round trip packet latency is 350mS, I believe you said. thats about
1/3rd of a second, which means about 300Kbytes can be in the pipe. If
your RWIN is, say, 64K (typical value used on a local network), the sender
will only be able to send 64k before having to wait 350mS for a ACK. This
will slow your pipe down about 5:1. If instead, the RWIN was above 300k,
then the client will be able to keep up with the ACKs without the sender
having to wait. Generally RWIN should be about twice as high as the data
rate * the round trip latency.
I haven't kept up with the details, but I definately remember having to
manually crank RWIN up to the 500kbyte range as Windows wouldn't
automatically go over 64k (in fact, going above 64k requires a TCP extension
called Window Scaling which wasn't supported by earlier OSs at all)
here's a typical sequence...
server - client
data 0 len 1500 {1500 bytes of data}
data 1500 len 1500 {1500 bytes of data}
data 3000 len 1500 {1500 bytes of data}
data 4500 len 1500 {1500 bytes of data}
ACK=3000
data 6000 len 1500 {1500 bytes of data)
data 7500 len 1500 {1500 bytes of data)
ACK=6000
acking 3000 implicitly acknowleges everything before that.
anyways, this article http://en.wikipedia.org/wiki/TCP_Tuning and various
linked articles may help you here.
and this, http://technet.microsoft.com/en-us/magazine/2007.01.cableguy.aspx
explains how to manually crank up a higher RWIN in Windows XP in particular
which isn't very smart about it.
[1] simplification. sockets are bidirectional, and both ends can send data
to the other end, and the acknowledgments for received data
Re: [GENERAL] Finding rank of a single record
Alexander Farber wrote: And: pref= select * from ( select u.id, u.first_name, u.city, m.money, rank() over (order by money desc) from pref_users u, pref_money m where m.yw=to_char(current_timestamp, '-IW') ) ar where ar.id='OK138239987797'; works, but delivers 800 different ranks: id | first_name |city | money | rank ++-+---+- OK138239987797 | Иван | Новосибирск | 2169 | 1 OK138239987797 | Иван | Новосибирск | 1955 |3479 OK138239987797 | Иван | Новосибирск | 1948 |6957 OK138239987797 | Иван | Новосибирск | 1060 | 10435 OK138239987797 | Иван | Новосибирск | 1034 | 13913 OK138239987797 | Иван | Новосибирск | 1012 | 17391 OK138239987797 | Иван | Новосибирск | 929 | 20869 I haven't created your tables, but it looks to me like the sub-select needs something from u to m. When run by itself what does the sub-select generate. (I'm tempted to recomment count(*) cause I think you're getting a cross-product.) This could all be a cut / paste error in my first response. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Libpq is very slow on windows but fast on linux.
On 07/11/10 09:13, Rob Brown-Bayliss wrote: Ok, So I did that, in the windows capture file are many many lines of Red text on a black background, I assume thats a bad thing. If you examine the packet it'll say invalid checksum. This will be because your network card is doing TCP checksum offloading, and you can almost certainly ignore the warning or turn it off. See: http://wiki.wireshark.org/TCP_Checksum_Verification http://www.wireshark.org/docs/wsug_html_chunked/ChAdvChecksums.html As I said before I really don't know what I am looking at. Use the wireshark throughput graph for a starter. If you don't understand TCP/IP, wireshark probably won't help you much - but then, I learned about TCP/IP with wireshark. Consider doing the same - dive in, read about it, and have a play. I won't be too surprised if the issue turns out to be related to TCP window sizes and window scaling differences between the two machines. -- Craig Ringer Tech-related writing: http://soapyfrogs.blogspot.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Libpq is very slow on windows but fast on linux.
Hi No Joy. The tcp window stuff didn't make any difference. I tried the unscientific speedtest.net to a server in Canada and all 3 machines (several times each)(. Linux, XP and Vista. The results are all too close to call. Yet psql or pgadmin are 4 or 5 times slower on the windows machines. Dropping back to 8.3 made no difference. The XP machine is a fresh install, it only has MS office and postgresql 8.3 installed, just to test this. I have come across many similar posts on the net, but there were several years ago. Is any one else seeing similar results? On Mon, Nov 8, 2010 at 3:19 PM, Craig Ringer cr...@postnewspapers.com.au wrote: On 07/11/10 09:13, Rob Brown-Bayliss wrote: Ok, So I did that, in the windows capture file are many many lines of Red text on a black background, I assume thats a bad thing. If you examine the packet it'll say invalid checksum. This will be because your network card is doing TCP checksum offloading, and you can almost certainly ignore the warning or turn it off. See: http://wiki.wireshark.org/TCP_Checksum_Verification http://www.wireshark.org/docs/wsug_html_chunked/ChAdvChecksums.html As I said before I really don't know what I am looking at. Use the wireshark throughput graph for a starter. If you don't understand TCP/IP, wireshark probably won't help you much - but then, I learned about TCP/IP with wireshark. Consider doing the same - dive in, read about it, and have a play. I won't be too surprised if the issue turns out to be related to TCP window sizes and window scaling differences between the two machines. -- Craig Ringer Tech-related writing: http://soapyfrogs.blogspot.com/ -- -- Rob -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Run postgresql\9.0\data on a RAID-1 disk on my Local Area Network (LAN)
Thanks for your help, Magnus. This problem was way over my head, so I´m glad I can leave it behind. I´ll will try to secure the PostgreSQL/PostGIS database using ordinary backup methods. Regards Anders Söderman Stockholm Sweden -Original Message- From: Magnus Hagander [mailto:mag...@hagander.net] Sent: den 7 november 2010 16:33 To: Anders Söderman Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Run postgresql\9.0\data on a RAID-1 disk on my Local Area Network (LAN) 2010/11/7 Anders Söderman anders.soder...@gisassistans.se: Hello I´m trying to install PostgreSQL and PostGIS asking PostgreSQL to store data on my new Netgear NAS Duo LAN-disk in a Windows XP sp2 environment. Note that storing the PostgreSQL data on a SMB mount is *not* safe and not supported. I don't know how your specific disk does it, but if it uses SMB or NFS, this is not safe. If it's an iSCSI mount, for example, it should be safe. But it certainly looks like an SMB mount - in which case you should rethink your strategy. I would bet your problems are caused by this. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
Re: [GENERAL] Run postgresql\9.0\data on a RAID-1 disk on my Local Area Network (LAN)
Hello, On Nov 7, 2010, at 1:22 PM, Anders Söderman wrote: Hello – I´m trying to install PostgreSQL and PostGIS ”asking” PostgreSQL to store data on my new Netgear NAS Duo LAN-disk in a Windows XP sp2 environment. In the Setup Wizard I accept C:\Program\PostgreSQL\9.0 and I change C:\Program\PostgreSQL\9.0\data to either //R1/aa_db/PostgreSQL\9.0\data or P:\PostgreSQL\9.0\data where P:\ = //R1/aa_db/ on the computer from which I´m installing Postgresql. R1 is the name of the Netgear disk och aa_db the Share/folder I want to store the PostgreSQL data in. I have tried every possible combination I can think of. Sometimes the installation seems to work, but in pgAdmin “Connected?” says “No”. If I try to install PostGIS afterwards I crashes during the EnterpriseDB Stackbuilder installation. After several hours I can´t find any useful information in the manual or on the FORUMs. If anybody could give me a hint where I should look I would be so happy. Regards Anders Söderman Stockholm Sweden What exactly is the error message you get while installing PostgreSQL? Can you attach the installation log (%TEMP%\install-postgresql.log) or (%TEMP%\bitrock_installer_some number.log). -- Regards, Sachin Srivastava EnterpriseDB, the Enterprise PostgreSQL company.
