[HACKERS] Getting dump from old version
Hi! I use postgres6.1. Before install new version I do not make dump_all, but I move /usr/local/pgsql to /usr/local/pgsql_bk. After that I successfuly install new version to /usr/local/pgsql. But now I need some data from old postgres. I try to do this: % kill postmaster_id % mv /usr/local/pgsql /usr/local/pgsql-7.4 % mv /usr/local/pgsql_bk /usr/local/pgsql % su -l pgsql -c 'exec /usr/local/pgsql/bin/postmaster -D/usr/local/pgsql/data -S -o -F -d 3 /usr/local/pgsql/errlog' % ps axu | grep postmaster show me postmaster process running: postgres 14442 ?? Is 0:00.00 /usr/local/pgsql/bin/postmaster -D/usr/local/pgsql/data -S -o -F -d 3 (postgres) I try to exec pg_dumpall as postgres user: ./pg_dumpall Connection to database 'template1' failed. PQexec() -- Request was sent to backend, but backend closed the channel before r esponding. This probably means the backend terminated abnormally before or whil e processing the request. .. I try to connect as postgres user: % /usr/local/pgsql/bin/psql Connection to database 'postgres' failed. FATAL 1:Database postgres does not exist in pg_database % ls -al /usr/local/pgsql/data/base drwxr-xr-x 2 postgres postgres 1024 Apr 2 2000 postgres drwx-- 2 postgres postgres 1024 Apr 1 2000 template1 [ .. some other bases .. ] Could someone help me? I need to get dump from old DB. Thanks. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] *sigh*
On Wednesday 03 December 2003 13:59, Mark Kirkwood wrote: How about: Implement a function estimated_count that can be used instead of count. It could use something like the algorithm in src/backend/commands/analyze.c to get a reasonably accurate psuedo count quickly. The advantage of this approach is that count still means (exact)count (for your xact snapshot anyway). Then the situation becomes: Want a fast count? - use estimated_count(*) Want an exact count - use count(*) Something like select reltuples from pg_class where relname='foo'? Shridhar ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] BTree index and optimizer
Anand, VJ (MED, GEMS-IT) kirjutas K, 03.12.2003 kell 18:18: Hello: I am trying to find out, how is the B-tree index implemented for multiple columns? does Postgres, just concatenate the columns --- Yes. if this is the case, then how is the search performed? Also, does the optimizer choose the index, only when the constraining is on the leading subset of the index column? Yes. If you want it more complicated, you could check out if PG's R-tree indexes suit you. - Hannu ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] request for feedback - read-only GUC variables, pg_settings
Joe Conway wrote: We (mostly Bruce, Tom, Peter, and I) have been having a discussion on the PATCHES list regarding some new functionality related to read-only GUC variables. The net result is pasted at the bottom of this post. Here is a link to the discussion: http://archives.postgresql.org/pgsql-patches/2003-11/msg00363.php In short, 5 new read-only GUC variables are created allowing the value of certain compile-time settings to be queried. Also the pg_settings system view has been expanded to include category, short_desc, and extra_desc (corresponding to group, short_desc, and long_desc in the generic guc structure). The 5 GUC variables are: block_size - int Shows size of a disk block The main open question at this point is the name for the block_size variable. Peter favors block_size, Bruce favors page_size, Tom hasn't taken a position on that specific issue. Does anyone have and opinion on the variable name, or any general comments before I commit this? I hate to reply to this because I have already cast my vote, but block_size does not report the size of a disk block. It reports the size of a PostgreSQL block/page. Disk blocks are almost always 512 bytes in size. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] request for feedback - read-only GUC variables,
Marc G. Fournier wrote: block_size - int Shows size of a disk block integer_datetimes - bool Datetimes are integer based max_function_args - int Shows the maximum number of function arguments max_identifier_length - int Shows the maximum identifier length max_index_keys - int Shows the maximum number of index keys The main open question at this point is the name for the block_size variable. Peter favors block_size, Bruce favors page_size, Tom hasn't taken a position on that specific issue. Does anyone have and opinion on the variable name, or any general comments before I commit this? PAGE_SIZE generally refers to memory allocations, no? I'd go with block_size ... True, page size usually references virtual memory pages, so it is related to virtual memory mapping. Block size is much more related to on-disk storage, true. The only reason I was leaning toward page is that it is possible to confuse disk block (512 bytes) with a PostgreSQL block (8k), but maybe that is not relivant. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PostgreSQL 7.3.4 gets killed by SIG_KILL
On Thu, 04 Dec 2003 03:35:49 +0100 Magnus Naeslund(t) [EMAIL PROTECTED] wrote: Well this just isn't the case. There is no printout in kernel logs/dmesg (as it would be if the kernel killed it in an OOM situation). I have 1 GB of RAM, and 1.5 GB of swap (swap never touched). Do you have any system monitoring scripts that may be killing it as it may look like a runaway process? We've had this happen to us before. You tend to forget about things like that. -- Jeff Trout [EMAIL PROTECTED] http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] PostgreSQL 7.3.4 gets killed by SIG_KILL
Magnus Naeslund(t) [EMAIL PROTECTED] writes: Doug McNaught wrote: Linux is probably killing your process because it (the kernel) is low on memory. Unfortunately, this happens more often with older versions of the kernel. Add more RAM/swap or figure out how to make your query use less memory... -Doug Well this just isn't the case. There is no printout in kernel logs/dmesg (as it would be if the kernel killed it in an OOM situation). I have 1 GB of RAM, and 1.5 GB of swap (swap never touched). Ahh, that's an additional piece of information hat you didn't supply earlier. ;) Though your system memory is ample, is it possible that you're hitting a ulimit() on the stack size or heap size or something? I'm not sure what signal you'd get in such a case, though. Is it possible to somehow find out what process sent the KILL (or if it's the kernel) ? Not that I know of, unless it's in a logfile somewhere. You could try strace(8) on the backend running the query--that might give you some more info. I find this very weird to say the least... Yah. You might also consider running a more recent kernel, especially with such a big machine. 2.2.X never did play that well with large amounts of RAM... -Doug ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] request for feedback - read-only GUC variables, pg_settings
On Thu, Dec 04, 2003 at 06:53:40AM -0500, Bruce Momjian wrote: Joe Conway wrote: The main open question at this point is the name for the block_size variable. Peter favors block_size, Bruce favors page_size, Tom hasn't taken a position on that specific issue. Does anyone have and opinion on the variable name, or any general comments before I commit this? I hate to reply to this because I have already cast my vote, but block_size does not report the size of a disk block. It reports the size of a PostgreSQL block/page. Disk blocks are almost always 512 bytes in size. pg_block_size ? -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) God is real, unless declared as int ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Encoding problem with 7.4
On Wed, 3 Dec 2003, Stephan Szabo wrote: The locale settings depend on LC_* at initdb time only. When the postmaster starts it sets the locale based on the stored values from initdb, not on the current environment. With an SQL_ASCII database being accessed from a client with client_encoding set to SQL_ASCII (which it should be if you aren't setting it) the byte values of a string are passed along with no conversion for the encoding. This means that from within one environment you should get back what you put in, so it might *look* like it's KOI8-R if that's what you're in, but it's not because someone accessing it from say an ISO8859-1 system may see something different. As a result, the possibility to control encodings and locales looks as follows: initdb createdb psql Encoding: Y Y Y Locale:Y N N It seems that more natural scheme will be initdb createdb psql Encoding: Y Y Y Locale:Y Y Y Now the possibility to use different encodings for createdb and psql is a bit strange... Also, it is impossible to have different locales for different databases within one cluster, and it is impossible to use different locales with one database. The latter is even more critical. The reason is that the sorting under C locale is much more effective compared with one under another locales (10-50 times faster for some implementations!). Another reason is that for some applications it is _necessary_ to use different sort order for different tables. For example, I may have two tables: russian_persons and forein_persons, and i'd like to print the sorted list of persons. The russian_persons names must be sorted with ru_RU.KOI8-R locale, and the forein_persons - with C locale. Best wishes, E.R. _ Evgeny Rodichev Sternberg Astronomical Institute email: [EMAIL PROTECTED] Moscow State University Phone: 007 (095) 939 2383 Fax: 007 (095) 932 8841 http://www.sai.msu.su/~er ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Encoding problem with 7.4
E.Rodichev wrote: On Wed, 3 Dec 2003, Stephan Szabo wrote: The locale settings depend on LC_* at initdb time only. When the postmaster starts it sets the locale based on the stored values from initdb, not on the current environment. With an SQL_ASCII database being accessed from a client with client_encoding set to SQL_ASCII (which it should be if you aren't setting it) the byte values of a string are passed along with no conversion for the encoding. This means that from within one environment you should get back what you put in, so it might *look* like it's KOI8-R if that's what you're in, but it's not because someone accessing it from say an ISO8859-1 system may see something different. As a result, the possibility to control encodings and locales looks as follows: initdb createdb psql Encoding: Y Y Y Locale:Y N N It seems that more natural scheme will be initdb createdb psql Encoding: Y Y Y Locale:Y Y Y Now the possibility to use different encodings for createdb and psql is a bit strange... Also, it is impossible to have different locales for different databases within one cluster, and it is impossible to use different locales with one database. The latter is even more critical. The reason is that the sorting under C locale is much more effective compared with one under another locales (10-50 times faster for some implementations!). Another reason is that for some applications it is _necessary_ to use different sort order for different tables. For example, I may have two tables: russian_persons and forein_persons, and i'd like to print the sorted list of persons. The russian_persons names must be sorted with ru_RU.KOI8-R locale, and the forein_persons - with C locale. see Multi-Language Support section on TODO list at http://developer.postgresql.org/todo.php - note that this specifies per-column locales rather than per-table, which should be even more useful. Most of these items have no names against them, meaning you could work on them ... cheers andrew ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] request for feedback - read-only GUC variables,
Bruce Momjian wrote: Marc G. Fournier wrote: I'd go with block_size ... True, page size usually references virtual memory pages, so it is related to virtual memory mapping. Block size is much more related to on-disk storage, true. The only reason I was leaning toward page is that it is possible to confuse disk block (512 bytes) with a PostgreSQL block (8k), but maybe that is not relivant. I committed this yesterday as block_size because that had the majority support. Of course it's not too late to change it, but as Tom mentioned, we want to settle on something relatively quickly and then not mess with it afterwards. As another data point in the discussion, pg_controldata gives this: # pg_controldata pg_control version number:72 Catalog version number: 200312031 Database cluster state: in production pg_control last modified: Wed Dec 3 12:06:35 2003 Current log file ID: 0 Next log file segment:3 Latest checkpoint location: 0/27D5EEC Prior checkpoint location:0/9BA8A0 Latest checkpoint's REDO location:0/27D5EEC Latest checkpoint's UNDO location:0/0 Latest checkpoint's StartUpID:14 Latest checkpoint's NextXID: 6376 Latest checkpoint's NextOID: 156406 Time of latest checkpoint:Wed Dec 3 12:06:31 2003 Database block size: 8192 Blocks per segment of large relation: 131072 Maximum length of identifiers:64 Maximum number of function arguments: 32 Date/time type storage: 64-bit integers Maximum length of locale name:128 LC_COLLATE: C LC_CTYPE: C Note that pg_controldata also uses block size, so I'm still inclined to stick with that. Joe ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Encoding problem with 7.4
On Thu, 4 Dec 2003, E.Rodichev wrote: On Wed, 3 Dec 2003, Stephan Szabo wrote: The locale settings depend on LC_* at initdb time only. When the postmaster starts it sets the locale based on the stored values from initdb, not on the current environment. With an SQL_ASCII database being accessed from a client with client_encoding set to SQL_ASCII (which it should be if you aren't setting it) the byte values of a string are passed along with no conversion for the encoding. This means that from within one environment you should get back what you put in, so it might *look* like it's KOI8-R if that's what you're in, but it's not because someone accessing it from say an ISO8859-1 system may see something different. As a result, the possibility to control encodings and locales looks as follows: initdb createdb psql Encoding: Y Y Y As a note you can change the *client* encoding from psql, not the *server* encoding. They're also two separate notions. Andrew already commented on the TODO list. You may also wish to look through the archives for a recent message from Peter E on the subject as he was looking into starting towards multiple collations and such. ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] Does Catalog contain the information of index insert/update/delete tuples number ??
hi : pg_stat_user_tables store the n_tup_ins,n_tup_upd,n_tup_del information, and those information is very useful. Icheck the pg_stat_user_indexes table, but there are no such information. can i get such information in other way ? or system catalog does not store such information ! or those information can be derived from other statistic ? i want use these information to tuning the index please give me some suggestion! thank you very much! Kao , Nchu Taiwan
[HACKERS] Minor (very) feature request...
Would it be (is it?) possible to add timestamp to the log messages put out by postgresql? I've got several databases running in an environment where users have this annoying habit of coming up to me with (Oh yes, three days ago around 4pm our instrument had trouble writing to database X.). Having some way of telling which messages were output when would be helpful in such cases, since I'm not allowed to beat the users into submission... Thanks! -- Steve Wampler -- [EMAIL PROTECTED] The gods that smiled on your birth are now laughing out loud. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Minor (very) feature request...
run it through syslog? On Thu, 4 Dec 2003, Steve Wampler wrote: Would it be (is it?) possible to add timestamp to the log messages put out by postgresql? I've got several databases running in an environment where users have this annoying habit of coming up to me with (Oh yes, three days ago around 4pm our instrument had trouble writing to database X.). Having some way of telling which messages were output when would be helpful in such cases, since I'm not allowed to beat the users into submission... Thanks! -- Steve Wampler -- [EMAIL PROTECTED] The gods that smiled on your birth are now laughing out loud. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] request for feedback - read-only GUC variables, pg_settings
Bruce Momjian [EMAIL PROTECTED] writes: I hate to reply to this because I have already cast my vote, but block_size does not report the size of a disk block. It reports the size of a PostgreSQL block/page. Disk blocks are almost always 512 bytes in size. Perhaps then neither block nor page is best. Perhaps it should be buffer_size or something like that? -- greg ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Minor (very) feature request...
Marc G. Fournier wrote: run it through syslog? or set log_timestamp = true in postgresql.conf ? On Thu, 4 Dec 2003, Steve Wampler wrote: Would it be (is it?) possible to add timestamp to the log messages put out by postgresql? I've got several databases running in an environment where users have this annoying habit of coming up to me with (Oh yes, three days ago around 4pm our instrument had trouble writing to database X.). Having some way of telling which messages were output when would be helpful in such cases, since I'm not allowed to beat the users into submission... ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Minor (very) feature request...
On Thu, 2003-12-04 at 09:52, Andrew Dunstan wrote: Marc G. Fournier wrote: run it through syslog? or set log_timestamp = true in postgresql.conf ? Thanks - for some reason I was assuming that only applied to logging connections. Should have tried it... Thanks again! -- Steve Wampler -- [EMAIL PROTECTED] The gods that smiled on your birth are now laughing out loud. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] How to get started hacking on pgsql
I have an idea for what I think may be a very simple optimization for postgres to make. I would like to try my hand at implementing it, but the last time I tried I apparently started off in the wrong direction. In the following query, the sort step is completely unnecessary. The order is already guaranteed by the index: test=# create table test (a integer,b integer); CREATE TABLE test=# create index test_i on test(a,b); CREATE INDEX test=# explain select * from test where a=1 order by b; QUERY PLAN - Sort (cost=5.95..5.96 rows=6 width=8) Sort Key: b - Index Scan using test_i on test (cost=0.00..5.87 rows=6 width=8) Index Cond: (a = 1) (4 rows) At what point in the process would it make sense to check for this? Where should I be looking in the code? -- greg ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] rebuilding rpm for RH9 error
Lamar Owen wrote: On Tuesday 02 December 2003 06:29 pm, Gaetano Mendola wrote: Lamar Owen wrote: You need to specify that you are building for Red Hat 9 on the command I'll try. Ok. I did it on a RHAS 2.1 and I get: make[3]: Leaving directory `/usr/src/redhat/BUILD/postgresql-7.4/src/pl/tcl' make[3]: Entering directory `/usr/src/redhat/BUILD/postgresql-7.4/src/pl/plperl' i386-redhat-linux-gcc -O2 -march=i386 -mcpu=i686 -I/usr/include/et -Wmissing-declarations, -Wl,-rpath,/usr/lib/perl5/5.6.1/i386-linux/CORE -fpic -I. -I/usr/lib/perl5/5.6.1/i386-linux/CORE -I../../../src/include -D_GNU_SOURCE -I/usr/kerberos/include -I/usr/include/et -c -o plperl.o plperl.c plperl.c: In function `plperl_create_sub': plperl.c:306: warning: passing arg 1 of `perl_call_pv' from incompatible pointer type plperl.c:306: warning: passing arg 2 of `perl_call_pv' makes pointer from integer without a cast plperl.c:306: too few arguments to function `perl_call_pv' plperl.c:317: `thr' undeclared (first use in this function) plperl.c:317: (Each undeclared identifier is reported only once plperl.c:317: for each function it appears in.) plperl.c: In function `plperl_call_perl_func': plperl.c:425: warning: passing arg 1 of `perl_call_sv' from incompatible pointer type plperl.c:306: warning: passing arg 2 of `perl_call_pv' makes pointer from integer without a cast plperl.c:306: too few arguments to function `perl_call_pv' plperl.c:317: `thr' undeclared (first use in this function) plperl.c:317: (Each undeclared identifier is reported only once plperl.c:317: for each function it appears in.) plperl.c: In function `plperl_call_perl_func': plperl.c:425: warning: passing arg 1 of `perl_call_sv' from incompatible pointer type plperl.c:425: warning: passing arg 2 of `perl_call_sv' makes pointer from integer without a cast plperl.c:425: too few arguments to function `perl_call_sv' plperl.c:437: `thr' undeclared (first use in this function) plperl.c: In function `plperl_build_tuple_argument': plperl.c:810: warning: passing arg 1 of `perl_eval_pv' from incompatible pointer type plperl.c:810: warning: passing arg 2 of `perl_eval_pv' makes pointer from integer without a cast plperl.c:810: too few arguments to function `perl_eval_pv' make[3]: *** [plperl.o] Error 1 make[3]: Leaving directory `/usr/src/redhat/BUILD/postgresql-7.4/src/pl/plperl' make[2]: *** [all] Error 2 make[2]: Leaving directory `/usr/src/redhat/BUILD/postgresql-7.4/src/pl' make[1]: *** [all] Error 2 make[1]: Leaving directory `/usr/src/redhat/BUILD/postgresql-7.4/src' make: *** [all] Error 2 error: Bad exit status from /var/tmp/rpm-tmp.82708 (%build) RPM build errors: Bad exit status from /var/tmp/rpm-tmp.82708 (%build) Regards Gaetano Mendola ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] rebuilding rpm for RH9 error
On Thursday 04 December 2003 02:29 pm, Gaetano Mendola wrote: I did it on a RHAS 2.1 and I get: For RHAS 2.1 you need to tell it that you are running Red Hat 7.x (--define 'build7x 1') Which I think disables the plperl build (but don't quote me on that). I'm working on making this automatic; in fact, I have delayed release of the 7.3.5 RPMset because of this. I'd love to check it out with 7.3.5. (however, real work keeps getting in the way today; it may be a tonight thing, with the upload to happen tomorrow). -- Lamar Owen Director of Information Technology Pisgah Astronomical Research Institute 1 PARI Drive Rosman, NC 28772 (828)862-5554 www.pari.edu ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PostgreSQL 7.3.4 gets killed by SIG_KILL [SOLVED]
Jeff wrote: Do you have any system monitoring scripts that may be killing it as it may look like a runaway process? We've had this happen to us before. You tend to forget about things like that. This got me thinking, and i rechecked all possibilities. It turned out that we changed rlimit policies earlier and the default cpu time limits bleeded over to postgres since it didn't have a negating entry in the pam limits control. Since the startup scripts use su - postgres -c cmd it logged in and so got the now default cpu time values. So it was only a mindbug, and thats good :) Magnus ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] How to get started hacking on pgsql
Greg Stark kirjutas N, 04.12.2003 kell 19:55: I have an idea for what I think may be a very simple optimization for postgres to make. I would like to try my hand at implementing it, but the last time I tried I apparently started off in the wrong direction. In the following query, the sort step is completely unnecessary. The order is already guaranteed by the index: test=# create table test (a integer,b integer); CREATE TABLE test=# create index test_i on test(a,b); CREATE INDEX test=# explain select * from test where a=1 order by b; QUERY PLAN - Sort (cost=5.95..5.96 rows=6 width=8) Sort Key: b - Index Scan using test_i on test (cost=0.00..5.87 rows=6 width=8) Index Cond: (a = 1) (4 rows) At what point in the process would it make sense to check for this? Why not rewrite it as: test=# explain select * from test where a=1 order by a,b; - Index Scan using test_i on test (cost=0.00..17.07 rows=5 width=8) Index Cond: (a = 1) (2 rows) Where should I be looking in the code? Try to find where the modified query is tested for. It's probably be inside the optimizer, as index scan + no sort is not always faster than seq scan + sort, as shown by the same query after vacuum analyze (on an empty table) hannu=# vacuum analyze test; VACUUM hannu=# explain select * from test where a=1 order by a,b; QUERY PLAN --- Sort (cost=0.01..0.02 rows=1 width=8) Sort Key: a, b - Seq Scan on test (cost=0.00..0.00 rows=1 width=8) Filter: (a = 1) (4 rows) --- Hannu ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] How to get started hacking on pgsql
Hannu Krosing kirjutas N, 04.12.2003 kell 23:01: Where should I be looking in the code? Try to find where the modified query is tested for. It's probably be inside the optimizer, as index scan + no sort is not always faster than seq scan + sort, as shown by the same query after vacuum analyze (on an empty table) OTOH, it may be that all combinations of sort and index and where are not watched in the optimiser proper at all (too compliaced and/or too costly), but a keyhole optimiser is run over its resulting best plan to remove redundant sorts (but it misses combinations of sort and where like the one in your example) --- Hannu ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] bytea, index and like operator again and detailed report
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi,
while changing a column from base255 encoded text (all except null byte) to
bytea, I found the following bug in Postgresql's LIKE operator with indexes
(it follows a more detailed description then my old mails in -bugs and
- -general, including the proof of the bug):
The index condition in the query plan for where bytea_column like 'a%' is:
Index Cond: (bytea_col = 'a'::bytea) AND (bytea_col 'b'::bytea))
Filter: (bcol ~~ 'a%'::bytea)
This is correct.
The index condition in the query plan for bytea_column like '\\141%' (a
in octal is 141) is exaclty the same, including filter condition.
Index Cond: ((bcol = 'a'::bytea) AND (bcol 'b'::bytea))
Filter: (bcol ~~ 'a%'::bytea)
This is also correct.
The index condition in the query plan for bytea_column like '\\001%' is:
Index Cond: (bcol = '0'::bytea)
Filter: (bcol ~~ '\\001%'::bytea)
THIS IS WRONG! Isn't it?
If the byte is displayable in ASCII, then all is OK. If not, it seems that
Postgres takes the first character of the octal number and uses this as
comparison parameter.
With ä (344) it takes 3 ...
When index scan is disabled or from other reasons seqscan is used, the
query plan and the result is correct.
The result differs, if index is used or not used.
I guess there is too much conversion between different character sets etc.
A piece of test SQL and the results are attached.
My Version is:
PostgreSQL 7.4 on i386-portbld-freebsd4.8, compiled by GCC 2.95.4
The same was with 7.3.4
Ciao
Alvar
- --
** Alvar C.H. Freude -- http://alvar.a-blast.org/ -- http://odem.org/
** Berufsverbot? http://odem.org/aktuelles/staatsanwalt.de.html
** ODEM.org-Tour: http://tour.odem.org/
** Informationsgesellschaft: http://www.wsis-koordinierungskreis.de/
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.3 (FreeBSD)
iD8DBQE/z6YbOndlH63J86wRAr+qAKCo6yi3/0HGO13IkKP2KbyH147kMACeKq7T
WEKPu3dNKnesLqQUd9puyh0=
=Sivh
-END PGP SIGNATURE-
rollback;
begin;
create table test (bcol bytea);
create index tst_idx on test(bcol);
insert into test values ('abc');
insert into test values ('abcdef');
insert into test values ('abc\\003');
insert into test values ('abc\\002');
insert into test values ('abc\\004');
insert into test values ('abc\\005');
insert into test values ('\\001abc\\006');
insert into test values ('\\001xabc\\006');
insert into test values ('\\001\\002abc\\006');
insert into test values ('\\002\\003abc\\006');
insert into test values ('\\001abc\\006');
insert into test values ('\\001xabc\\006');
insert into test values ('\\001\\002abc\\006');
insert into test values ('\\000\\001\\002abc\\006');
insert into test values ('\\002\\003abc\\006');
set enable_indexscan=on;
set enable_seqscan=off;
select * from test where bcol like '\\001%';
select * from test where bcol like '\\001\002%';
select * from test where bcol like 'a%';
select * from test where bcol like '\\141%';
select * from test where bcol like 'ä%';
select * from test where bcol like '\\344%';
select * from test where bcol like '\1%';
explain analyze select * from test where bcol like '\\001%';
explain analyze select * from test where bcol like '\\001\002%';
explain analyze select * from test where bcol like 'a%';
explain analyze select * from test where bcol like '\\141%';
explain analyze select * from test where bcol like 'ä%';
explain analyze select * from test where bcol like '\\344%';
explain analyze select * from test where bcol like '\1%';
set enable_indexscan=off;
set enable_seqscan=on;
select * from test where bcol like '\\001%';
select * from test where bcol like '\\001\002%';
select * from test where bcol like 'a%';
select * from test where bcol like '\\141%';
select * from test where bcol like 'ä%';
select * from test where bcol like '\\344%';
select * from test where bcol like '\1%';
explain analyze select * from test where bcol like '\\001%';
explain analyze select * from test where bcol like '\\001\002%';
explain analyze select * from test where bcol like 'a%';
explain analyze select * from test where bcol like '\\141%';
explain analyze select * from test where bcol like 'ä%';
explain analyze select * from test where bcol like '\\344%';
explain analyze select * from test where bcol like '\1%';
pgp0.pgp
Description: PGP signature
db= \i /tmp/sql-bug.txt
ROLLBACK
BEGIN
CREATE TABLE
CREATE INDEX
INSERT 184807 1
INSERT 184808 1
INSERT 184809 1
INSERT 184810 1
INSERT 184811 1
INSERT 184812 1
INSERT 184813 1
INSERT 184814 1
INSERT 184815 1
INSERT 184816 1
INSERT 184817 1
INSERT 184818 1
INSERT 184819 1
INSERT 184820 1
INSERT 184821 1
SET
SET
bcol
--
(0 Zeilen)
bcol
--
(0 Zeilen)
bcol
-
abc
abc\002
abc\003
abc\004
abc\005
abcdef
(6 Zeilen)
bcol
-
abc
abc\002
abc\003
abc\004
abc\005
abcdef
(6 Zeilen)
bcol
--
(0 Zeilen)
bcol
--
(0 Zeilen)
bcol
--
(0 Zeilen)
QUERY
Re: [HACKERS] bytea, index and like operator again and detailed report
Alvar Freude wrote: while changing a column from base255 encoded text (all except null byte) to bytea, I found the following bug in Postgresql's LIKE operator with indexes (it follows a more detailed description then my old mails in -bugs and - -general, including the proof of the bug): Apparently you never read my reply to you all the way to the bottom. I said: Joe Conway wrote: Alvar Freude wrote: select * from test where b like '\001%'; This is weird. I'm sure it worked at one time -- will research. Joe I'm actively working on your issue. Please quit spamming all the lists with it. Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] bytea, index and like operator again and detailed
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, - -- Joe Conway [EMAIL PROTECTED] wrote: Apparently you never read my reply to you all the way to the bottom. I said: oh, sorry, I understand your mail wrong! I understand it in this way, that you are not sure that there is something buggy, so I looked more deeply in this issue. I'm actively working on your issue. Please quit spamming all the lists with it. I'm sorry, but after reading the desciption of bugs again I realised that according to the list desciption bugs is the wrong one und hackers the right; so, after debugging my code two days I'm now sure that there is something wrong with Postgres. Normally such Problems are in my Application ;-) If there is no answer like Ah, it really seems that there is a bug, thanks for the pointer, I'll look for it I every time guess that the issue depends on a mistake by myself or it is forgotten ... ;-) Ciao Alvar - -- ** Alvar C.H. Freude -- http://alvar.a-blast.org/ -- http://odem.org/ ** Berufsverbot? http://odem.org/aktuelles/staatsanwalt.de.html ** ODEM.org-Tour: http://tour.odem.org/ ** Informationsgesellschaft: http://www.wsis-koordinierungskreis.de/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (FreeBSD) iD8DBQE/z7Q5OndlH63J86wRAvrHAKDLAEz7X8ZeGah0CvL9QmglVMZrfwCdHAGr H17Kp6qy65jj32lBvsC/9zY= =b1l7 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] bytea, index and like operator again and detailed report
Alvar Freude wrote: I'm sorry, but after reading the desciption of bugs again I realised that according to the list desciption bugs is the wrong one und hackers the right; so, after debugging my code two days I'm now sure that there is something wrong with Postgres. Normally such Problems are in my Application ;-) Actually bugs is the best place to post bug reports if you think they might be in Postgres and not your app. If there is no answer like Ah, it really seems that there is a bug, thanks for the pointer, I'll look for it I every time guess that the issue depends on a mistake by myself or it is forgotten ... ;-) OK -- I guess I could have been more clear myself. Anyway I've been working on this on-and-off for a day now. I understand the root cause, but am still trying to figure out what the right solution is. It may take another day or so due to other things I have going on (like my job ;-)). Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] bytea, index and like operator again and detailed
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, - -- Joe Conway [EMAIL PROTECTED] wrote: I understand the root cause, but am still trying to figure out what the right solution is. It may take another day or so due to other things I have going on (like my job ;-)). :-) If you need some testing data, I can give you a dump ... Actually bugs is the best place to post bug reports if you think they might be in Postgres and not your app. perhaps someone should change the list descriptions at http://www.postgresql.org/lists.html pgsql-bugs If you a find a bug, please fill out the form typically located in the PostgreSQL source code at src/pgsql/doc/bug.template and mail it to this mailing list. [...] pgsql-hackers (Developers List) [...] This list is for the discussion of current development issues, problems and bugs and the discussion of proposed new features. [...] ;-) So, my idea was: Uups, it was the wrong group, therefore no real answer ;) Ciao Alvar - -- ** Alvar C.H. Freude -- http://alvar.a-blast.org/ -- http://odem.org/ ** Berufsverbot? http://odem.org/aktuelles/staatsanwalt.de.html ** ODEM.org-Tour: http://tour.odem.org/ ** Informationsgesellschaft: http://www.wsis-koordinierungskreis.de/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (FreeBSD) iD8DBQE/z7giOndlH63J86wRAmBwAJ46KjKlahE8vYcVG33lOsmi2sGqiACgluPn 8CE/HqK+OqYuGQONXnBszP0= =5+rU -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] How to get started hacking on pgsql
Greg Stark [EMAIL PROTECTED] writes: At what point in the process would it make sense to check for this? You'd need to mess with the code that generates pathkeys describing the sort ordering of index scans --- read about pathkeys in src/backend/optimizer/README. As Hannu notes nearby, the existing code is not broken for cases like explain select * from test where a=1 order by a,b; and it would not be cool to break that case while fixing explain select * from test where a=1 order by b; This probably means that you'd need to offer up multiple pathkey descriptions of an index's sort order, ie, both ((a), (b)) and ((b)). I'm not sure how painful that would be. You could quick-hack it by generating multiple indexscan Paths that are really identical but have different pathkeys --- but I think that would have unpleasant consequences for planning time ... it'd be better to attach multiple pathkeys to a single Path. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
