Re: [GENERAL] Comparing txid_current() to xmin
On Wed, Nov 7, 2012 at 10:21 AM, Andres Freund and...@anarazel.de wrote: On Tue, Nov 06, 2012 at 02:55:40PM -0800, Mike Lewis wrote: I am trying to make a trigger that updates a row once and only once per transaction (even if this trigger gets fired multiple times). The general idea is that for a user we have a version number. When we modify the user's data, the version number is incremented then set on the object. We only need to increment the version number once. I am thinking about doing something like: update user set version=version+1 where txid_current() != xmin and user_id = 352395; So I guess my questions are: How dirty is this? Will I run into issues? It won't work in the presenence of subtransactions and is a bit more complicated if you inserted the row in the same transaction. This can be solved by storing txid_current() into row and using that in comparision instead xmin/xmax. -- marko -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Running out of memory while making a join
Hi, The following SQL join command runs the PostgreSQL server out of memory. The server runs on a box with Red Hat Enterprise Linux Server release 6.3 (Santiago) and PostgreSQL 8.3.21. select wm_nfsp from 5611_isarq.wm_nfsp left join 5611_nfarq.nfe on wm_nfsp.tpdoc = 7 where 1 = 1 and wm_nfsp.codpre = 2866 and wm_nfsp.compet = '10/2012'; Explain result: Nested Loop Left Join (cost=7356.61..48466.46 rows=346312 width=32) Join Filter: (wm_nfsp.tpdoc = 7) - Bitmap Heap Scan on wm_nfsp (cost=11.65..1162.37 rows=11 width=34) Recheck Cond: (codpre = 2866) Filter: ((compet)::text = '10/2012'::text) - Bitmap Index Scan on idx_wm_nfsp_codpre (cost=0.00..11.64 rows=714 width=0) Index Cond: (codpre = 2866) - Materialize (cost=7344.96..8959.47 rows=161451 width=0) - Seq Scan on nfe (cost=0.00..7183.51 rows=161451 width=0) (9 rows) Once the query starts the top command starts showing an increase of memory use and minutes later vmstat shows the server performing a lot swapping and almost stops everything until the PID is killed. top command output while join running on PID 29787: top - 11:26:41 up 10 days, 6:30, 3 users, load average: 131.05, 74.55, 32.33 Tasks: 499 total, 3 running, 496 sleeping, 0 stopped, 0 zombie Cpu(s): 10.8%us, 12.7%sy, 0.0%ni, 0.0%id, 75.2%wa, 0.0%hi, 1.3%si, 0.0%st Mem: 32876756k total, 32677176k used, 199580k free, 4804k buffers Swap: 16777208k total, 6248980k used, 10528228k free, 485188k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 29787 postgres 20 0 35.5g 29g 98m D 30.8 92.9 15:07.23 postgres: ipm Fisca 98 root 20 0 000 D 13.7 0.0 23:46.72 [kswapd0] 31496 postgres 20 0 546m 20m 18m D 4.9 0.1 0:00.29 postgres: ipm Fisca 29024 postgres 20 0 547m 52m 50m D 4.0 0.2 0:03.95 postgres: ipm Fisca vmstat output showing the server is performing a lot of swapping: Tue Nov 6 11:27:06 BRST 2012 procs ---memory-- ---swap-- -io --system-- -cpu- r b swpd free buff cache si sobibo in cs us sy id wa st 1 143 6653476 199076 6368 47635601 694 64620 8 3 88 1 0 1 79 6700576 204104 6212 453808 64 9438 99833 9503 14213 9477 10 9 0 80 0 5 74 6813252 199144 5196 4888727 22540 102592 22704 13770 8762 9 10 2 80 0 2 58 6855596 199332 4456 462592 70 8474 133870 8509 13527 9242 8 10 0 82 0 3 90 6907264 199096 5544 472112 102 10403 102617 11136 12764 8497 7 9 0 84 0 Running the same command on a PostgreSQL 9.0.0 server results in an OUT OF MEMORY message stopping the backend but preventing the entire server to stop. The development team is going to change the SQL join command but my concern is with other similar SQL commands not yet identified. Is there a way to make PostgreSQL 8.3.21 server stop memory bound backends as PostgreSQL 9.0.0 does? -- Reimer 47-3347-1724 47-9183-0547 msn: carlos.rei...@opendb.com.br
Re: [GENERAL] Comparing txid_current() to xmin
On Thu, Nov 08, 2012 at 11:47:37AM +0200, Marko Kreen wrote: On Wed, Nov 7, 2012 at 10:21 AM, Andres Freund and...@anarazel.de wrote: On Tue, Nov 06, 2012 at 02:55:40PM -0800, Mike Lewis wrote: I am trying to make a trigger that updates a row once and only once per transaction (even if this trigger gets fired multiple times). The general idea is that for a user we have a version number. When we modify the user's data, the version number is incremented then set on the object. We only need to increment the version number once. I am thinking about doing something like: update user set version=version+1 where txid_current() != xmin and user_id = 352395; So I guess my questions are: How dirty is this? Will I run into issues? It won't work in the presenence of subtransactions and is a bit more complicated if you inserted the row in the same transaction. This can be solved by storing txid_current() into row and using that in comparision instead xmin/xmax. If there is sufficient demand for this it should be easy enough to add a function that checks for stuff like this using the information already available in the backends memory. The hardest part seems to be to find a good name... It would basically only need to wrap TransactionIdIsCurrentTransactionId. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Running out of memory while making a join
On 11/08/2012 06:20 PM, Carlos Henrique Reimer wrote: Is there a way to make PostgreSQL 8.3.21 server stop memory bound backends as PostgreSQL 9.0.0 does? Are there any triggers on the table? What's the setting for work_mem? -- Craig Ringer -- 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] Problem with streaming replication over SSL
I wrote: Magnus Hagander wrote: I have streaming replication configured over SSL, and there seems to be a problem with SSL renegotiation. [...] After that, streaming replication reconnects and resumes working. Is this an oversight in the replication protocol, or is this working as designed? It can hardly be the CVE-2009-3555 renegotiation problem. Both machines have OpenSSL 1.0.0, and RFC 5746 was implemented in 0.9.8m. It would be worth trying with ssl_renegotiation=0 to see if the problem goes away. I tried, and that makes the problem go away. This is to be expected of course, because no renegotiation will take place with that setting. But I'll try to test if normal connections have the problem too. That would be a useful datapoint. All settings around this *should* happen at a lower layer than the difference between a replication connection and a regular one, but it would be good to confir mit. I tried, and a normal data connection does not have the problem. I transferred more than 0.5 GB of data (at which point renegotiation should take place), and there was no error. Does it make sense to try and take a stack trace of the problem, on primary or standby? FWIW, I collected a stack trace: #0 libpqrcv_receive (timeout=100, type=0x7fff0e1d9d2f w\002, buffer=0x7fff0e1d9d20, len=0x7fff0e1d9d28) at libpqwalreceiver.c:366 #1 0x00601797 in WalReceiverMain () at walreceiver.c:311 #2 0x004ae1a1 in AuxiliaryProcessMain (argc=2, argv=0x7fff0e1d9dc0) at bootstrap.c:433 #3 0x005ee0c3 in StartChildProcess (type=WalReceiverProcess) at postmaster.c:4504 #4 0x005f13c1 in sigusr1_handler (postgres_signal_arg=value optimized out) at postmaster.c:4300 #5 signal handler called #6 0x0037934de2d3 in __select_nocancel () from /lib64/libc.so.6 #7 0x005ef54a in ServerLoop () at postmaster.c:1415 #8 0x005f21de in PostmasterMain (argc=value optimized out, argv=value optimized out) at postmaster.c:1116 #9 0x00594398 in main (argc=5, argv=0x1596bf0) at main.c:199 The only thing that sticks out to me is that walreceiver is running inside a signal handler -- could that cause a problem with OpenSSL? Yours, Laurenz Albe -- 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] Running out of memory while making a join
Hi Craig,
work_mem is defined with 10MB and yes, there are triggers defined on both
tables:
FiscalWeb=# \d 5611_isarq.wm_nfsp
Table 5611_isarq.wm_nfsp
Column | Type | Modifiers
---+---+---
tpdoc | smallint | not null
numero| character varying(18) | not null
codpre| bigint| not null
compet| character varying(7) |
dtemis| date |
vlrtot| numeric(15,2) |
situnf| character(1) |
parcela | smallint |
obs | text |
sequencia | bigint| not null
cnpjtom | numeric(14,0) |
Indexes:
wm_nfsp_pkey PRIMARY KEY, btree (tpdoc, numero, codpre, sequencia)
idx_wm_nfsp_codpre btree (codpre)
idx_wm_nfsp_dtemis btree (dtemis)
Triggers:
tg_wm_nfsp BEFORE INSERT OR DELETE OR UPDATE ON 5611_isarq.wm_nfsp
FOR EACH ROW EXECUTE PROCEDURE fun_isarq.wm_nfsp('5611', 'isarq')
FiscalWeb=# \d 5611_nfarq.nfe
Table 5611_nfarq.nfe
Column | Type | Modifiers
---+---+
cadastro | bigint| not null
serie | smallint | not null
numero| integer | not null
codauten | character varying(14) | not null
dataemis | date | not null
horaemis | character varying(8) | not null
vlrtot| numeric(15,2) | not null
situacao | smallint | not null
observ| text |
datacanc | date |
obscanc | text |
tipotom | character varying(30) | not null
nf_html | text |
nf_assinada | text |
nf_protocolada| text |
vlrtot_desc | numeric(15,2) |
vlrtot_ir | numeric(15,2) |
vlrtot_inss | numeric(15,2) |
vlrtot_contsocial | numeric(15,2) |
tipo | smallint | not null default 1
vlrtot_rps| numeric(15,2) |
vlrtot_pis| numeric(15,2) |
vlrtot_cofins | numeric(15,2) |
lote_rps | integer | default 0
numero_rps| integer | default 0
cnpjtom | bigint|
cpfcnpjcanc | bigint|
tomadorsemdoc | integer |
Indexes:
pk_nfe PRIMARY KEY, btree (cadastro, serie, numero)
uni_codauten UNIQUE, btree (codauten)
Foreign-key constraints:
nfe_serie_fkey FOREIGN KEY (serie) REFERENCES
5611_nfarq.serie(codigo) ON UPDATE RESTRICT ON DELETE RESTRICT
Disabled triggers:
tg_nfe BEFORE INSERT OR DELETE OR UPDATE ON 5611_nfarq.nfe FOR EACH
ROW EXECUTE PROCEDURE fun_nfarq.nfe('5611', 'NFARQ')
FiscalWeb=#
On Thu, Nov 8, 2012 at 10:50 AM, Craig Ringer cr...@2ndquadrant.com wrote:
On 11/08/2012 06:20 PM, Carlos Henrique Reimer wrote:
Is there a way to make PostgreSQL 8.3.21 server stop memory bound
backends as PostgreSQL 9.0.0 does?
Are there any triggers on the table?
What's the setting for work_mem?
--
Craig Ringer
--
Reimer
47-3347-1724 47-9183-0547 msn: carlos.rei...@opendb.com.br
Re: [GENERAL] How is execution plan cost calculated for index scan
On Wed, Nov 7, 2012 at 11:17 PM, 高健 luckyjack...@gmail.com wrote: Hi all: I want to see the explain plan for a simple query. My question is : How is the cost calculated? The cost parameter is: random_page_cost= 4 seq_page_cost = 1 cpu_tuple_cost =0.01 cpu_operator_cost =0.0025 The cost is estimates as 2*random_page_cost + cpu_tuple_cost + cpu_index_tuple_cost + 100* cpu_operator_cost. I determined this by changing each cost parameter and running explain, to see how much each one changed the cost estimate (after verifying the overall plan did not change). I was surprised the multiplier for cpu_operator_cost was that high. The two random_page_costs are one for the index leaf page and one for the table page. Higher pages in the index are assumed to be cached and thus not charged for IO. ... Firstly, database need to search for 9 index pages by sequential to find the index entry. For each index page in memory, every “index tuple” need to be scanned. That is not how indexes are traversed. Cheers, Jeff -- 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] How is execution plan cost calculated for index scan
=?UTF-8?B?6auY5YGl?= luckyjack...@gmail.com writes: I want to see the explain plan for a simple query. My question is : How is the cost calculated? In the case you're looking at, it's basically one random index page fetch plus one random heap page fetch (hence 8.0), plus assorted CPU costs making up the other 0.27 cost units. The argument for charging only for the index leaf-page fetch, and not upper levels of the index btree, is basically that all but the leaf level are likely to be in cache. This is pretty handwavy I know, but the costs seem to come out reasonably in line with reality that way. The result returned for path-path.total_cost is86698968.And 86698968/1024/1024 = 82.68258 . If devided by 10 , is near 8.27. but this is still a little odd. Your debugger isn't doing you any favors ... that field is a double. In the above case,can I say that the cost formula for index scan is in-- the cost_index function ? cost_index is only responsible for the heap-access part of the charges. The index-access part is in btcostestimate and genericcostestimate in utils/adt/selfuncs.c. 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] Use order by clause, got index scan involved
On Wed, Nov 7, 2012 at 11:41 PM, 高健 luckyjack...@gmail.com wrote: Hi all: What confused me is that: When I select data using order by clause, I got the following execution plan: postgres=# set session enable_indexscan=true; SET postgres=# explain SELECT * FROM pg_proc ORDER BY oid; QUERY PLAN Index Scan using pg_proc_oid_index on pg_proc (cost=0.00..321.60 rows=2490 width=552) You should probably use sample cases much larger than this when trying to understand the planner. With queries this small, it almost doesn't matter what plan is chosen. (1 row) postgres=# My Question is : If I want to find record using the where clause which hold the id column, the index scan might be used. But I just want to get all the records on sorted output format, Why index scan can be used here? I can’t imagine that: Step 1 Index is read into memory, then for each tuple in it, Step 2 Then we got the address of related data block, and then access the data block . Step 2 will be repeated for many times. I think it is not efficient. But step 2 will repeatedly find the block it is visiting to already be in memory, so it is efficient. Maybe the database system is clever enough to accumulate data access for same physical page, and reduce the times of physical page acess ? There is a bitmap scan which does that, but such a scan can't be used to fulfill a sort, because it doesn't return the rows in index order. What reduces the cost here is the various levels of caching implemented by the file system, the memory system, and the CPU. PG uses effective_cache_size to try to account for these effects, although I admit I don't quite understand what exactly it is doing in this case. I thought that setting effective_cache_size to absurdly low values would make the index scan cost estimate go up a lot, but it only made it go up a little. Cheers, Jeff
Re: [GENERAL] Does PostgreSQL have complete functional test cases?
Hi, Pavel, Bruce, Thanks for the explanation! I have another question regarding the regress test suite. What does the test result mean to the users/administrators? Are they the basic functions that have to be supported by PG server? Or, they are just some benchmarks (failure is ok?) Under certain configuration, I ran the test suite and got several failure (see the report below). What does it mean to me? The test results show that regex, create_index, inherit, etc, failed (even select failed). But I still don't know whether these are severe problems to my PG server. (p.s., in the default configuration, all the test suites are passed.) Best, Tianyin === test tablespace ... ok test boolean ... ok test char ... ok test name ... ok test varchar ... ok test text ... ok test int2 ... ok test int4 ... ok test int8 ... ok test oid ... ok test float4 ... ok test float8 ... ok test bit ... ok test numeric ... ok test txid ... ok test uuid ... ok test enum ... ok test money... ok test rangetypes ... ok test strings ... ok test numerology ... ok test point... ok test lseg ... ok test box ... ok test path ... ok test polygon ... ok test circle ... ok test date ... ok test time ... ok test timetz ... ok test timestamp... ok test timestamptz ... ok test interval ... ok test abstime ... ok test reltime ... ok test tinterval... ok test inet ... ok test macaddr ... ok test tstypes ... ok test comments ... ok test geometry ... ok test horology ... ok test regex... FAILED test oidjoins ... ok test type_sanity ... ok test opr_sanity ... ok test insert ... ok test create_function_1... ok test create_type ... ok test create_table ... ok test create_function_2... ok test create_function_3... ok test copy ... ok test copyselect ... ok test create_misc ... ok test create_operator ... ok test create_index ... FAILED test create_view ... ok test create_aggregate ... ok test create_cast ... ok test constraints ... ok test triggers ... ok test inherit ... FAILED test create_table_like... ok test typed_table ... ok test vacuum ... ok test drop_if_exists ... ok test sanity_check ... ok test errors ... ok test select ... FAILED test select_into ... ok test select_distinct ... ok test select_distinct_on ... ok test select_implicit ... ok test select_having... ok test subselect... ok test union... ok test case ... ok test join ... FAILED test aggregates ... FAILED test transactions ... ok test random ... ok test portals ... ok test arrays ... FAILED test btree_index ... ok test hash_index ... ok test update ... ok test delete ... ok test namespace... ok test prepared_xacts ... ok test privileges ... ok test security_label ... ok test collate ... ok test misc ... ok test rules... ok test select_views ... ok test portals_p2 ... ok test foreign_key ... ok test cluster ... ok test dependency ... ok test guc ... ok test bitmapops... ok test combocid ... ok test tsearch ... ok test tsdicts ... ok test foreign_data ... ok test window ... ok test xmlmap ... ok test functional_deps ... ok test advisory_lock... ok test json ... ok test plancache... ok test limit... ok test plpgsql ... ok test copy2... ok
Re: [GENERAL] Does PostgreSQL have complete functional test cases?
Hello 2012/11/8 Tianyin Xu t...@cs.ucsd.edu: Hi, Pavel, Bruce, Thanks for the explanation! I have another question regarding the regress test suite. What does the test result mean to the users/administrators? Are they the basic functions that have to be supported by PG server? Or, they are just some benchmarks (failure is ok?) no, there must not be failure!- it is not benchmark Under certain configuration, I ran the test suite and got several failure (see the report below). What does it mean to me? The test results show that regex, create_index, inherit, etc, failed (even select failed). But I still don't know whether these are severe problems to my PG server. probably you disable some important internal functionality - this usually means critical problem, because some documented feature is broken look to diff file src/test/regress/regression.diffs - and there you can see, what doesn't work Regards Pavel Stehule (p.s., in the default configuration, all the test suites are passed.) Best, Tianyin === test tablespace ... ok test boolean ... ok test char ... ok test name ... ok test varchar ... ok test text ... ok test int2 ... ok test int4 ... ok test int8 ... ok test oid ... ok test float4 ... ok test float8 ... ok test bit ... ok test numeric ... ok test txid ... ok test uuid ... ok test enum ... ok test money... ok test rangetypes ... ok test strings ... ok test numerology ... ok test point... ok test lseg ... ok test box ... ok test path ... ok test polygon ... ok test circle ... ok test date ... ok test time ... ok test timetz ... ok test timestamp... ok test timestamptz ... ok test interval ... ok test abstime ... ok test reltime ... ok test tinterval... ok test inet ... ok test macaddr ... ok test tstypes ... ok test comments ... ok test geometry ... ok test horology ... ok test regex... FAILED test oidjoins ... ok test type_sanity ... ok test opr_sanity ... ok test insert ... ok test create_function_1... ok test create_type ... ok test create_table ... ok test create_function_2... ok test create_function_3... ok test copy ... ok test copyselect ... ok test create_misc ... ok test create_operator ... ok test create_index ... FAILED test create_view ... ok test create_aggregate ... ok test create_cast ... ok test constraints ... ok test triggers ... ok test inherit ... FAILED test create_table_like... ok test typed_table ... ok test vacuum ... ok test drop_if_exists ... ok test sanity_check ... ok test errors ... ok test select ... FAILED test select_into ... ok test select_distinct ... ok test select_distinct_on ... ok test select_implicit ... ok test select_having... ok test subselect... ok test union... ok test case ... ok test join ... FAILED test aggregates ... FAILED test transactions ... ok test random ... ok test portals ... ok test arrays ... FAILED test btree_index ... ok test hash_index ... ok test update ... ok test delete ... ok test namespace... ok test prepared_xacts ... ok test privileges ... ok test security_label ... ok test collate ... ok test misc ... ok test rules... ok test select_views ... ok test portals_p2 ... ok test foreign_key ... ok test cluster ... ok test dependency ... ok test guc ... ok test bitmapops... ok
Re: [GENERAL] Does PostgreSQL have complete functional test cases?
On Thu, Nov 8, 2012 at 10:47 AM, Tianyin Xu t...@cs.ucsd.edu wrote: Hi, Pavel, Bruce, Thanks for the explanation! I have another question regarding the regress test suite. What does the test result mean to the users/administrators? Are they the basic functions that have to be supported by PG server? Or, they are just some benchmarks (failure is ok?) Under certain configuration, I ran the test suite and got several failure (see the report below). What does it mean to me? The test results show that regex, create_index, inherit, etc, failed (even select failed). But I still don't know whether these are severe problems to my PG server. You have to check the individual log files about what exactly the differences are. Sometimes the failures are for spurious reasons, for example the regression test was assuming rows would be returned in one order, but they were returned in a different order instead, yet the order is not meaningful to the correctness. If you build with a non-default block size, for example, you get many of these types of failures. Verifying that they are all acceptable is tedious, and I don't know of an easy way to do it. Cheers, Jeff -- 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 PostgreSQL have complete functional test cases?
On 2012-11-08 10:47:18 -0800, Tianyin Xu wrote: (p.s., in the default configuration, all the test suites are passed.) What did you change? Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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 PostgreSQL have complete functional test cases?
Thanks a lot, Pavel, Jeff, Andres! I just changed the configuration file, postgresql.conf. Using the default one, all the regress tests are passed (so it should not be the block size?). But when I changed something, quite a number of tests are failed. I looked at the regression.diffs file, but I don't understand what it said. For example, I have a bunch of things as follows: *** /home/tianyin/source_code/postgresql-9.2.1/src/test/regress/expected/select.out 2012-09-19 14:47:58.0 -0700 --- /home/tianyin/source_code/postgresql-9.2.1/src/test/regress/results/select.out 2012-11-08 11:02:54.30883 -0800 *** *** 219,234 SELECT onek2.* FROM onek2 WHERE onek2.unique1 10; unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 -+-+-+--+-++-+--+-+---+--+-+--+--+--+- !0 | 998 | 0 |0 | 0 | 0 | 0 |0 | 0 | 0 |0 | 0 |1 | AA | KMBAAA | xx 1 | 214 | 1 |1 | 1 | 1 | 1 |1 | 1 | 1 |1 | 2 |3 | BA | GI | xx 2 | 326 | 0 |2 | 2 | 2 | 2 |2 | 2 | 2 |2 | 4 |5 | CA | OM | xx 3 | 431 | 1 |3 | 3 | 3 | 3 |3 | 3 | 3 |3 | 6 |7 | DA | PQ | xx -4 | 833 | 0 |0 | 4 | 4 | 4 |4 | 4 | 4 |4 | 8 |9 | EA | BGBAAA | xx 5 | 541 | 1 |1 | 5 | 5 | 5 |5 | 5 | 5 |5 | 10 | 11 | FA | VU | xx -6 | 978 | 0 |2 | 6 | 6 | 6 |6 | 6 | 6 |6 | 12 | 13 | GA | QLBAAA | xx 7 | 647 | 1 |3 | 7 | 7 | 7 |7 | 7 | 7 |7 | 14 | 15 | HA | XY | xx 8 | 653 | 0 |0 | 8 | 8 | 8 |8 | 8 | 8 |8 | 16 | 17 | IA | DZ | xx !9 | 49 | 1 |1 | 9 | 9 | 9 |9 | 9 | 9 |9 | 18 | 19 | JA | XB | xx (10 rows) .. *** 268,292 WHERE onek2.unique1 980; unique1 | stringu1 -+-- ! 981 | TL ! 982 | UL 983 | VL - 984 | WL - 985 | XL - 986 | YL - 987 | ZL - 988 | AM 989 | BM 990 | CM 991 | DM ! 992 | EM 993 | FM 994 | GM ! 995 | HM ! 996 | IM ! 997 | JM ! 998 | KM ! 999 | LM (19 rows) And also something like that: *** /home/tianyin/source_code/postgresql-9.2.1/src/test/regress/expected/join.out 2012-09-19 14:47:58.0 -0700 --- /home/tianyin/source_code/postgresql-9.2.1/src/test/regress/results/join.out 2012-11-08 11:02:56.210666929 -0800 *** *** 2545,2561 ( SELECT COALESCE(q2, -1) AS qq FROM int8_tbl b ) AS ss2 USING (qq) INNER JOIN tenk1 c ON qq = unique2; ! QUERY PLAN ! --- ! Nested Loop !- Hash Full Join ! Hash Cond: (COALESCE(a.q1, 0::bigint) = COALESCE(b.q2, (-1)::bigint)) ! - Seq Scan on int8_tbl a ! - Hash !- Seq Scan on int8_tbl b !- Index Scan using tenk1_unique2 on tenk1 c ! Index Cond: (unique2 = COALESCE((COALESCE(a.q1, 0::bigint)), (COALESCE(b.q2, (-1)::bigint ! (8 rows) SELECT qq, unique1 FROM *** /home/tianyin/source_code/postgresql-9.2.1/src/test/regress/expected/stats.out 2012-09-19 14:47:58.0 -0700 --- /home/tianyin/source_code/postgresql-9.2.1/src/test/regress/results/stats.out 2012-11-08 11:04:15.206668806 -0800 *** *** 99,105 WHERE st.relname='tenk2' AND cl.relname='tenk2'; ?column? | ?column? | ?column? | ?column? --+--+--+-- ! t| t| t| t (1 row) SELECT st.heap_blks_read + st.heap_blks_hit = pr.heap_blks + cl.relpages, --- 99,105 WHERE st.relname='tenk2' AND cl.relname='tenk2'; ?column? | ?column? | ?column? | ?column? --+--+--+-- ! t| t| f| f (1 row) SELECT st.heap_blks_read + st.heap_blks_hit = pr.heap_blks + cl.relpages, *** *** 108,114 WHERE st.relname='tenk2' AND cl.relname='tenk2'; ?column? | ?column? --+-- ! t| t
[GENERAL] Message: incomplete startup packet
Hi Guys, We are having a problem with our pgsql 9.1 on Linux(Debian). Suddently, the database stop working and the logs shows the statements below just before the problem. Any thoughts? 2012-11-08 02:46:44.216 CST 0 509b70fb.4570LOG: execute S_2: COMMIT 2012-11-08 02:47:12.529 CST 08P01 509b7190.4583LOG: incomplete startup packet 2012-11-08 02:48:12.534 CST 08P01 509b71cc.458bLOG: incomplete startup packet 2012-11-08 02:49:12.541 CST 08P01 509b7208.4593LOG: incomplete startup packet 2012-11-08 02:50:12.546 CST 08P01 509b7244.459aLOG: incomplete startup packet 2012-11-08 02:51:12.557 CST 08P01 509b7280.45a0LOG: incomplete startup packet 2012-11-08 02:52:12.562 CST 08P01 509b72bc.45a8LOG: incomplete startup packet 2012-11-08 02:53:12.569 CST 08P01 509b72f8.45b0LOG: incomplete startup packet 2012-11-08 02:54:12.575 CST 08P01 509b7334.45b6LOG: incomplete startup packet 2012-11-08 02:55:12.582 CST 08P01 509b7370.45c0LOG: incomplete startup packet 2012-11-08 02:56:12.592 CST 08P01 509b73ac.45c7LOG: incomplete startup packet 2012-11-08 02:57:12.599 CST 08P01 509b73e8.45d5LOG: incomplete startup packet 2012-11-08 02:58:12.604 CST 08P01 509b7424.45dcLOG: incomplete startup packet 2012-11-08 02:59:12.612 CST 08P01 509b7460.45eaLOG: incomplete startup packet 2012-11-08 03:00:12.617 CST 08P01 509b749c.45f2LOG: incomplete startup packet Thank you, Rodrigo Pereira da Silva. -- 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] Message: incomplete startup packet
On 11/8/2012 2:05 PM, Rodrigo Pereira da Silva wrote: Hi Guys, We are having a problem with our pgsql 9.1 on Linux(Debian). Suddently, the database stop working and the logs shows the statements below just before the problem. Any thoughts? Just a word of caution that there may be no cause/effect relationship between the log messages and the non-working-ness. PG will print this message in the log if any TCP client simply connects to it, then closes the connection. For example if you have some kind of service liveness checker (nagios, etc) that checks that something is accepting connections, you'll see these messages. Note that they have time stamps exactly one minute apart. 2012-11-08 02:47:12.529 CST 08P01 509b7190.4583LOG: incomplete startup packet 2012-11-08 02:48:12.534 CST 08P01 509b71cc.458bLOG: incomplete startup packet 2012-11-08 02:49:12.541 CST 08P01 509b7208.4593LOG: incomplete startup packet -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_hba.conf directory?
Hello, I've searched the mailing list archives and google regarding using a directory to contain pg_hba.conf snippets. Does such a feature exist for any version of PG? Would this be a better question for a pg dev mailing list? Please Cc me, I am not (yet) subscribed to the list. Thanks! -Matt Zagrabelny -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Range constraint with unique index
I have a table that has an integer and a int8range. What I want is to add a constraint that stops anyone adding This email transmission is confidential and intended solely for the person or organisation to whom it is addressed. If you are not the intended recipient, you must not copy, distribute or disseminate the information, or take any action in reliance of it. Any views expressed in this message are those of the individual sender, except where the sender specifically states them to be the views of any organisation or employer. If you have received this message in error, do not open any attachment but please notify the sender (above) before deleting this message from your system. Please rely on your own virus check as no responsibility is taken by the sender for any damage rising out of any bug or virus infection. Registered Office: Motor Trade Technologies Limited, 33 The Clarendon Centre, Salisbury Business Park, Dairy Meadow Lane, Salisbury, Wiltshire, SP1 2TJ. Company No. 05573816
Re: [GENERAL] Range constraint with unique index
Steven Plummer wrote: I have a table that has an integer and a int8range. What I want is to add a constraint that stops anyone adding Maybe you want an exclusion constraint: http://www.postgresql.org/docs/9.0/static/ddl-constraints.html#DDL-CONSTRAINTS-EXCLUSION For example: CREATE EXTENSION btree_gist ; CREATE TABLE t1 (c1 INT NOT NULL, c2 int8range NOT NULL, EXCLUDE USING gist (c1 WITH =, c2 WITH )); INSERT INTO t1 VALUES (1, '[1,100]'); Now try to insert an overlapping row: INSERT INTO t1 VALUES (1, '[51,150]'); -Kevin -- 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 PostgreSQL have complete functional test cases?
On 11/09/2012 03:28 AM, Tianyin Xu wrote: Thanks a lot, Pavel, Jeff, Andres! I just changed the configuration file, postgresql.conf. Using the default one, all the regress tests are passed (so it should not be the block size?). But when I changed something, quite a number of tests are failed. Changed something. What did you change? !0 | 998 | 0 |0 | 0 | 0 | 0 |0 | 0 | 0 |0 | 0 |1 | AA | KMBAAA | xx 1 | 214 | 1 |1 | 1 | 1 | 1 |1 | 1 | 1 |1 | 2 |3 | BA | GI | xx 2 | 326 | 0 |2 | 2 | 2 | 2 |2 | 2 | 2 |2 | 4 |5 | CA | OM | xx 3 | 431 | 1 |3 | 3 | 3 | 3 |3 | 3 | 3 |3 | 6 |7 | DA | PQ | xx -4 | 833 | 0 |0 | 4 | 4 | 4 |4 | 4 | 4 |4 | 8 |9 | EA | BGBAAA | xx 5 | 541 | 1 |1 | 5 | 5 | 5 |5 | 5 | 5 |5 | 10 | 11 | FA | VU | xx -6 | 978 | 0 |2 | 6 | 6 | 6 |6 | 6 | 6 |6 | 12 | 13 | GA | QLBAAA | xx 7 | 647 | 1 |3 | 7 | 7 | 7 |7 | 7 | 7 |7 | 14 | 15 | HA | XY | xx 8 | 653 | 0 |0 | 8 | 8 | 8 |8 | 8 | 8 |8 | 16 | 17 | IA | DZ | xx !9 | 49 | 1 |1 | 9 | 9 | 9 |9 | 9 | 9 |9 | 18 | 19 | JA | XB | xx This is a context diff. http://en.wikipedia.org/wiki/Diff#Context_format . Lines beginning with + mean the line is added. Lines with - mean the line is removed. Lines with ! mean the line is changed. Here, you can see that two rows are not output that should be, and two others are output with different-than-expected values; they could've been swapped with each other, or just have totally unexpected values. See the expected/ files for the output that should be produced. -- Craig Ringer
Re: [GENERAL] pg_hba.conf directory?
On 11/09/2012 04:49 AM, Matt Zagrabelny wrote: Hello, I've searched the mailing list archives and google regarding using a directory to contain pg_hba.conf snippets. Does such a feature exist for any version of PG? If I understand you correctly, you want a `pg_hba.conf.d` where PostgreSQL reads every file in `pg_hba.conf.d` in alphabetical order and concatenates it to the contents of `pg_hba.conf`? So effectively you can create a `pg_hba.conf` from a bunch of small files? If so: No, there is no such feature in PostgreSQL. You might be able to come up with a convincing argument for the creation of one, especially if you made it generic enough that it also worked for postgresql.conf, but you probably won't get enough interest for someone else to write it. If you want the feature you'll probably need to write it yourself - after asking on pgsql-hackers to make sure there are no objections to the idea and that your design is reasonable. What you CAN do is simulate the feature using init scripts. Have your PostgreSQL start/stop scripts do something like: cat pg_hba.conf.head pg_hba.conf.d/* pg_hba.conf.tail pg_hba.conf (Note that the glob will sort alphabetically at least in bash; see http://superuser.com/questions/192280/does-bashs-match-files-in-alphanumeric-order) Make sure to put prominent comments in pg_hba.conf.head and pg_hba.conf.tail that explain that pg_hba.conf is a generated file, so people don't edit it then wonder why it's overwritten. You'll need to provide a reload command that rewrites pg_hba.conf and then signals PostgreSQL to reload or uses pg_ctl reload, as well as the usual start and stop commands. -- Craig Ringer
Re: [GENERAL] pg_hba.conf directory?
On 11/09/2012 04:49 AM, Matt Zagrabelny wrote: Hello, I've searched the mailing list archives and google regarding using a directory to contain pg_hba.conf snippets. Does such a feature exist for any version of PG? Oh, by the way; proposals are currently being discussed on pgsql-hackers about making it possible to modify postgresql.conf via SQL commands. This might be a good time to mention your interest in supporting a snippet directory. See the thread by Amit Kapila subject Proposal for Allow postgresql.conf values to be changed via SQL. -- Craig Ringer -- 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] Unexpectedly high disk space usage
On 11/07/2012 12:42 PM, Tom Lane wrote: ... because it occasionally causes transactions and queries to hang when an update causes a vacuum mid-day, effectively taking us offline randomly. I suspect this claim is based on ancient and no longer very relevant experience. Even so, if I felt the need to keep autovacuum off, what would I need to run regularly in order to keep things neat and tidy under the hood? Would a simple vacuum within each database suffice? Should I be logged in as the database owner or as an administrative user? Thanks, Ben -- 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] Unexpectedly high disk space usage
Lists li...@benjamindsmith.com writes: Even so, if I felt the need to keep autovacuum off, what would I need to run regularly in order to keep things neat and tidy under the hood? Would a simple vacuum within each database suffice? Should I be logged in as the database owner or as an administrative user? A plain vacuum (or probably better, vacuum analyze) done as superuser will suffice, as long as you do it often enough. 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] Does PostgreSQL have complete functional test cases?
Thanks, Craig, Yes, I know context diff. What I don't know is whether + or - some rows is a big problem, let's say correctness problem. I didn't write the test cases so I don't know what these test cases are exactly doing. If you tell me the failure of these test cases are severe and not acceptable, I'm fine with it. It means these configurations are not allowed. For this particular case, I figured out that it's because of the following settings, cpu_index_tuple_cost = 2147483647 which assigned a big number to the cpu_index_tuple_cost, affecting the query planner. But to me, the configuration settings should not affect the correctness, right? Because whatever optimizations you do, the results should be the same (what matters is the performance). And that's why I need testing before adjusting these values. T On Thu, Nov 8, 2012 at 3:29 PM, Craig Ringer cr...@2ndquadrant.com wrote: On 11/09/2012 03:28 AM, Tianyin Xu wrote: Thanks a lot, Pavel, Jeff, Andres! I just changed the configuration file, postgresql.conf. Using the default one, all the regress tests are passed (so it should not be the block size?). But when I changed something, quite a number of tests are failed. Changed something. What did you change? !0 | 998 | 0 |0 | 0 | 0 | 0 |0 | 0 | 0 |0 | 0 |1 | AA | KMBAAA | xx 1 | 214 | 1 |1 | 1 | 1 | 1 |1 | 1 | 1 |1 | 2 |3 | BA | GI | xx 2 | 326 | 0 |2 | 2 | 2 | 2 |2 | 2 | 2 |2 | 4 |5 | CA | OM | xx 3 | 431 | 1 |3 | 3 | 3 | 3 |3 | 3 | 3 |3 | 6 |7 | DA | PQ | xx -4 | 833 | 0 |0 | 4 | 4 | 4 |4 | 4 | 4 |4 | 8 |9 | EA | BGBAAA | xx 5 | 541 | 1 |1 | 5 | 5 | 5 |5 | 5 | 5 |5 | 10 | 11 | FA | VU | xx -6 | 978 | 0 |2 | 6 | 6 | 6 |6 | 6 | 6 |6 | 12 | 13 | GA | QLBAAA | xx 7 | 647 | 1 |3 | 7 | 7 | 7 |7 | 7 | 7 |7 | 14 | 15 | HA | XY | xx 8 | 653 | 0 |0 | 8 | 8 | 8 |8 | 8 | 8 |8 | 16 | 17 | IA | DZ | xx !9 | 49 | 1 |1 | 9 | 9 | 9 |9 | 9 | 9 |9 | 18 | 19 | JA | XB | xx This is a context diff. http://en.wikipedia.org/wiki/Diff#Context_format . Lines beginning with + mean the line is added. Lines with - mean the line is removed. Lines with ! mean the line is changed. Here, you can see that two rows are not output that should be, and two others are output with different-than-expected values; they could've been swapped with each other, or just have totally unexpected values. See the expected/ files for the output that should be produced. -- Craig Ringer -- Tianyin XU, http://cseweb.ucsd.edu/~tixu/
Re: [GENERAL] Use order by clause, got index scan involved
Hi Jeff Thank you for your reply. I will try to learn about effective_cache_size . Jian gao 2012/11/9 Jeff Janes jeff.ja...@gmail.com On Wed, Nov 7, 2012 at 11:41 PM, 高健 luckyjack...@gmail.com wrote: Hi all: What confused me is that: When I select data using order by clause, I got the following execution plan: postgres=# set session enable_indexscan=true; SET postgres=# explain SELECT * FROM pg_proc ORDER BY oid; QUERY PLAN Index Scan using pg_proc_oid_index on pg_proc (cost=0.00..321.60 rows=2490 width=552) You should probably use sample cases much larger than this when trying to understand the planner. With queries this small, it almost doesn't matter what plan is chosen. (1 row) postgres=# My Question is : If I want to find record using the where clause which hold the id column, the index scan might be used. But I just want to get all the records on sorted output format, Why index scan can be used here? I can’t imagine that: Step 1 Index is read into memory, then for each tuple in it, Step 2 Then we got the address of related data block, and then access the data block . Step 2 will be repeated for many times. I think it is not efficient. But step 2 will repeatedly find the block it is visiting to already be in memory, so it is efficient. Maybe the database system is clever enough to accumulate data access for same physical page, and reduce the times of physical page acess ? There is a bitmap scan which does that, but such a scan can't be used to fulfill a sort, because it doesn't return the rows in index order. What reduces the cost here is the various levels of caching implemented by the file system, the memory system, and the CPU. PG uses effective_cache_size to try to account for these effects, although I admit I don't quite understand what exactly it is doing in this case. I thought that setting effective_cache_size to absurdly low values would make the index scan cost estimate go up a lot, but it only made it go up a little. Cheers, Jeff
Re: [GENERAL] How is execution plan cost calculated for index scan
Hi Jeff Thank you very much. I determined this by changing each cost parameter and running explain, to see how much each one changed the cost estimate (after verifying the overall plan did not change). your method is so smart! Jian Gao 2012/11/9 Jeff Janes jeff.ja...@gmail.com On Wed, Nov 7, 2012 at 11:17 PM, 高健 luckyjack...@gmail.com wrote: Hi all: I want to see the explain plan for a simple query. My question is : How is the cost calculated? The cost parameter is: random_page_cost= 4 seq_page_cost = 1 cpu_tuple_cost =0.01 cpu_operator_cost =0.0025 The cost is estimates as 2*random_page_cost + cpu_tuple_cost + cpu_index_tuple_cost + 100* cpu_operator_cost. I determined this by changing each cost parameter and running explain, to see how much each one changed the cost estimate (after verifying the overall plan did not change). I was surprised the multiplier for cpu_operator_cost was that high. The two random_page_costs are one for the index leaf page and one for the table page. Higher pages in the index are assumed to be cached and thus not charged for IO. ... Firstly, database need to search for 9 index pages by sequential to find the index entry. For each index page in memory, every “index tuple” need to be scanned. That is not how indexes are traversed. Cheers, Jeff
Re: [GENERAL] Does PostgreSQL have complete functional test cases?
On Thu, Nov 8, 2012 at 05:37:22PM -0800, Tianyin Xu wrote: Thanks, Craig, Yes, I know context diff. What I don't know is whether + or - some rows is a big problem, let's say correctness problem. I didn't write the test cases so I don't know what these test cases are exactly doing. If you tell me the failure of these test cases are severe and not acceptable, I'm fine with it. It means these configurations are not allowed. For this particular case, I figured out that it's because of the following settings, cpu_index_tuple_cost = 2147483647 which assigned a big number to the cpu_index_tuple_cost, affecting the query planner. But to me, the configuration settings should not affect the correctness, right? Because whatever optimizations you do, the results should be the same (what matters is the performance). And that's why I need testing before adjusting these values. We can't test the optimizer is reasonable if you change settings in this way, so no, I am not surprised it failed. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 PostgreSQL have complete functional test cases?
On 11/09/2012 09:37 AM, Tianyin Xu wrote: Thanks, Craig, Yes, I know context diff. What I don't know is whether + or - some rows is a big problem, let's say correctness problem. I didn't write the test cases so I don't know what these test cases are exactly doing. The SQL to the test cases is right there in the regress directory, usually with comments explaining what each test is for. If you tell me the failure of these test cases are severe and not acceptable, I'm fine with it. It means these configurations are not allowed. It depends on the test. Some are testing whether the optimizer behaves as expected, choosing a particular plan. Some tests assume rows are being returned in a particular order, so if you change settings you can see at a glance that while the test fails the output is actually OK. Others are correctness tests and must produce exactly the the expected results. This is generally clear from the test query and comments. which assigned a big number to the cpu_index_tuple_cost, affecting the query planner. The planner tests are written for a particular configuration, so if you change the configuration they won't produce the expected results. That's OK; just make sure other tests are fine. It'd be nice to split the tests up into clearer groups - will fail if planner settings are changed; WARNING, will fail only if incorrect result is returned; FATAL etc. Right now, AFAIK that hasn't been done. -- Craig Ringer -- 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 PostgreSQL have complete functional test cases?
Ok, I agree that 2147483647 is not a reasonable setting. But what's the definition of reasonable? I just want to study the impact of the setting so I test the big number first. Having the setting: cpu_index_tuple_cost = 10 I still get failures of create_index, inherit, join, stats. When you give users the flexibility of configurations, you cannot say all the values mismatching with your expectations are not allowed. In fact the system allowed such settings. T On Thu, Nov 8, 2012 at 6:10 PM, Bruce Momjian br...@momjian.us wrote: On Thu, Nov 8, 2012 at 05:37:22PM -0800, Tianyin Xu wrote: Thanks, Craig, Yes, I know context diff. What I don't know is whether + or - some rows is a big problem, let's say correctness problem. I didn't write the test cases so I don't know what these test cases are exactly doing. If you tell me the failure of these test cases are severe and not acceptable, I'm fine with it. It means these configurations are not allowed. For this particular case, I figured out that it's because of the following settings, cpu_index_tuple_cost = 2147483647 which assigned a big number to the cpu_index_tuple_cost, affecting the query planner. But to me, the configuration settings should not affect the correctness, right? Because whatever optimizations you do, the results should be the same (what matters is the performance). And that's why I need testing before adjusting these values. We can't test the optimizer is reasonable if you change settings in this way, so no, I am not surprised it failed. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Tianyin XU, http://cseweb.ucsd.edu/~tixu/
Re: [GENERAL] Does PostgreSQL have complete functional test cases?
Thanks, Craig. That makes sense. Yes, it's quite a number of work to do. :-) I'll take a look at the comments and code and try to understand it. T On Thu, Nov 8, 2012 at 6:28 PM, Craig Ringer cr...@2ndquadrant.com wrote: On 11/09/2012 09:37 AM, Tianyin Xu wrote: Thanks, Craig, Yes, I know context diff. What I don't know is whether + or - some rows is a big problem, let's say correctness problem. I didn't write the test cases so I don't know what these test cases are exactly doing. The SQL to the test cases is right there in the regress directory, usually with comments explaining what each test is for. If you tell me the failure of these test cases are severe and not acceptable, I'm fine with it. It means these configurations are not allowed. It depends on the test. Some are testing whether the optimizer behaves as expected, choosing a particular plan. Some tests assume rows are being returned in a particular order, so if you change settings you can see at a glance that while the test fails the output is actually OK. Others are correctness tests and must produce exactly the the expected results. This is generally clear from the test query and comments. which assigned a big number to the cpu_index_tuple_cost, affecting the query planner. The planner tests are written for a particular configuration, so if you change the configuration they won't produce the expected results. That's OK; just make sure other tests are fine. It'd be nice to split the tests up into clearer groups - will fail if planner settings are changed; WARNING, will fail only if incorrect result is returned; FATAL etc. Right now, AFAIK that hasn't been done. -- Craig Ringer -- Tianyin XU, http://cseweb.ucsd.edu/~tixu/
Re: [GENERAL] Does PostgreSQL have complete functional test cases?
Craig Ringer wrote: It'd be nice to split the tests up into clearer groups - will fail if planner settings are changed; WARNING, will fail only if incorrect result is returned; FATAL etc. Right now, AFAIK that hasn't been done. Not sure that's enough of an improvement. Really, these tests should pass whatever the optimizer parameters are set to; instead of checking the exact order of the result set, they should check that the right tuples are returned. Except, of course, in those cases that specify ORDER BY. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Unexpectedly high disk space usage
On Thu, Nov 8, 2012 at 6:05 PM, Lists li...@benjamindsmith.com wrote: On 11/07/2012 12:42 PM, Tom Lane wrote: ... because it occasionally causes transactions and queries to hang when an update causes a vacuum mid-day, effectively taking us offline randomly. I suspect this claim is based on ancient and no longer very relevant experience. Even so, if I felt the need to keep autovacuum off, what would I need to run regularly in order to keep things neat and tidy under the hood? Would a simple vacuum within each database suffice? Should I be logged in as the database owner or as an administrative user? Just know that most of the time people think they need to turn off autovacuum they usually need to tune it instead. either more or less agressive depending on why they think they need to turn it off. If it's consuming too much IO then reduce cost limit / increase cost delay, if it's not aggressive enough, then reverse that and increase cost limit and decrease cost delay. If your IO subsystem can't keep up, then turning off autovacuum or turning it down simply be delaying the problem rather than solving it (i.e. throw more IO at it). -- 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 PostgreSQL have complete functional test cases?
Tianyin Xu wrote: Ok, I agree that 2147483647 is not a reasonable setting. But what's the definition of reasonable? I just want to study the impact of the setting so I test the big number first. Please don't top-post. Those values are not wrong. They just don't match what our current testing framework expects. Whether the generated plans are sensible or not is entirely another question; the queries should still return the same resultsets. Ordering of tuples within the resultset shouldn't matter, but the test framework is not smart enough to compare them that way. When you give users the flexibility of configurations, you cannot say all the values mismatching with your expectations are not allowed. In fact the system allowed such settings. Sure. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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 PostgreSQL have complete functional test cases?
On Thu, Nov 8, 2012 at 6:40 PM, Alvaro Herrera alvhe...@2ndquadrant.comwrote: Tianyin Xu wrote: Ok, I agree that 2147483647 is not a reasonable setting. But what's the definition of reasonable? I just want to study the impact of the setting so I test the big number first. Please don't top-post. Those values are not wrong. They just don't match what our current testing framework expects. Whether the generated plans are sensible or not is entirely another question; the queries should still return the same resultsets. Ordering of tuples within the resultset shouldn't matter, but the test framework is not smart enough to compare them that way. Thanks, Alvaro! I see your point. Yes, I'll try to understand the test cases to be able to judge the results by myself. What I was worried about was some settings might break the correctness and causes unexpected problem in the product run. So I choose to first test a bit by myself instead of directly applying what I want. You know, there's a gap between developers assumptions and users intentions. When you give users the flexibility of configurations, you cannot say all the values mismatching with your expectations are not allowed. In fact the system allowed such settings. Sure. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Tianyin XU, http://cseweb.ucsd.edu/~tixu/
Re: [GENERAL] Does PostgreSQL have complete functional test cases?
On 11/09/2012 10:36 AM, Alvaro Herrera wrote: Craig Ringer wrote: It'd be nice to split the tests up into clearer groups - will fail if planner settings are changed; WARNING, will fail only if incorrect result is returned; FATAL etc. Right now, AFAIK that hasn't been done. Not sure that's enough of an improvement. Really, these tests should pass whatever the optimizer parameters are set to; instead of checking the exact order of the result set, they should check that the right tuples are returned. Except, of course, in those cases that specify ORDER BY. Except for cases where the test is verifying that the optimizer chooses the right plan. Those must fail if different optimizer inputs are provided. OTOH, maybe those tests should explicitly set the required optimizer parameters, rather than assuming that the PostgreSQL install's defaults are what the test wants. For tests without ORDER BY it'd certainly be nice to sort the output and the expected result and *then* compare, so simple ordering differences due to non-deterministic ordering are ignored. That isn't really possible when the regression tests are just diffs between expected/ and actual output, though. -- Craig Ringer -- 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 PostgreSQL have complete functional test cases?
On Fri, Nov 9, 2012 at 10:50:42AM +0800, Craig Ringer wrote: On 11/09/2012 10:36 AM, Alvaro Herrera wrote: Craig Ringer wrote: It'd be nice to split the tests up into clearer groups - will fail if planner settings are changed; WARNING, will fail only if incorrect result is returned; FATAL etc. Right now, AFAIK that hasn't been done. Not sure that's enough of an improvement. Really, these tests should pass whatever the optimizer parameters are set to; instead of checking the exact order of the result set, they should check that the right tuples are returned. Except, of course, in those cases that specify ORDER BY. Except for cases where the test is verifying that the optimizer chooses the right plan. Those must fail if different optimizer inputs are provided. OTOH, maybe those tests should explicitly set the required optimizer parameters, rather than assuming that the PostgreSQL install's defaults are what the test wants. For tests without ORDER BY it'd certainly be nice to sort the output and the expected result and *then* compare, so simple ordering differences due to non-deterministic ordering are ignored. That isn't really possible when the regression tests are just diffs between expected/ and actual output, though. I question if the regression tests really warrant this kind of detailed work. Not only is it work that could be applied somewhere else, but if it makes adding new tests harder, it might be a net loss. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Using hstore, json, lo, etc from C extensions?
Hi all With the growing number of extensions that expose useful and increasingly widely used custom data types, I'm wondering: is there any way to use them from a C extension without going through the SPI? A look at the sources for hstore and json shows that they mostly define static functions and don't present much of an external C API. But say they did; if I or someone else wrote a proper C level API for use of hstore from other C extensions. Is there any way to actually link to it and use it from another C extension? It looks like extensions can use `requires` to express dependencies on each other ( http://www.postgresql.org/docs/current/static/extend-extensions.html http://www.postgresql.org/docs/9.2/static/extend-extensions.html) ... but is there any way to express a *linkage* dependency for C-level use, to actually link to hstore and use its non-static functions? As far as I can tell the only way to do it at the moment would be to extract most of the guts from the `hstore` extension into a `libhstore` that both the hstore extension and other extensions could link to. There doesn't seem to be provision for doing that in the extension building infrastructure, so it seems it'd be a manual hackjob. I realise you can still use other extensions via the SPI, I'm just wondering about direct C level usage for performance critical or specialized tasks. With the growing popularity of hstore, the interest in json, etc, this is likely to start coming up more. Question originally prompted by this SO post: http://stackoverflow.com/questions/13302682/postgresql-udf-in-c-using-hstore -- Craig Ringer http://www.postgresql.org/docs/9.2/static/extend-extensions.html
Re: [GENERAL] Running out of memory while making a join
On 11/08/2012 11:35 PM, Carlos Henrique Reimer wrote: Hi Craig, work_mem is defined with 10MB and yes, there are triggers defined on both tables Come to think of it, the triggers don't make any difference to memory use for a SELECT anyway. Your work_mem is perfectly reasonable. The plan looks pretty harsh, with that big nested loop, but I'm not aware of anything that'd cause that to run out of memory. Personally I'd be attaching a debugger to it and seeing what it was doing while it ran. I'm sure there are smarter ways to trace where the memory use is going, though. -- Craig Ringer -- 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] Using hstore, json, lo, etc from C extensions?
Craig Ringer cr...@2ndquadrant.com writes: With the growing number of extensions that expose useful and increasingly widely used custom data types, I'm wondering: is there any way to use them from a C extension without going through the SPI? Invoke the extension's exposed SQL functions at fmgr level? 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] Using hstore, json, lo, etc from C extensions?
On 11/09/2012 02:12 PM, Tom Lane wrote: Craig Ringer cr...@2ndquadrant.com writes: With the growing number of extensions that expose useful and increasingly widely used custom data types, I'm wondering: is there any way to use them from a C extension without going through the SPI? Invoke the extension's exposed SQL functions at fmgr level? That's certainly a lot better than using the SPI. Probably good enough for most uses, really. Good point, and thanks. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
