[HACKERS] Surge in MySQL converters
Hi All, Just thought the hackers might be interested to know that there has been a serious surge in the number of people in #postgresql coming in with questions related to switching from MySQL to PostgreSQL. Maybe it's something to do with Innobase - a few of them have specifically mentioned that. The no. 1 thing that people ask about is converting ENUMs. No.2 is probably configuring pg_hba.conf... Chris ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] New timezone data
On Mon, Oct 24, 2005 at 06:53:30PM -0600, Michael Fuhr wrote: > So in case anybody was going to check on that prior to the upcoming > 8.1 release candidate, there is new timezone data available (2005n > vs. the current 2005m) but it appears to have only minor changes > for Kyrgyzstan and Uruguay. If anybody wants, I'll submit a patch. The only change of real significanace was the US government changing the daylight saving times for the coming years. We should probably make clear somewhere that updating your OS doesn't fix the issue for PostgreSQL. But then, that's scheduled for 2007 so not exactly a real issue currently... Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgpPa0utei0CC.pgp Description: PGP signature
[HACKERS] SQL99 compat list
Has the sql compatibiliy list been updated? eg. for BETWEEN SYMMETRIC? Chris ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] SQL99 compat list
Am Dienstag, 25. Oktober 2005 11:17 schrieb Christopher Kings-Lynne: > Has the sql compatibiliy list been updated? eg. for BETWEEN SYMMETRIC? http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/catalog/sql_features.txt All signs point to "Sort of not really". -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 1: 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] Surge in MySQL converters
Christopher Kings-Lynne wrote: The no. 1 thing that people ask about is converting ENUMs. There was some discussion a while back about enums, but it petered out. If I have time I intend to follow this up in the 8.2 time frame: http://archives.postgresql.org/pgsql-hackers/2005-07/msg00959.php chyeers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] Win32 CHECK_FOR_INTERRUPTS() performance
> "Merlin Moncure" <[EMAIL PROTECTED]> writes: > > OK, running the latest patch. Observations: > > ... > > I ran tests for about an hour, randomly killing/canceling backends > > without any problems. > > Are we all comfortable that > http://archives.postgresql.org/pgsql-hackers/2005-10/msg01009.php > is OK to apply? Yea. I can vouch for Magnus as well (he said so off list). I'd vote with my own servers anyways. Merlin ---(end of broadcast)--- TIP 1: 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] [PATCHES] Win32 CHECK_FOR_INTERRUPTS() performance
> > > OK, running the latest patch. Observations: > > > ... > > > I ran tests for about an hour, randomly killing/canceling > backends > > > without any problems. > > > > Are we all comfortable that > > http://archives.postgresql.org/pgsql-hackers/2005-10/msg01009.php > > is OK to apply? > > Yea. I can vouch for Magnus as well (he said so off list). > I'd vote with my own servers anyways. Yup, and I can also vouch fo rmyself ;-) Just didn't get around to it until now. Yes, I believe we should be fine with that patch, even though we're late in beta. It's a huge win, so I htink it's worth the small extra risk it is. //Magnus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PG Killed by OOM Condition
daveg <[EMAIL PROTECTED]> writes: > I work with a client that runs 16Gb memory with 16Gb of swap on dual opterons > dedicated to postgres. They have large tables and like hash joins as they are > often the fastest way to a result, so work_mem is set fairly large. Sometimes > postgres is very inaccurate predicting real memory use verses work_mem and > will grow very much larger than expected. FWIW, 8.1 should be a lot better at this --- it can dynamically readjust the hash join parameters to keep memory usage under the work_mem limit. > When this happens the machine runs out of memory and swap. Without the oom > killer it simply hangs the machine which is inconvenient as it is at a remote > location. It shouldn't "hang" in any case ... something wrong there. I can believe that the machine would go to its knees as it thrashes more and more while approaching the totally-out-of-swap point, but it shouldn't hang up. You might have a kernel bug to deal with. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] sort_mem statistics ...
Bruce Momjian wrote: > Tom Lane wrote: > > "Marc G. Fournier" <[EMAIL PROTECTED]> writes: > > > On Tue, 18 Oct 2005, Tom Lane wrote: > > >> Looking at the code, I notice that the messages are all emitted at level > > >> NOTICE. Perhaps that was not such a good idea --- it'd be pretty much > > >> in-your-face if it were on all the time. Does anyone think it'd be a > > >> good idea to emit the trace_sort messages at level LOG, instead? > > > > > If someone sets trace_sort, does it matter what level its emit'd at? > > > > Well, yeah. It depends whether you are thinking of the trace feature as > > being used interactively, or as something turned on to gather data over > > time in a production installation. In the second case you'd want the > > info to go to the postmaster log, but not want to see it dumped on your > > terminal all the time ... > > I think it should go to the logs, hence LOG. Right now it just scrolls > off my screen: > > test=> select * from pg_class order by relname; > NOTICE: begin tuple sort: nkeys = 1, workMem = 1024, randomAccess = t > NOTICE: performsort starting: CPU 0.00s/0.00u sec elapsed 0.00 sec > NOTICE: performsort done: CPU 0.00s/0.00u sec elapsed 0.00 sec > NOTICE: sort ended: CPU 0.00s/0.00u sec elapsed 0.00 sec > relname | relnamespace | reltype | relowner | > relam | relfilenode | reltablespace | relpages | reltup > les | reltoastrelid | reltoastidxid | relhasindex | relisshared | > relkind | relnatts | relchecks | reltriggers | relukeys | relf > ... Simon also agrees, so changed to LOG in CVS. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] Win32 CHECK_FOR_INTERRUPTS() performance
"Magnus Hagander" <[EMAIL PROTECTED]> writes: >>> Are we all comfortable that >>> http://archives.postgresql.org/pgsql-hackers/2005-10/msg01009.php >>> is OK to apply? >> >> Yea. I can vouch for Magnus as well (he said so off list). >> I'd vote with my own servers anyways. > Yup, and I can also vouch fo rmyself ;-) Just didn't get around to it > until now. > Yes, I believe we should be fine with that patch, even though we're late > in beta. It's a huge win, so I htink it's worth the small extra risk it > is. OK, patch committed. Please recheck that CVS tip is OK. regards, tom lane ---(end of broadcast)--- TIP 1: 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
[HACKERS] determining random_page_cost value
[To admin: this message was posted earlier via google group. needless
to say, it was stalled waiting for approval, please ignore that
one. Thanks.]
Hi,
Yesterday in #pgsql, I was talking with neilc about determining rpc
value in a more concrete way. So I created a program that compares
exhaustive (all blocks are eventually read) random reads with
sequential reads. The full source is attached.
I tested the db files residing on a software RAID-1 composed of 2 IDE
7200rpm drives on linux 2.6.12.
What I discovered is:
random_page_cost (floating point)
Sets the planner's estimate of the cost of a nonsequentially
fetched disk page. This is measured as a multiple of the cost of a
sequential page fetch. A higher value makes it more likely a
sequential scan will be used, a lower value makes it more likely
an index scan will be used. The default is four.
is not precise enough. Which pages? Those that belong to the dbase
file or sequential pages on the media?
On dbases smaller (calculated from du )than 500M, I got a
ratio (random over sequential time) of 4.5:1. A 3.0GB dbase has a
ratio of 10:1. On a 3GB contiguous file, the ratio is about 4:1.
If, in fact, the pages meant in the quotation are pages occupied by
the dbase files, then does that mean the RPC config should be changed
over time to reflect the varying ratio (which I guess is due to file
fragmentation)? If that's the case, isn't RPC config actually a
per-database config rather than a per-cluster config?
Thanks,
YS (gnome)
/* determine the random_page_cost,
don't do O_DIRECT since pgsql also doesn't do O_DIRECT */
#include
#include
#include
#include
#include
#include
#include
#include
#include
#include
#include
#include
static int VERBOSE = 0;
double
curr_epoch()
{
struct timeval tv;
double time = -1;
if (0 == gettimeofday(&tv, NULL)) {
time = 1.0 * (tv.tv_sec * 1000 * 1000 + tv.tv_usec) / (1000.0 * 1000.0);
} else {
perror("gettimeofday");
exit(1);
}
return time;
}
double
strategy_sequential_read(int fd, off_t length, int block_size, char *buffer)
{
double elapsed_time = -1;
double start_time = -1;
char *reason;
ssize_t read_code;
off_t lseek_code;
ssize_t total_read;
lseek_code = lseek(fd, 0, SEEK_SET);
if ((lseek_code != 0) || (lseek_code == (off_t)-1)) {
reason = "lseek";
goto error;
}
start_time = curr_epoch();
total_read = 0;
while (1) {
read_code = read(fd, buffer, block_size); /* read() on disk i/o always do full read */
if (read_code == 0) {
break;
} else if (read_code == -1) {
reason = "read";
goto error;
}
if (VERBOSE) {
total_read += read_code;
if (total_read % (100*1024*1024)) {
printf("\r%0.2lf%%", 1.0*total_read/length);
}
}
}
elapsed_time = curr_epoch() - start_time;
goto cleanup;
error:
perror(reason);
cleanup:
return elapsed_time;
}
/* inclusive min, exclusive max */
int
rand_between(int min, int max)
{
int random = min + (int) (1.0 * max* rand() / (RAND_MAX + 1.0));
return random;
}
/* max file size that can be serviced is INT_MAX * block_size */
int
*random_shuffling(int length)
{
int *array;
char *reason;
int i,j,k;
if (!(array = malloc(length * sizeof(int {
reason = "malloc";
goto error;
}
for (i=0; i < length; i++) {
array[i] = i;
}
for (i=length-1; i >=1; i--) {
j = rand_between(0, i);
k = array[i];
array[i] = array[j];
array[j] = k;
}
return array;
error:
perror(reason);
return NULL;
}
double
strategy_random_read(int fd, off_t length, int block_size, char *buffer)
{
int block_count;
int *read_sequence = NULL;
char *reason = NULL;
double elapsed_time = -1;
double start_time = -1;
int i;
ssize_t total_read;
if (length/block_size+1 > INT_MAX) {
fprintf(stderr, "Cannot do random read on file larger than %ld bytes. Please increase block_size.\n", length);
goto cleanup;
}
block_count = length/block_size+1;
if ((read_sequence = random_shuffling(block_count)) == NULL) {
fprintf(stderr, "Cannot shuffle read order\n");
goto cleanup;
}
total_read = 0;
start_time = curr_epoch();
for (i=0; i < block_count; i++) {
ssize_t read_code;
off_t offset = read_sequence[i] * block_size;
off_t lseek_code = lseek(fd, offset, SEEK_SET);
if ((lseek_code != offset) || (lseek_code == (off_t)-1)) {
reason = "lseek";
goto error;
}
read_code = read(fd, buffer, block_size); /* read() on disk i/o always do full read */
if (read_code == 0) {
break;
} else if (read_code == -1) {
reason = "read";
goto error;
}
if (VERBOSE) {
total_read += read_code;
if (total_read % (1024*1024)) {
printf("%lf%%\r", 1.0*length/total_read);
}
}
}
elapsed_time = curr_epoch() - start_time;
goto cleanup;
error:
perror(reason);
cleanup:
return elapsed_time;
}
double
time_read_file(cons
Re: [HACKERS] determining random_page_cost value
Yohanes, > Yesterday in #pgsql, I was talking with neilc about determining rpc > value in a more concrete way. So I created a program that compares > exhaustive (all blocks are eventually read) random reads with > sequential reads. The full source is attached. Thanks for code. > I tested the db files residing on a software RAID-1 composed of 2 IDE > 7200rpm drives on linux 2.6.12. FWIW, most performance-conscious users will be using a SCSI RAID array. > is not precise enough. Which pages? Those that belong to the dbase > file or sequential pages on the media? Well, it's actually calculating the cost ratio of pulling non-sequential random *rows* from the db files against pulling sequential blocks. > On dbases smaller (calculated from du )than 500M, I got a > ratio (random over sequential time) of 4.5:1. A 3.0GB dbase has a > ratio of 10:1. On a 3GB contiguous file, the ratio is about 4:1. All of this goes to uphold Tom's general assertion that the default of 4 is more or less correct but the calculation in which we're using that number is not. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] expanded \df+ display broken in beta4
not sure exactly when this was changed, but expanded display of \df+
output is broken in beta4. compare:
[EMAIL PROTECTED] data]$ /usr/local/pgsql-8.1.x/bin/psql -p 5481
template1
Welcome to psql 8.1beta4, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
template1=# \df+ area
List of functions
Schema | Name | Result data type | Argument data types | Owner |
Language | Source code | Description
+--+--+-+--+--+-+---
pg_catalog | area | double precision | box | postgres |
internal | box_area| box area
pg_catalog | area | double precision | circle | postgres |
internal | circle_area | area of circle
pg_catalog | area | double precision | path| postgres |
internal | path_area | area of a closed path
(3 rows)
template1=# \x
Expanded display is on.
template1=# \df+ area
List of functions
Schema | Name | Result data type | Argument data types | Owner |
Language | Source code | Description
+--+--+-+--+--+-+---
pg_catalog | area | double precision | box | postgres |
internal | box_area| box area
pg_catalog | area | double precision | circle | postgres |
internal | circle_area | area of circle
pg_catalog | area | double precision | path| postgres |
internal | path_area | area of a closed path
[EMAIL PROTECTED] data]$ /usr/local/pgsql-8.0.x/bin/psql -p 5481
template1
Welcome to psql 8.0.4, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
template1=# \df+ area
List of functions
Schema | Name | Result data type | Argument data types | Owner |
Language | Source code | Description
+--+--+-+--+--+-+---
pg_catalog | area | double precision | box | postgres |
internal | box_area| box area
pg_catalog | area | double precision | circle | postgres |
internal | circle_area | area of circle
pg_catalog | area | double precision | path| postgres |
internal | path_area | area of a closed path
(3 rows)
template1=# \x
Expanded display is on.
template1=# \df+ area
List of functions
-[ RECORD 1 ]---+--
Schema | pg_catalog
Name| area
Result data type| double precision
Argument data types | box
Owner | postgres
Language| internal
Source code | box_area
Description | box area
-[ RECORD 2 ]---+--
Schema | pg_catalog
Name| area
Result data type| double precision
Argument data types | circle
Owner | postgres
Language| internal
Source code | circle_area
Description | area of circle
-[ RECORD 3 ]---+--
Schema | pg_catalog
Name| area
Result data type| double precision
Argument data types | path
Owner | postgres
Language| internal
Source code | path_area
Description | area of a closed path
template1=#
certainly the second is much more legible... this is on... FC3 in case
it matters.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] memcpy SEGV on AIX 5.3
Seneca Cunningham wrote: > On an powerPC AIX 5.3 box, initdb from 8.1beta4 segfaults at > src/backend/utils/hash/dynahash.c:673. No segfaults occur and all 98 > regression tests pass if a test is added to see if keycopy is memcpy and > if it is, go through a loop memcpying one byte at a time instead of > memcpying everything at once. looks like I'm seeing a similiar problem(using -HEAD) on AIX 5.3ML3 using the IBM AIX c-compiler. initdb just hangs after "selecting default max_connections ..." in a 100% CPU-loop. Stefan ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] expanded \df+ display broken in beta4
On Tue, Oct 25, 2005 at 02:27:50PM -0400, Robert Treat wrote: > not sure exactly when this was changed, but expanded display of \df+ > output is broken in beta4. http://archives.postgresql.org/pgsql-hackers/2005-06/msg00423.php http://archives.postgresql.org/pgsql-committers/2005-06/msg00149.php -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] expanded \df+ display broken in beta4
Good point. We modified 8.1 so backslash commands do not honor \x
because things like \d look silly in \x, but \df+ looks better with \x,
no question.
Ideally I think \x should allow three modes, on, off, and auto, with
auto doing \x if the row output is wider than the screen. If we had
this, backslash commands could be auto, or we can set all queries to
auto by default.
Added to TODO:
o Add auto-expanded mode so expanded output is used if the row
length is wider than the screen width.
Consider using auto-expanded mode for backslash commands like \df+.
---
Robert Treat wrote:
> not sure exactly when this was changed, but expanded display of \df+
> output is broken in beta4. compare:
>
>
> [EMAIL PROTECTED] data]$ /usr/local/pgsql-8.1.x/bin/psql -p 5481
> template1
> Welcome to psql 8.1beta4, the PostgreSQL interactive terminal.
>
> Type: \copyright for distribution terms
>\h for help with SQL commands
>\? for help with psql commands
>\g or terminate with semicolon to execute query
>\q to quit
>
> template1=# \df+ area
>List of functions
>Schema | Name | Result data type | Argument data types | Owner |
> Language | Source code | Description
> +--+--+-+--+--+-+---
> pg_catalog | area | double precision | box | postgres |
> internal | box_area| box area
> pg_catalog | area | double precision | circle | postgres |
> internal | circle_area | area of circle
> pg_catalog | area | double precision | path| postgres |
> internal | path_area | area of a closed path
> (3 rows)
>
> template1=# \x
> Expanded display is on.
> template1=# \df+ area
>List of functions
>Schema | Name | Result data type | Argument data types | Owner |
> Language | Source code | Description
> +--+--+-+--+--+-+---
> pg_catalog | area | double precision | box | postgres |
> internal | box_area| box area
> pg_catalog | area | double precision | circle | postgres |
> internal | circle_area | area of circle
> pg_catalog | area | double precision | path| postgres |
> internal | path_area | area of a closed path
>
>
> [EMAIL PROTECTED] data]$ /usr/local/pgsql-8.0.x/bin/psql -p 5481
> template1
> Welcome to psql 8.0.4, the PostgreSQL interactive terminal.
>
> Type: \copyright for distribution terms
>\h for help with SQL commands
>\? for help with psql commands
>\g or terminate with semicolon to execute query
>\q to quit
>
> template1=# \df+ area
>List of functions
>Schema | Name | Result data type | Argument data types | Owner |
> Language | Source code | Description
> +--+--+-+--+--+-+---
> pg_catalog | area | double precision | box | postgres |
> internal | box_area| box area
> pg_catalog | area | double precision | circle | postgres |
> internal | circle_area | area of circle
> pg_catalog | area | double precision | path| postgres |
> internal | path_area | area of a closed path
> (3 rows)
>
> template1=# \x
> Expanded display is on.
> template1=# \df+ area
> List of functions
> -[ RECORD 1 ]---+--
> Schema | pg_catalog
> Name| area
> Result data type| double precision
> Argument data types | box
> Owner | postgres
> Language| internal
> Source code | box_area
> Description | box area
> -[ RECORD 2 ]---+--
> Schema | pg_catalog
> Name| area
> Result data type| double precision
> Argument data types | circle
> Owner | postgres
> Language| internal
> Source code | circle_area
> Description | area of circle
> -[ RECORD 3 ]---+--
> Schema | pg_catalog
> Name| area
> Result data type| double precision
> Argument data types | path
> Owner | postgres
> Language| internal
> Source code | path_area
> Description | area of a closed path
>
> template1=#
>
>
> certainly the second is much more legible... this is on... FC3 in case
> it matters.
>
>
> Robert Treat
> --
> Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
>
>
> ---(end of broadcast)---
>
Re: [HACKERS] expanded \df+ display broken in beta4
On Tue, Oct 25, 2005 at 02:51:04PM -0400, Bruce Momjian wrote: > > Good point. We modified 8.1 so backslash commands do not honor \x > because things like \d look silly in \x, but \df+ looks better with \x, > no question. > > Ideally I think \x should allow three modes, on, off, and auto, with > auto doing \x if the row output is wider than the screen. If we had > this, backslash commands could be auto, or we can set all queries to > auto by default. I have a patch on my local system that fixes the splattering across the screen you currently get with multiline function definitions. Maybe once that has been fixed we should revisit this. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgp8EHZvoFPR2.pgp Description: PGP signature
[HACKERS] Postrges Queries Estimator
In continuation of my previous mails. Till now what all i figured out about the query flow in postgres has suddenly zeroed. Reason: analyze... till now i was running query with out analyaze command but once you give it ...i dont knw whts the path query takes.Any body havign any idea. AnujT ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] expanded \df+ display broken in beta4
Martijn van Oosterhout wrote: -- Start of PGP signed section. > On Tue, Oct 25, 2005 at 02:51:04PM -0400, Bruce Momjian wrote: > > > > Good point. We modified 8.1 so backslash commands do not honor \x > > because things like \d look silly in \x, but \df+ looks better with \x, > > no question. > > > > Ideally I think \x should allow three modes, on, off, and auto, with > > auto doing \x if the row output is wider than the screen. If we had > > this, backslash commands could be auto, or we can set all queries to > > auto by default. > > I have a patch on my local system that fixes the splattering across the > screen you currently get with multiline function definitions. Maybe > once that has been fixed we should revisit this. Yes, but \df+ is not multi-line per column. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: 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
[HACKERS] The use of (mb)print.c from psql in the scripts directory
Currently createlang and droplang use these two files mbprint.c and print.c to access the function printQuery() just to handle the displaying of the current languages. Is there any particular reason why it can't be made to use the version in libpq? In particular, does anyone really rely on the possibility of createlang invoking your pager to display the list of languages? It really does, you just have to make your terminal really small to see it. The reason I'm asking is because I'm working on the interaction between psql and the pager and keep running into issues w.r.t. createlang using the same code. Thanks in advance, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgpa7AyRsMrGm.pgp Description: PGP signature
Re: [HACKERS] expanded \df+ display broken in beta4
On Tue, 2005-10-25 at 14:42, Michael Fuhr wrote:
> On Tue, Oct 25, 2005 at 02:27:50PM -0400, Robert Treat wrote:
> > not sure exactly when this was changed, but expanded display of \df+
> > output is broken in beta4.
>
> http://archives.postgresql.org/pgsql-hackers/2005-06/msg00423.php
> http://archives.postgresql.org/pgsql-committers/2005-06/msg00149.php
>
grr... I thought I remembered Chris KL mentioning something about that
after I sent my email... and I see now that I actually did reply to that
thread saying I liked the \x \df+ output...
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 1: 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] determining random_page_cost value
Josh Berkus writes: >> I tested the db files residing on a software RAID-1 composed of 2 IDE >> 7200rpm drives on linux 2.6.12. > > FWIW, most performance-conscious users will be using a SCSI RAID > array. No worry, I'm not out to squeeze every little juice from a particular installation, which in this case is my home computer. I am interested in automating estimation of a suitable RPC value for a given installation. > Well, it's actually calculating the cost ratio of pulling non-sequential > random *rows* from the db files against pulling sequential blocks. Then running it against the db files should yield better estimation than on sequential pages. >> On dbases smaller (calculated from du )than 500M, I got a >> ratio (random over sequential time) of 4.5:1. A 3.0GB dbase has a >> ratio of 10:1. On a 3GB contiguous file, the ratio is about 4:1. > > All of this goes to uphold Tom's general assertion that the default of 4 is > more or less correct Doesn't this show that 4:1 is a pretty optimistic value considering that no long-running db files are fragmentation-free? >but the calculation in which we're using that number is > not. The calculation inside the planner, IOW, how the planner uses the RPC value? Thanks, YS. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] The use of (mb)print.c from psql in the scripts directory
Martijn van Oosterhout wrote: -- Start of PGP signed section. > Currently createlang and droplang use these two files mbprint.c and > print.c to access the function printQuery() just to handle the > displaying of the current languages. Is there any particular reason why > it can't be made to use the version in libpq? > > In particular, does anyone really rely on the possibility of createlang > invoking your pager to display the list of languages? It really does, > you just have to make your terminal really small to see it. > > The reason I'm asking is because I'm working on the interaction between > psql and the pager and keep running into issues w.r.t. createlang using > the same code. Yea, I think it would be good to use libpq rather than grab from psql. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] expanded \df+ display broken in beta4
On Tue, 2005-10-25 at 14:51, Bruce Momjian wrote:
>
> Good point. We modified 8.1 so backslash commands do not honor \x
> because things like \d look silly in \x, but \df+ looks better with \x,
> no question.
>
> Ideally I think \x should allow three modes, on, off, and auto, with
> auto doing \x if the row output is wider than the screen. If we had
> this, backslash commands could be auto, or we can set all queries to
> auto by default.
>
ISTM even a GUC to enable/disable would have been better scheme than
what we have now; we are basically leaving no options for those who
found the old behavior useful, while what we had before would at least
let people switch back and forth.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] expanded \df+ display broken in beta4
Robert Treat wrote: On Tue, 2005-10-25 at 14:51, Bruce Momjian wrote: Good point. We modified 8.1 so backslash commands do not honor \x because things like \d look silly in \x, but \df+ looks better with \x, no question. Ideally I think \x should allow three modes, on, off, and auto, with auto doing \x if the row output is wider than the screen. If we had this, backslash commands could be auto, or we can set all queries to auto by default. ISTM even a GUC to enable/disable would have been better scheme than what we have now; we are basically leaving no options for those who found the old behavior useful, while what we had before would at least let people switch back and forth. I think Robert is right here and the new behaviour is a step backwards. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] add_missing_from breaks existing views
Sample case: regression=# create table t1(f1 int, f2 int); CREATE TABLE regression=# set add_missing_from = true; SET regression=# create view v1 as select t1.*; NOTICE: adding missing FROM-clause entry for table "t1" CREATE VIEW regression=# \d v1 View "public.v1" Column | Type | Modifiers +-+--- f1 | integer | f2 | integer | View definition: SELECT t1.f1, t1.f2; The problem with this is that pg_dump will dump the view exactly like that: $ pg_dump -t v1 regression ... -- -- Name: v1; Type: VIEW; Schema: public; Owner: postgres -- CREATE VIEW v1 AS SELECT t1.f1, t1.f2; ALTER TABLE public.v1 OWNER TO postgres; and therefore the dump will fail to load into a machine with add_missing_from set to false. What I suggest we do about this is change addImplicitRTE() to set inFromCl true for implicitly added RTEs, so that the view rule will later be dumped as if the query had been written per spec. The problem with this is that it does not retroactively fix existing dumps (and pg_dump can't force the backend to list the view correctly, so "use 8.1 pg_dump" is no answer). That leaves us with two not very appealing choices: 1. Tell people they may have to set add_missing_from = true to reload a dump that contains such views. 2. Revert the change to make add_missing_from default as false, and wait a few more releases before making it default. Comments? regards, tom lane ---(end of broadcast)--- TIP 1: 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] expanded \df+ display broken in beta4
"Michael Paesold" <[EMAIL PROTECTED]> writes: > Robert Treat wrote: >> ISTM even a GUC to enable/disable would have been better scheme than >> what we have now; we are basically leaving no options for those who >> found the old behavior useful, while what we had before would at least >> let people switch back and forth. > I think Robert is right here and the new behaviour is a step backwards. Should we revert the patch for the time being, and take another go at it in 8.2? Is it practical to have the old behavior for \df (and anything else with particularly wide output) while still forcing \x off for \d? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] add_missing_from breaks existing views
On Tue, 2005-25-10 at 17:43 -0400, Tom Lane wrote: > What I suggest we do about this is change addImplicitRTE() to set > inFromCl true for implicitly added RTEs, so that the view rule will > later be dumped as if the query had been written per spec. Sounds reasonable. I wonder if this should be backpatched -- ISTM the proper representation of the view is with an explicit FROM list anyway, and making the correctness of view definition dependent on a GUC variable is only asking for trouble. > 1. Tell people they may have to set add_missing_from = true to reload > a dump that contains such views. pg_dump could itself specify "SET add_missing_from = true" before view definitions. However, that is definitely a hack :( -Neil ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] add_missing_from breaks existing views
On Tue, 2005-10-25 at 17:43 -0400, Tom Lane wrote: > 1. Tell people they may have to set add_missing_from = true to reload > a dump that contains such views. > > 2. Revert the change to make add_missing_from default as false, and > wait a few more releases before making it default. > > Comments? This needs to be (1). Whichever version we make the change for, there will always be a point where the pg_dump would be taken with add_missing_from=true by default and moving to new version with add_missing_from=false. Putting that off for another release will still allow that failure condition to exist at that later time: so we must tell people about it now. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] add_missing_from breaks existing views
On 2005-10-25, Simon Riggs <[EMAIL PROTECTED]> wrote: > On Tue, 2005-10-25 at 17:43 -0400, Tom Lane wrote: > >> 1. Tell people they may have to set add_missing_from = true to reload >> a dump that contains such views. >> >> 2. Revert the change to make add_missing_from default as false, and >> wait a few more releases before making it default. >> >> Comments? > > This needs to be (1). > > Whichever version we make the change for, there will always be a point > where the pg_dump would be taken with add_missing_from=true by default > and moving to new version with add_missing_from=false. Putting that off > for another release will still allow that failure condition to exist at > that later time: so we must tell people about it now. Wild idea: how about having pg_dump include SET add_missing_from = true; in the dump file if, and only if, it is set that way in the server? Perhaps do this only for server versions <8.1... and ensure that dumps from an 8.1 server include the explicit table names... -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Sequence dependencies
Hi, I notice that in the release notes there is a large query that should be run if upgrading from prior to 8.1, to ensure that sequence dependencies are recorded. Should we not just make this part of contrib/adddepend? Chris ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] add_missing_from breaks existing views
2. Revert the change to make add_missing_from default as false, and wait a few more releases before making it default. +1 No skin off our nose. What do we care if the default changes in a few releases time - however there are probably many end-users who will see problems upgrading... Chris ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] SQL99 compat list
Has the sql compatibiliy list been updated? eg. for BETWEEN SYMMETRIC? http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/catalog/sql_features.txt All signs point to "Sort of not really". Someone more knowledgeable than me will have to update it I think. These look like likely candidates: Row and table constructors? All the "basic array" support stuff? Row types? Basic roles Extended roles IN/OUT/INOUT params (dunno what they're called - or even if they're standard) Chris ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Sequence dependencies
Christopher Kings-Lynne wrote: > Hi, > > I notice that in the release notes there is a large query that should be > run if upgrading from prior to 8.1, to ensure that sequence dependencies > are recorded. > > Should we not just make this part of contrib/adddepend? Uh, I thought adddepend did more than just sequence dependencies, and I am worried it might mess up someone's database. Also, by doing it manually, users will see which sequences are being changed to late binding, and perhaps catch ones they do not want changed. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Sequence dependencies
Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: > I notice that in the release notes there is a large query that should be > run if upgrading from prior to 8.1, to ensure that sequence dependencies > are recorded. > Should we not just make this part of contrib/adddepend? No ... this is not an automatic "you surely wanted this" change. It is a fundamental change in semantics. Probably 95% of users will find it an improvement, but that's not enough to justify having tools that automatically screw the other 5%. (Also, I thought we'd determined that adddepend is unmaintained and likely broken...) regards, tom lane ---(end of broadcast)--- TIP 1: 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] The use of (mb)print.c from psql in the scripts directory
Martijn van Oosterhout writes: > Currently createlang and droplang use these two files mbprint.c and > print.c to access the function printQuery() just to handle the > displaying of the current languages. Is there any particular reason why > it can't be made to use the version in libpq? I believe Peter had deliberately moved away from that, basically on the grounds that we can't whack the behavior of libpq functions around every five minutes, the way some people like to do with psql and our other client programs (see nearby thread about \x ;-)) regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] add_missing_from breaks existing views
Neil Conway <[EMAIL PROTECTED]> writes: > On Tue, 2005-25-10 at 17:43 -0400, Tom Lane wrote: >> What I suggest we do about this is change addImplicitRTE() to set >> inFromCl true for implicitly added RTEs, so that the view rule will >> later be dumped as if the query had been written per spec. > Sounds reasonable. I wonder if this should be backpatched -- ISTM the > proper representation of the view is with an explicit FROM list anyway, I think it'd be reasonable to back-patch it into the branches that have the add_missing_from variable (how far back did we add that?). But that won't fix the problem with incompatible dump files from existing installations. regards, tom lane ---(end of broadcast)--- TIP 1: 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] add_missing_from breaks existing views
Andrew - Supernews <[EMAIL PROTECTED]> writes: > Wild idea: how about having pg_dump include SET add_missing_from = true; > in the dump file if, and only if, it is set that way in the server? Uh, no ... the global setting of add_missing_from does *not* tell you anything about whether there exist views in the database that were created under a different setting. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Sequence dependencies
Should we not just make this part of contrib/adddepend? Uh, I thought adddepend did more than just sequence dependencies, and I Yes it does... am worried it might mess up someone's database. Adddepend has been around for a long time - seems to work perfectly. Also, by doing it manually, users will see which sequences are being changed to late binding, and perhaps catch ones they do not want changed. The aepend script asks Y/N on each. Also, consider people upgrading from 7.2 to 8.1. They should only have to run the adddepend script to get ALL the dependencies, right? How about it's in the release notes and in adddepend? Chris ps. I can't actually _code_ it, as I'm no Perl coder :) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] add_missing_from breaks existing views
On 2005-10-26, Tom Lane <[EMAIL PROTECTED]> wrote: > Andrew - Supernews <[EMAIL PROTECTED]> writes: >> Wild idea: how about having pg_dump include SET add_missing_from = true; >> in the dump file if, and only if, it is set that way in the server? > > Uh, no ... the global setting of add_missing_from does *not* tell you > anything about whether there exist views in the database that were > created under a different setting. I realize that; but is it also not the case that someone who creates a view that requires add_missing_from, and then turns it off, has _already_ broken dump+restore on his own database? i.e. people who turn off add_missing_from intentionally are not likely to be using it. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Sequence dependencies
Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: > Also, consider people upgrading from 7.2 to 8.1. They should only have > to run the adddepend script to get ALL the dependencies, right? This isn't a "dependency" though. > How about it's in the release notes and in adddepend? Hmm, it's currently early Wednesday morning my time, and we were thinking of wrapping RC1 Thursday or Friday. An adddepend extension is going to get coded and tested when exactly? Also, I'm dubious about the assumption that adddepend still works at all, given the author's opinion here: http://archives.postgresql.org/pgsql-hackers/2005-06/msg00311.php The original plan was to pull it out of this release altogether, along with some other contrib modules that really belong on pgfoundry. It's currently looking like that won't get done :-( but hacking adddepend more in the core CVS is surely pretty far down our priority list. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Sequence dependencies
How about it's in the release notes and in adddepend? Hmm, it's currently early Wednesday morning my time, and we were thinking of wrapping RC1 Thursday or Friday. An adddepend extension is going to get coded and tested when exactly? Fair enough. Also, I'm dubious about the assumption that adddepend still works at all, given the author's opinion here: http://archives.postgresql.org/pgsql-hackers/2005-06/msg00311.php The original plan was to pull it out of this release altogether, along with some other contrib modules that really belong on pgfoundry. It's currently looking like that won't get done :-( but hacking adddepend more in the core CVS is surely pretty far down our priority list. Ok. Chris ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] add_missing_from breaks existing views
Andrew - Supernews <[EMAIL PROTECTED]> writes: > On 2005-10-26, Tom Lane <[EMAIL PROTECTED]> wrote: >> Uh, no ... the global setting of add_missing_from does *not* tell you >> anything about whether there exist views in the database that were >> created under a different setting. > I realize that; but is it also not the case that someone who creates a > view that requires add_missing_from, and then turns it off, has _already_ > broken dump+restore on his own database? No, because we consider that a client-local setting. This argument is akin to saying that if a client loads some data with client_encoding FOO into a database with server_encoding BAR, we are not responsible for dumping and reloading the data correctly. In hindsight I think there's no doubt that we blew it in not making ruleutils.c reverse-list implicit RTEs some time ago. The handwriting has been on the wall for that "feature" for a good while, and so we should long ago have thought about how to migrate nonstandard views to standard syntax. We missed the bet, though, so the question is now how to cover our mistake. Pretending it's the user's mistake isn't an answer that fits down my craw very well... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] PQescapeIdentifier
TODO item done for 8.2: * Add PQescapeIdentifier() to libpq Someone probably needs to check this :) Chris libpq.txt.gz Description: GNU Zip compressed data ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] Increase of buffers usage count by bgwriter
Hi Hackers, I found that bgwriter increments usage count of buffers when it writes the buffers. I feel this behavior is strange, because the behavior of bgwriter will affect buffer management strategy. When backends write LRU dirty buffers, they are replaced immediately. However, when bgwriter writes dirty buffer, the replacement is postponed until the next rotation of CLOCK. If the behavior is not intended, another UnpinBuffer that doesn't increase the buffers usage count will be needed. Otherwise, bgwriter should do behavior that looks like VACUUM, using StrategyHintVacuum(true). I'll appreciate any comments. Thanks, --- ITAGAKI Takahiro NTT Cyber Space Laboratories ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] add_missing_from breaks existing views
On 2005-10-26, Tom Lane <[EMAIL PROTECTED]> wrote: > Andrew - Supernews <[EMAIL PROTECTED]> writes: >> On 2005-10-26, Tom Lane <[EMAIL PROTECTED]> wrote: >>> Uh, no ... the global setting of add_missing_from does *not* tell you >>> anything about whether there exist views in the database that were >>> created under a different setting. > >> I realize that; but is it also not the case that someone who creates a >> view that requires add_missing_from, and then turns it off, has _already_ >> broken dump+restore on his own database? > > No, because we consider that a client-local setting. This argument is > akin to saying that if a client loads some data with client_encoding FOO > into a database with server_encoding BAR, we are not responsible for > dumping and reloading the data correctly. 8.0: test=# show add_missing_from; add_missing_from -- off (1 row) test=# set add_missing_from to true; SET test=# create view v1 as select test.*; CREATE VIEW test=# \q % pg_dump -U pgsql -s -d test | psql -U pgsql -d test2 [...] ERROR: missing FROM-clause entry for table "test" ERROR: relation "public.v1" does not exist Looks broken to me. I wasn't arguing that the broken behaviour was correct, merely that it exists. > In hindsight I think there's no doubt that we blew it in not making > ruleutils.c reverse-list implicit RTEs some time ago. Obviously. Isn't hindsight wonderful. > Pretending it's the user's mistake isn't > an answer that fits down my craw very well... I'm not claiming it's the user's mistake. My point is that if the user did in fact remove add_missing_from after creating views that depend on it, then they have already run into a bug. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 1: 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
