Re: [GENERAL] Incomplete startup packet help needed
Hi Juan Antonio: On Tue, Nov 4, 2014 at 9:54 PM, memtec wrote: > You can force this message executing: > $ echo | nc SERVER_IP 5432 > 2014-11-04 21:44:37 CET LOG: incomplete startup packet > What do you need help on? PG uses a binary protocol, you sent it bad data ( I think this will open connection, send a \n byte, shutdown it ) and it complained in the first check ( I suppose it is due to the connection expecting an startup packet with a given format, and it couldn't get it because it's longer than 1 byte, everything is prety well documented, RTFM ). Regards. Francisco Olarte.
Re: [GENERAL] Performance problem on 2 PG versions on same query
Thank you very much for the insights (and how to read correctly a explain :)). There is a difference in data between the 2 databases, crons where running on one and not the other. But your insights enabled us to dive deeper, thanks ! 2014-11-05 18:30 GMT+01:00 Tom Lane : > "Tomas Vondra" writes: > > Dne 5 Listopad 2014, 18:10, Tom Lane napsal(a): > >> For some reason there's over 1000 times more rows in andalertsmatch in > >> the 9.1.14 installation. I'm betting on a foulup somewhere in the data > >> dump/restore process. > > > I'd bet that's mostly due to rescans in the merge join ... > > Doh, I should have thought of that :-(. But still, the difference in > the number of final output rows is pretty compelling evidence that > it's not really the same data. > > regards, tom lane >
Re: [GENERAL] DATA corruption after promoting slave to master
Hi Krit, It looks like your actual problem is here: > Index Scan using t1_orderid_creationtime_idx on t1 > (cost=0.43..1181104.36 rows=9879754 width=158) > (actual time=0.021..60830.724 rows=2416614 loops=1 This index scan estimates 9.8M rows, and had to touch 2.4M. The issue is that your LIMIT clause makes the planner overly optimistic. The worst case cost estimate for this part of the query is about 1.2M, which is much higher than the SEQ SCAN variation you posted. The planner must think it can get the rows without incurring the full cost, otherwise I can't see how the 1.2M cost estimate wasn't rolled into the total estimate. Unfortunately behavior like this is pretty common when using LIMIT clauses. Sometimes the planner thinks it can get results much faster than it actually can, and it ends up reading a much larger portion of the data than it assumed would be necessary. Just out of curiosity, Can you tell me what your default_statistics_target is? __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] psql connection via localhost or 127.0.0.1
I am using postgres 9.4beta3 (Debian jessie) this is my pg_hba.conf -- localallpostgrespeer localallallmd5 hostallall127.0.0.1/32md5 hostallall::1/128md5 -- but if I use psql to connect via tcp to the server (on the same machine) I can see two differente behaviours if I use "localhost" or "127.0.0.1" If I understand correctly the first two lines are useful only for Unix sockets and not for tcp-ip connection. -- with localhost edoardo@host:~$ psql -h localhost -U postgres psql (9.4beta3) Connessione SSL (protocollo: TLSv1.2, cifrario: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compressione: disattivato) Digita "help" per avere un aiuto. postgres=# -- no password request -- with localhost edoardo@happy:~$ psql -h 127.0.0.1 -U postgres Inserisci la password per l'utente postgres: -- with password request. Why localhost is not an alias for 127.0.0.1 ? I did some search on Google but with no useful results thank you Edoardo -- 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] psql connection via localhost or 127.0.0.1
On 11/06/2014 06:34 AM, Edoardo Panfili wrote: I am using postgres 9.4beta3 (Debian jessie) this is my pg_hba.conf -- localallpostgrespeer localallallmd5 hostallall127.0.0.1/32md5 hostallall::1/128md5 -- but if I use psql to connect via tcp to the server (on the same machine) I can see two differente behaviours if I use "localhost" or "127.0.0.1" If I understand correctly the first two lines are useful only for Unix sockets and not for tcp-ip connection. -- with localhost edoardo@host:~$ psql -h localhost -U postgres psql (9.4beta3) Connessione SSL (protocollo: TLSv1.2, cifrario: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compressione: disattivato) Digita "help" per avere un aiuto. postgres=# -- no password request -- with localhost edoardo@happy:~$ psql -h 127.0.0.1 -U postgres Inserisci la password per l'utente postgres: -- with password request. Why localhost is not an alias for 127.0.0.1 ? See here: http://www.postgresql.org/docs/9.3/interactive/libpq-connect.html hostaddr I did some search on Google but with no useful results thank you Edoardo -- Adrian Klaver adrian.kla...@aklaver.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] Performance of UPDATE SET = FROM vs UPDATE SET = (SELECT ...)
These updates aren't equivalent. It's very important you know this, because you're also inflating your table with a lot of extra updated rows. Take the first UPDATE: > UPDATE second SET time1 = orig.time1 > FROM orig > WHERE second.key1 = orig.key1; If you wrote this as a SELECT, it would look like this: SELECT second.time1, orig.time1 FROM second JOIN ORIG ON (second.key1 = orig.key1) Since second is a many to one subset of orig, you now have several simultaneous updates. Your second UPDATE: > UPDATE second SET time1 = (SELECT orig.time1 FROM orig,second > WHERE orig.key1 = second.key1 LIMIT 1); Is equivalent to this SELECT: SELECT second.time1, (SELECT orig.time1 FROM orig,second WHERE orig.key1 = second.key1 LIMIT 1) FROM second; Meaning you'd only get as many updates as there are rows in second. The difference is your LIMIT 1. However, since you're not using an ORDER BY clause, the actual value you get for time1 will be indeterminate. Something like this would remove the row inflation and fix the random time1 behavior, but I'm not sure it was your intent: UPDATE second SET time1 = orig.time1 FROM (SELECT DISTINCT ON (key1) key1, time1 FROM orig ORDER BY key1, time1 DESC) sub WHERE second.key1 = sub.key1; __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email --- Begin Message --- Why does the UPDATE SET = FROM choose a more poorly performing plan than the UPDATE SET = (SELECT ...)? It seems to me that it is the same join. I'm using 9.3.5. CREATE TABLE orig ( key1VARCHAR(11) PRIMARY KEY, time1 TIME ); INSERT INTO orig (key1, time1) SELECT a::TEXT, (((random()*100)::INT % 24)::TEXT || ':' || ((random()*100)::INT % 60)::TEXT)::TIME FROM generate_series(800, 8000200) a; CREATE INDEX odx ON orig(key1); CREATE TABLE second (LIKE orig); INSERT INTO second (key1) SELECT (800+(((random()*100)::INT) % 100))::TEXT FROM generate_series(1,40); EXPLAIN ANALYZE UPDATE second SET time1 = orig.time1 FROM orig WHERE second.key1 = orig.key1; QUERY PLAN Update on second (cost=69461.02..106082.02 rows=40 width=32) (actual time=16033.023..16033.023 rows=0 loops=1) -> Hash Join (cost=69461.02..106082.02 rows=40 width=32) (actual time=7698.445..12992.039 rows=40 loops=1) Hash Cond: ((second.key1)::text = (orig.key1)::text) -> Seq Scan on second (cost=0.00..12627.00 rows=40 width=18) (actual time=49.820..791.397 rows=40 loops=1) -> Hash (cost=31765.01..31765.01 rows=201 width=26) (actual time=7648.540..7648.540 rows=201 loops=1) Buckets: 4096 Batches: 128 Memory Usage: 717kB -> Seq Scan on orig (cost=0.00..31765.01 rows=201 width=26) (actual time=0.014..3655.844 rows=201 loops=1) Total runtime: 16033.193 ms (8 rows) UPDATE second SET time1 = NULL; EXPLAIN ANALYZE UPDATE second SET time1 = (SELECT orig.time1 FROM orig,second WHERE orig.key1 = second.key1 LIMIT 1); QUERY PLAN Update on second (cost=3.60..19078.19 rows=1279959 width=18) (actual time=4642.453..4642.453 rows=0 loops=1) InitPlan 1 (returns $1) -> Limit (cost=0.43..3.60 rows=1 width=8) (actual time=2.611..2.613 rows=1 loops=1) -> Nested Loop (cost=0.43..4056331.83 rows=1279959 width=8) (actual time=2.606..2.606 rows=1 loops=1) -> Seq Scan on second second_1 (cost=0.00..19074.59 rows=1279959 width=12) (actual time=2.487..2.487 rows=1 loops=1) -> Index Scan using odx on orig (cost=0.43..3.14 rows=1 width=20) (actual time=0.098..0.098 rows=1 loops=1) Index Cond: ((key1)::text = (second_1.key1)::text) -> Seq Scan on second (cost=0.00..19074.59 rows=1279959 width=18) (actual time=6.420..817.739 rows=40 loops=1) Total runtime: 4642.561 ms (9 rows) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general --- End Message --- -- 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] psql connection via localhost or 127.0.0.1
On 11/6/2014 6:34 AM, Edoardo Panfili wrote: Why localhost is not an alias for 127.0.0.1 ? grep localhost /etc/hosts I've seen some weird stuff on some systems. -- john r pierce 37N 122W somewhere on the middle of the left coast -- 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] psql connection via localhost or 127.0.0.1
Edoardo Panfili writes: > I am using postgres 9.4beta3 (Debian jessie) > this is my pg_hba.conf > -- > localallpostgrespeer > localallallmd5 > hostallall127.0.0.1/32md5 > hostallall::1/128md5 > -- > but if I use psql to connect via tcp to the server (on the same machine) > I can see two differente behaviours if I use "localhost" or "127.0.0.1" > If I understand correctly the first two lines are useful only for Unix > sockets and not for tcp-ip connection. Yeah. On modern Linux distros it's quite likely that "localhost" is resolving as IPv6 ::1, not 127.0.0.1, but in neither case would it match your "local" lines. In any case, your first connection attempt is definitely not connecting over a Unix socket because we never use SSL with Unix sockets. I can think of two plausible theories: 1. The postmaster isn't actually using the pg_hba.conf you think it is. (Maybe you modified the file and forgot to do a "pg_ctl reload"?) 2. You have a "service file" active that is capturing the server name "localhost" and redefining it to mean something other than the obvious meaning. http://www.postgresql.org/docs/9.3/interactive/libpq-pgservice.html A useful test would be to try "psql -h ::1" and see which way that behaves. If it does the same thing as "-h localhost" then we can eliminate the service-file theory. 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] psql connection via localhost or 127.0.0.1
On 11/06/2014 07:00 AM, John R Pierce wrote: On 11/6/2014 6:34 AM, Edoardo Panfili wrote: Why localhost is not an alias for 127.0.0.1 ? grep localhost /etc/hosts I've seen some weird stuff on some systems. I have localhost/127.0.0.1 set up correctly on my machine and I see the same behavior as the OP. I'm pretty sure it is an interaction between the libpq host/hostaddr code and a .pgpass file. I suspect the OP has a .pgpass entry for localhost but not 127.0.0.1. -- Adrian Klaver adrian.kla...@aklaver.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] psql connection via localhost or 127.0.0.1
Il 06/11/14 16:00, John R Pierce ha scritto: On 11/6/2014 6:34 AM, Edoardo Panfili wrote: Why localhost is not an alias for 127.0.0.1 ? grep localhost /etc/hosts 127.0.0.1 localhost 127.0.1.1 host.host host # The following lines are desirable for IPv6 capable hosts ::1 localhost ip6-localhost ip6-loopback ff02::1 ip6-allnodes ff02::2 ip6-allrouters I've seen some weird stuff on some systems. 127.0.1.1 sounds a little bit strange form me but the file seems ok. and $ host localhost localhost has address 127.0.0.1 localhost has IPv6 address ::1 Edoardo -- 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] psql connection via localhost or 127.0.0.1
On 11/6/2014 7:36 AM, Edoardo Panfili wrote: grep localhost /etc/hosts 127.0.0.1 localhost 127.0.1.1 host.host host wah? whats the output of ... # ifconfig lo loLink encap:Local Loopback inet addr:127.0.0.1 Mask:255.0.0.0 inet6 addr: ::1/128 Scope:Host UP LOOPBACK RUNNING MTU:65536 Metric:1 RX packets:18367154 errors:0 dropped:0 overruns:0 frame:0 TX packets:18367154 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:0 RX bytes:23279985092 (21.6 GiB) TX bytes:23279985092 (21.6 GiB) note that net mask?the loopback interface is the entire 127.0.0.0/8 networkyou can't put a host at 127.0.1.x and expect it to work right. you should instead use one of the RFC1918 reserved subnets for a private network, within 10.0.0.0/8 or 172.16.0.0/12, or 192.168.0.0/16 (you can use these with any mask size you want, for instance, /24 is usually used with 192.168.x.y) -- john r pierce 37N 122W somewhere on the middle of the left coast -- 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] psql connection via localhost or 127.0.0.1
Il 06/11/14 16:08, Tom Lane ha scritto: Edoardo Panfili writes: I am using postgres 9.4beta3 (Debian jessie) this is my pg_hba.conf -- localallpostgrespeer localallallmd5 hostallall127.0.0.1/32md5 hostallall::1/128md5 -- but if I use psql to connect via tcp to the server (on the same machine) I can see two differente behaviours if I use "localhost" or "127.0.0.1" If I understand correctly the first two lines are useful only for Unix sockets and not for tcp-ip connection. Yeah. On modern Linux distros it's quite likely that "localhost" is resolving as IPv6 ::1, not 127.0.0.1, but in neither case would it match your "local" lines. In any case, your first connection attempt is definitely not connecting over a Unix socket because we never use SSL with Unix sockets. ok, thank you. I can think of two plausible theories: 1. The postmaster isn't actually using the pg_hba.conf you think it is. (Maybe you modified the file and forgot to do a "pg_ctl reload"?) another way on my test machine: /etc/init.d/postgresql stop /etc/init.d/postgresql start 2. You have a "service file" active that is capturing the server name "localhost" and redefining it to mean something other than the obvious meaning. http://www.postgresql.org/docs/9.3/interactive/libpq-pgservice.html A useful test would be to try "psql -h ::1" and see which way that behaves. If it does the same thing as "-h localhost" then we can eliminate the service-file theory. connection via ::1 $ psql -h ::1 -U postgres Inserisci la password per l'utente postgres: same as for 127.0.0.1 thank you Edoardo -- 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] psql connection via localhost or 127.0.0.1
Il 06/11/14 16:18, Adrian Klaver ha scritto: On 11/06/2014 07:00 AM, John R Pierce wrote: On 11/6/2014 6:34 AM, Edoardo Panfili wrote: Why localhost is not an alias for 127.0.0.1 ? grep localhost /etc/hosts I've seen some weird stuff on some systems. I have localhost/127.0.0.1 set up correctly on my machine and I see the same behavior as the OP. I'm pretty sure it is an interaction between the libpq host/hostaddr code and a .pgpass file. I suspect the OP has a .pgpass entry for localhost but not 127.0.0.1. -- .pgpass -- localhost:5432:*:postgres:postgres_pwd removing this file localhost behaves like 127.0.0.1 maybe that the debian installer creates this file during installation (is useful but I didn't know this file). this explains the situation, thank you again to all Edoardo -- 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] psql connection via localhost or 127.0.0.1
On 11/06/2014 07:47 AM, John R Pierce wrote: On 11/6/2014 7:36 AM, Edoardo Panfili wrote: grep localhost /etc/hosts 127.0.0.1 localhost 127.0.1.1 host.host host wah? https://lists.debian.org/debian-devel/2013/07/msg00809.html -- Adrian Klaver adrian.kla...@aklaver.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] psql connection via localhost or 127.0.0.1
Il 06/11/14 16:47, John R Pierce ha scritto: On 11/6/2014 7:36 AM, Edoardo Panfili wrote: grep localhost /etc/hosts 127.0.0.1 localhost 127.0.1.1 host.host host wah? whats the output of ... # ifconfig lo loLink encap:Local Loopback inet addr:127.0.0.1 Mask:255.0.0.0 inet6 addr: ::1/128 Scope:Host UP LOOPBACK RUNNING MTU:65536 Metric:1 RX packets:18367154 errors:0 dropped:0 overruns:0 frame:0 TX packets:18367154 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:0 RX bytes:23279985092 (21.6 GiB) TX bytes:23279985092 (21.6 GiB) # ifconfig lo loLink encap:Local Loopback inet addr:127.0.0.1 Mask:255.0.0.0 inet6 addr: ::1/128 Scope:Host UP LOOPBACK RUNNING MTU:65536 Metric:1 RX packets:3306 errors:0 dropped:0 overruns:0 frame:0 TX packets:3306 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:0 RX bytes:2544659 (2.4 MiB) TX bytes:2544659 (2.4 MiB) note that net mask?the loopback interface is the entire 127.0.0.0/8 networkyou can't put a host at 127.0.1.x and expect it to work right. you should instead use one of the RFC1918 reserved subnets for a private network, within 10.0.0.0/8 or 172.16.0.0/12, or 192.168.0.0/16 (you can use these with any mask size you want, for instance, /24 is usually used with 192.168.x.y) I can't figure why 127.0.1.1 is there (I will remove it) is an (almost) new installation on a virtual machine, Edoardo -- 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] psql connection via localhost or 127.0.0.1
On 11/06/2014 07:57 AM, Edoardo Panfili wrote: Il 06/11/14 16:47, John R Pierce ha scritto: On 11/6/2014 7:36 AM, Edoardo Panfili wrote: grep localhost /etc/hosts 127.0.0.1 localhost 127.0.1.1 host.host host wah? whats the output of ... # ifconfig lo loLink encap:Local Loopback inet addr:127.0.0.1 Mask:255.0.0.0 inet6 addr: ::1/128 Scope:Host UP LOOPBACK RUNNING MTU:65536 Metric:1 RX packets:18367154 errors:0 dropped:0 overruns:0 frame:0 TX packets:18367154 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:0 RX bytes:23279985092 (21.6 GiB) TX bytes:23279985092 (21.6 GiB) # ifconfig lo loLink encap:Local Loopback inet addr:127.0.0.1 Mask:255.0.0.0 inet6 addr: ::1/128 Scope:Host UP LOOPBACK RUNNING MTU:65536 Metric:1 RX packets:3306 errors:0 dropped:0 overruns:0 frame:0 TX packets:3306 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:0 RX bytes:2544659 (2.4 MiB) TX bytes:2544659 (2.4 MiB) note that net mask?the loopback interface is the entire 127.0.0.0/8 networkyou can't put a host at 127.0.1.x and expect it to work right. you should instead use one of the RFC1918 reserved subnets for a private network, within 10.0.0.0/8 or 172.16.0.0/12, or 192.168.0.0/16 (you can use these with any mask size you want, for instance, /24 is usually used with 192.168.x.y) I can't figure why 127.0.1.1 is there (I will remove it) is an (almost) new installation on a virtual machine, See the link I sent to John. Edoardo -- Adrian Klaver adrian.kla...@aklaver.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] psql connection via localhost or 127.0.0.1
Il 06/11/14 16:54, Adrian Klaver ha scritto: On 11/06/2014 07:47 AM, John R Pierce wrote: On 11/6/2014 7:36 AM, Edoardo Panfili wrote: grep localhost /etc/hosts 127.0.0.1 localhost 127.0.1.1 host.host host wah? https://lists.debian.org/debian-devel/2013/07/msg00809.html No dubt regarding the good reasons of the debian (and postgres) developers. All works fine, the problem is that I search the wrong thing in the documentation. "Connecting to a Database" section of http://www.postgresql.org/docs/9.4/static/app-psql.html contains the explanation for my situation. again, thank you Edoardo -- 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] psql connection via localhost or 127.0.0.1
Hi John. On Thu, Nov 6, 2014 at 4:47 PM, John R Pierce wrote: note that net mask?the loopback interface is the entire 127.0.0.0/8 > networkyou can't put a host at 127.0.1.x and expect it to work right. > You can, and it works, it just connects to yourself. Even without names, I have used connections from 127.100.100.100:1 to 127.111.111.111:11 and 127.222.222.222:2 to 127.233.233.233:2 when testing some network code, they all bind to loopback and a single packet trace is easier to follow than just changing the ports. Francisco Olarte.
Re: [GENERAL] phppgadmin
tell me good guidelines for phppgadmin..? On Thu, Nov 6, 2014 at 8:54 PM, Ramesh T wrote: > hello, > any guidelines is their how to use these tool..? > not home site.. > http://phppgadmin.kattare.com/phppgadmin/...:) >
[GENERAL] phppgadmin
hello, any guidelines is their how to use these tool..? not home site.. http://phppgadmin.kattare.com/phppgadmin/...:)
Re: [GENERAL] DATA corruption after promoting slave to master
Hey Shaun, Hope you are keeping well. Will you please help me with this un-common behaviour of postgres. When executing the query (1) it takes around 62 seconds. However when disabling the index scan the same query (2) executes in around 2 seconds. Is there any reason why the query planner prefers (1)? *--- (1) * *---* explain analyze select * from t1 inner join t2 on t2.orderid = t1.orderid where t1.currentstatus IN (E'Active') and (t1.parentkey = E'1_2_3' OR t1.parentkey LIKE E'1\\_2\\_3\\_%') order by t1.orderid limit 500 offset 2000; QUERY PLAN -- Limit (cost=61151.75..74956.58 rows=500 width=316) (actual time=48066.053..62264.179 rows=500 loops=1) -> Merge Join (cost=5932.46..1212391.22 rows=43697 width=316) (actual time=417.461..62262.616 rows=2500 loops=1) Merge Cond: (t2.orderid = t1.orderid) -> Index Scan using t1_orderid_creationtime_idx on t1 (cost=0.43..1181104.36 rows=9879754 width=158) (actual time=0.021..60830.724 rows=2416614 loops=1 ) -> Sort (cost=5932.02..6041.26 rows=43697 width=158) (actual time=221.333..225.101 rows=2500 loops=1) Sort Key: t1.orderid Sort Method: quicksort Memory: 3573kB -> Bitmap Heap Scan on t1 (cost=59.85..2564.02 rows=43697 width=158) (actual time=11.443..210.783 rows=12005 loops=1) Recheck Cond: ((parentkey = '1_2_3'::text) OR (parentkey ~~ '1\_2\_3\_%'::text)) Filter: ((currentstatus = 'Active'::text) AND ((parentkey = '1_2_3'::text) OR (parentkey ~~ '1\_2\_3\_%'::text))) Rows Removed by Filter: 915 -> BitmapOr (cost=59.85..59.85 rows=649 width=0) (actual time=7.220..7.220 rows=0 loops=1) -> Bitmap Index Scan on t1_parentkey_idx (cost=0.00..33.43 rows=649 width=0) (actual time=5.625..5.625 rows=10646 loops=1) Index Cond: (parentkey = '1_2_3'::text) -> Bitmap Index Scan on t1_parentkey_idx (cost=0.00..4.57 rows=1 width=0) (actual time=1.592..1.592 rows=2445 loops=1) Index Cond: ((parentkey ~>=~ '1_2_3_'::text) AND (parentkey ~<~ '1_2_3`'::text)) Total runtime: 62265.568 ms (17 rows) *--- (2) * *---* begin ; set enable_indexscan=false; explain analyze select * from t1 inner join t2 on t2.orderid = t1.orderid where t1.currentstatus IN (E'Active') and (t1.parentkey = E'1_2_3' OR t1.parentkey LIKE E'1\\_2\\_3\\_%') order by t1.orderid limit 500 offset 2000; QUERY PLAN Limit (cost=316628.97..316630.22 rows=500 width=316) (actual time=1182.978..1183.196 rows=500 loops=1) -> Sort (cost=316623.97..316741.70 rows=47094 width=316) (actual time=1182.409..1182.935 rows=2500 loops=1) Sort Key: t1.orderid Sort Method: top-N heapsort Memory: 1482kB -> Nested Loop (cost=64.13..313730.58 rows=47094 width=316) (actual time=17.099..1122.316 rows=12920 loops=1) -> Bitmap Heap Scan on t1 (cost=61.54..2564.10 rows=47094 width=158) (actual time=17.001..501.400 rows=12920 loops=1) Recheck Cond: ((parentkey = '1_2_3'::text) OR (parentkey ~~ '1\_2\_3\_%'::text)) Filter: ((parentkey = '1_2_3'::text) OR (parentkey ~~ '1\_2\_3\_%'::text)) -> BitmapOr (cost=61.54..61.54 rows=649 width=0) (actual time=11.575..11.575 rows=0 loops=1) -> Bitmap Index Scan on t1_parentkey_idx (cost=0.00..33.43 rows=649 width=0) (actual time=9.221..9.221 rows=10646 loops=1) Index Cond: (parentkey = '1_2_3'::text) -> Bitmap Index Scan on t1_parentkey_idx (cost=0.00..4.57 rows=1 width=0) (actual time=2.352..2.352 rows=2445 loops=1) Index Cond: ((parentkey ~>=~ '1_2_3_'::text) AND (parentkey ~<~ '1_2_3`'::text)) -> Bitmap Heap Scan on t1 (cost=2.58..6.60 rows=1 width=158) (actual time=0.043..0.043 rows=1 loops=12920) Recheck Cond: (orderid = t1.orderid) -> Bitmap Index Scan on t1_orderid_creationtime_idx (cost=0.00..2.58 rows=1 width=0) (actual time=0.038..0.038 rows=1 loops=12920) Index Cond: (orderid = t1.orderid) Total runtime: 1184.140 ms (18 rows) Looking forward to your help. On Thu, Jun 26, 2014 at 11:07 PM, Karthik Iyer
[GENERAL] Postgres char type inconsistency
Looking at the behaviour of char & varchar types, there seems to be an issue.
Can anyone explain this behaviour? Is there a bug of some sort?
According to the docs
http://www.postgresql.org/docs/9.3/static/datatype-character.html)(:
" If the string to be stored is shorter than the declared length, values of
type character will be space-padded; values of type character varying will
simply store the shorter string."
Yet chars are not being padded, in fact they lose trailing spaces which are
retained by varchars. They also return length()'s less than the defined
length... which should not be the case for a padded string as defined in the
documentation.
fish=# create table test(var3 varchar(3),cha3 char(3));
CREATE TABLE
fish=# insert into test values('1','1');
INSERT 0 1
fish=# insert into test values('2 ','2 '); -- one space
INSERT 0 1
fish=# insert into test values('3 ','3 '); --two spaces
INSERT 0 1
fish=# select var3||':' as var3, cha3||':' as char3 from test;
var3 | char3
--+---
1: | 1:
2 : | 2:
3 : | 3:
(3 rows)
test=# select length(var3) as v_lgth, length(cha3) as c_length from test;
v_lgth | c_length
+--
1 |1
2 |1
3 |1
So, in summary, varchar stores whatever feed to it and keeps trailing spaces to
max length, char type will trim off trailing spaces, and stor a string shorter
than the specified length..
Brent Wood
Programme leader: Environmental Information Delivery
NIWA
DDI: +64 (4) 3860529
Brent Wood
Principal Technician - GIS and Spatial Data Management
Programme Leader - Environmental Information Delivery
+64-4-386-0529 | 301 Evans Bay Parade, Greta Point, Wellington |
www.niwa.co.nz
[NIWA]
Re: [GENERAL] phppgadmin
On 06/11/2014 15:24, Ramesh T wrote: > hello, > any guidelines is their how to use these tool..? > not home site.. > http://phppgadmin.kattare.com/phppgadmin/...:) Here's the home site: http://phppgadmin.sourceforge.net Lots of documentation available there. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Postgres char type inconsistency
On Tue, 4 Nov 2014 18:43:38 +
Brent Wood wrote:
>
> Looking at the behaviour of char & varchar types, there seems to be an issue.
> Can anyone explain this behaviour? Is there a bug of some sort?
>
> According to the docs
> http://www.postgresql.org/docs/9.3/static/datatype-character.html)(:
> " If the string to be stored is shorter than the declared length, values of
> type character will be space-padded; values of type character varying will
> simply store the shorter string."
>
> Yet chars are not being padded, in fact they lose trailing spaces which are
> retained by varchars. They also return length()'s less than the defined
> length... which should not be the case for a padded string as defined in the
> documentation.
>
> fish=# create table test(var3 varchar(3),cha3 char(3));
> CREATE TABLE
> fish=# insert into test values('1','1');
> INSERT 0 1
> fish=# insert into test values('2 ','2 '); -- one space
> INSERT 0 1
> fish=# insert into test values('3 ','3 '); --two spaces
> INSERT 0 1
> fish=# select var3||':' as var3, cha3||':' as char3 from test;
> var3 | char3
> --+---
> 1: | 1:
> 2 : | 2:
> 3 : | 3:
> (3 rows)
> test=# select length(var3) as v_lgth, length(cha3) as c_length from test;
> v_lgth | c_length
> +--
> 1 |1
> 2 |1
> 3 |1
>
> So, in summary, varchar stores whatever feed to it and keeps trailing spaces
> to max length, char type will trim off trailing spaces, and stor a string
> shorter than the specified length..
Your conclusion is wrong. The spaces _are_stored_. You've missed some
possibilities.
In all of your examples above, the || operator casts the char to varchar before
executing. The _cast_ from char to varchar is what trims the spaces.
You can see this visually with psql with something like this:
db=# select '3'::char(33);
bpchar
---
3
(1 row)
db=# select '3'::char(33)::varchar;
varchar
-
3
(1 row)
I seem to remember discussion about this actually being correct behavior per the
SQL standard, but I could be wrong on this count. Quite frankly, I don't see any
reason for anyone using char any more. If I had to guess, I would guess that
char
is in the standard because at the time of creation there were systems that could
heavily optimize access to fix-width fields, and that it's still in the standard
becuase nobody is sure how to clean cruft out of the standard.
--
Bill Moran
I need your help to succeed:
http://gamesbybill.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] Incomplete startup packet help needed
On Thu, 6 Nov 2014 13:40:14 +0100 Francisco Olarte wrote: > Hi Juan Antonio: > > On Tue, Nov 4, 2014 at 9:54 PM, memtec > wrote: > > > You can force this message executing: > > $ echo | nc SERVER_IP 5432 > > 2014-11-04 21:44:37 CET LOG: incomplete startup packet > > > > What do you need help on? PG uses a binary protocol, you sent it bad data > ( I think this will open connection, send a \n byte, shutdown it ) and it > complained in the first check ( I suppose it is due to the connection > expecting an startup packet with a given format, and it couldn't get it > because it's longer than 1 byte, everything is prety well documented, RTFM > ). FYI: It's very common to get these messages in your log file if you have something like nagios or other monitoring tools checking to see if postgres is up. This is because many of them just test to see if something is listening on the port, without opening a proper postgres connection (i.e. telnet localhost 5432). The just connect to the port then disconnect without sending any data of any kind. -- Jon Erdman (aka StuckMojo) PostgreSQL Zealot pgpgN7OK4rPmE.pgp Description: PGP signature
Re: [GENERAL] Postgres char type inconsistency
On 11/04/2014 10:43 AM, Brent Wood wrote:
Looking at the behaviour of char & varchar types, there seems to be an
issue. Can anyone explain this behaviour? Is there a bug of some sort?
According to the docs
http://www.postgresql.org/docs/9.3/static/datatype-character.html)(:
" If the string to be stored is shorter than the declared length, values
of type character will be space-padded; values of type character varying
will simply store the shorter string."
Yet chars are not being padded, in fact they lose trailing spaces which
are retained by varchars. They also return length()'s less than the
defined length... which should not be the case for a padded string as
defined in the documentation.
fish=# create table test(var3 varchar(3),cha3 char(3));
CREATE TABLE
fish=# insert into test values('1','1');
INSERT 0 1
fish=# insert into test values('2 ','2 '); -- one space
INSERT 0 1
fish=# insert into test values('3 ','3 '); --two spaces
INSERT 0 1
fish=# select var3||':' as var3, cha3||':' as char3 from test;
var3 | char3
--+---
1: | 1:
2 : | 2:
3 : | 3:
(3 rows)
test=# select length(var3) as v_lgth, length(cha3) as c_length from test;
v_lgth | c_length
+--
1 |1
2 |1
3 |1
So, in summary, varchar stores whatever feed to it and keeps trailing
spaces to max length, char type will trim off trailing spaces, and stor
a string shorter than the specified length..
I believe what you are seeing is explained here:
http://www.postgresql.org/docs/9.3/interactive/datatype-character.html
"Values of type character are physically padded with spaces to the
specified width n, and are stored and displayed that way. However, the
padding spaces are treated as semantically insignificant. Trailing
spaces are disregarded when comparing two values of type character, and
they will be removed when converting a character value to one of the
other string types. Note that trailing spaces are semantically
significant in character varying and text values, and when using pattern
matching, e.g. LIKE, regular expressions."
Brent Wood
Programme leader: Environmental Information Delivery
NIWA
DDI: +64 (4) 3860529
Brent Wood
Principal Technician - GIS and Spatial Data Management
Programme Leader - Environmental Information Delivery
+64-4-386-0529| 301 Evans Bay Parade, Greta Point, Wellington|
www.niwa.co.nz
NIWA
--
Adrian Klaver
adrian.kla...@aklaver.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] Tablespace limit feature
On 11/4/14, 2:10 PM, Alvaro Herrera wrote: Alejandro Carrillo escribió: > >But This feature is very necessary to control the amount space expensed by a postgresql user or tablespace. This feature could be used in PostgreSQL Sharing Hosting, see: Shared Database Hosting - PostgreSQL wiki Sure. Rub hits the road when it comes to actually implementing this, though -- the issue of enforcing the limit without creating a bottleneck to table extension is not trivial to overcome, as I recall. And index extension. And visibility map extension. And, and, and. It's definitely a non-trivial problem to solve, and unfortunately there are extremely few hosting environment providers active in the community to even ask for the kinds of features they need, which means this just doesn't get much attention. -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.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] Tablespace limit feature
* Jim Nasby (jim.na...@bluetreble.com) wrote: > On 11/4/14, 2:10 PM, Alvaro Herrera wrote: > >Alejandro Carrillo escribió: > >>> > >>>But This feature is very necessary to control the amount space expensed by > >>>a postgresql user or tablespace. This feature could be used in PostgreSQL > >>>Sharing Hosting, see: Shared Database Hosting - PostgreSQL wiki > >Sure. Rub hits the road when it comes to actually implementing this, > >though -- the issue of enforcing the limit without creating a bottleneck > >to table extension is not trivial to overcome, as I recall. > > And index extension. And visibility map extension. And, and, and. Indexes I could agree with. I'm not sure that the VM would be worthwhile to include explicitly; as its size is dictated directly by the number of pages in the heap, we might be able to include it implicitly. There are definitely big questions around how to track space used and how to figure out if we've reached the limit, in an efficient and correct way. > It's definitely a non-trivial problem to solve, and unfortunately there are > extremely few hosting environment providers active in the community to even > ask for the kinds of features they need, which means this just doesn't get > much attention. Agreed. There are also larger problems (ran into yet another case of "wait, roles aren't database-specific?!" just today..) than the quota issue for hosting providers. Thanks, Stephen signature.asc Description: Digital signature
[GENERAL] does pg_restore respect CLUSTER ON in the schema?
I have a database where several tables have been physically clustered on disk. Those clustering configuration parameters are stored in the schema (via ALTER TABLE mytable CLUSTER ON myindex). My question is, when i restore a backup of this database using pg_restore, is it restored with that physical clustering, or without? i.e. do i need to run a cluster command immediately after the restore, or is it already done? Thanks, Seth -- 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] does pg_restore respect CLUSTER ON in the schema?
On 11/6/2014 3:03 PM, Seth Pollack wrote: I have a database where several tables have been physically clustered on disk. Those clustering configuration parameters are stored in the schema (via ALTER TABLE mytable CLUSTER ON myindex). My question is, when i restore a backup of this database using pg_restore, is it restored with that physical clustering, or without? i.e. do i need to run a cluster command immediately after the restore, or is it already done? when you restore, the data is inserted in new empty tables via COPY, which will write the records in whatever order they were dumped in, which is going to be the order they are in the original table at that point in time.. any waste space will be packed out, but the data will only be as clustered as it was when it was at the time of the dump. -- john r pierce 37N 122W somewhere on the middle of the left coast -- 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] does pg_restore respect CLUSTER ON in the schema?
Seth Pollack wrote > I have a database where several tables have been physically clustered on > disk. Those clustering configuration parameters are stored in the schema > (via ALTER TABLE mytable CLUSTER ON myindex). > > My question is, when i restore a backup of this database using pg_restore, > is it restored with that physical clustering, or without? i.e. do i need > to run a cluster command immediately after the restore, or is it already > done? Some assuming here... The COPY command that pg_dump uses will use a sequential scan over the table and return tuples as it encounters them. Upon restore the COPY will see those records in the source file in the same order and should insert them onto disk without shuffling. A sequential scan should cause the copy output to remain in cluster order for records that were previously clustered (keeping in mind MVCC mechanics) while any newer ones will likely be in actual insert order. The restored database should have a similar layout and thus the need to perform clustering would be dependent upon when you last ran cluster on the source database. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/does-pg-restore-respect-CLUSTER-ON-in-the-schema-tp5826007p5826009.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
