Re: [HACKERS] Implementation of new operators inside the PostgreSQL
rupesh bajaj wrote: I am mailing on behalf of the Database Systems Lab, Indian Institute of Science(IISc), Bangalore, India. We have implemented three new operators internal to the PostgreSQL 8.1.2 engine in order to support queries on multilingual data(english and hindi as of now). It can be extended to support any other language. In the process, we have augmented the parser, rewriter, planner and optimizer to support such queries. We want to make a release of our version now. Could you please let me know if there is any standard procedure to be followed for release of PostgreSQL. Also please let me know if we can release our version on the official PostgreSQL site - www.postgresql.org The normal procedure is to discuss the feature and design on pgsql-hackers first, and then send a patch against CVS HEAD to pgsql-patches for review. The first thing you need to do is to convince people that the feature is worth having. What does it provide that you can't do with the current feature set? How does it work from user's point of view? After that you need to discuss the design. Are all those changes to the parser, rewriter, planner and optimizer really necessary? How does it interact with all the other features, like tsearch2 and indexes? Since you've already done the work, you can just submit the patch as it is for people to look at, in addition to the above, but it's extremely unlikely that it will be accepted as it is. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [GENERAL] Unable to get postgres running after long time no vacuum
"Leon Mergen" <[EMAIL PROTECTED]> writes: >> Perhaps you are not shutting down the standalone mode cleanly after >> the vacuum? > Okay, that was obviously it -- I didn't realize I needed to send a > crtl+D signal to the server when in single user mode to shut it down, > and figured that any uncommitted changes would be replayed when the > database was started. Most of them would be, but the one you actually needed here (the update of pg_database.datfrozenxid) is treated as a non-WAL-logged action in pre-8.2 releases :-( ... so you had to have a checkpoint to ensure it got out to disk. It's actually not that easy to get out of the single-user mode without it doing a checkpoint. I suppose you must have either SIGQUIT or SIGKILL'd it. While there's nothing we can do about SIGKILL, it strikes me that it might be a good safety measure if single-user mode treated SIGQUIT the same as SIGTERM, ie, non-panic shutdown. Comments anyone? 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] [GENERAL] Unable to get postgres running after long time no vacuum
"Leon Mergen" <[EMAIL PROTECTED]> writes: > On 7/8/07, Tom Lane <[EMAIL PROTECTED]> wrote: >> It's actually not that easy to get out of the single-user mode without >> it doing a checkpoint. I suppose you must have either SIGQUIT or >> SIGKILL'd it. While there's nothing we can do about SIGKILL, it strikes >> me that it might be a good safety measure if single-user mode treated >> SIGQUIT the same as SIGTERM, ie, non-panic shutdown. Comments anyone? > What I found with SIGTERM was that it did nothing, since it was still > waiting for the (single-user) client to exit, and thus had no effect > unless I sent an end-of-input ctrl+d singal, which would have resulted > in a shutdown anyway. We might need a bit of rejiggering around the edges of the single-user command reading code to make this work nicely, but what I'm envisioning is that a keyboard-generated SIGQUIT ought to result in a clean shutdown, same as EOF does. At least on my machine there doesn't seem to be a defined way to generate SIGTERM from the terminal; so I can see where if someone hasn't read the postgres man page carefully, their first instinct upon finding that control-C doesn't get them out of single-user mode might be to type control-\ (or whatever the local QUIT character is). It doesn't seem like it should be quite that easy to force a panic stop. 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] Should we bump libpq major version for 8.3?
As of today there are two new functions exported by libpq.so since 8.2 (lo_truncate and PQconnectionUsedPassword). Currently, libpq/Makefile sets the major.minor shlib version to 5.1 as compared to 5.0 in PG 8.2. Should it be 6.0? I seem to recall people chastizing us for not bumping the major version if there were any ABI changes at all, forward-compatible or not. regards, tom lane ---(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] [BUGS] BUG #3431: age() gets the days wrong
"Pelle Johansson" <[EMAIL PROTECTED]> writes:
> The age() function seem to work by first counting months until less than a
> month remains to to the second argument, then counting days left. This
> doesn't give the correct result, as shown by this example:
> # select column1, age(column1, '2006-11-02'), date '2006-11-02' +
> age(column1, '2006-11-02') from (values ('2007-01-31'::date),
> ('2007-02-01')) as alias;
> column1 | age | ?column?
> ++-
> 2007-01-31 | 2 mons 29 days | 2007-01-31 00:00:00
> 2007-02-01 | 2 mons 29 days | 2007-01-31 00:00:00
> (2 rows)
I took another look at this example. I believe what is actually going
wrong here is that when timestamp_age converts a month into an
equivalent number of days, it uses the number of days in the first
month of the interval it's dealing with (ie, the month containing
the earlier of the two dates). This is just wrong, because interval
addition adds months first and then days. The appropriate conversion
to use is actually the length of the next-to-last month of the interval.
As an example, 8.2 and CVS HEAD produce
regression=# select age('2007-03-14', '2007-02-15');
age
-
27 days
(1 row)
which is reasonable, but
regression=# select age('2007-04-14', '2007-02-15');
age
---
1 mon 27 days
(1 row)
is not so reasonable, nor is
regression=# select age('2007-03-14', '2007-01-15');
age
---
1 mon 30 days
(1 row)
If we change the code to use the next-to-last month of the interval
then these two cases produce '1 mon 30 days' and '1 mon 27 days'
respectively.
Another problem is that the code isn't doing the propagate-to-next-field
bit for negative fractional seconds. Hence it produces
regression=# select age('2007-02-14 01:00:00', '2007-01-15 01:00:00.4');
age
--
30 days -00:00:00.40
(1 row)
which is maybe not incorrect, but certainly fairly inconsistent with
regression=# select age('2007-02-14 01:00:00', '2007-01-15 01:00:01');
age
--
29 days 23:59:59
(1 row)
Hence I propose the attached patch. This does not change any existing
regression test outputs, but it does change the example given in the
documentation: age(timestamp '2001-04-10', timestamp '1957-06-13')
will now produce '43 years 9 mons 28 days' not 27 days. Which actually
is correct if you try to add back the result to timestamp '1957-06-13'.
It also appears to fix Palle's example:
regression=# select column1, age(column1, '2006-11-02'), date '2006-11-02' +
age(column1, '2006-11-02') from (values ('2007-01-31'::date),
('2007-02-01')) as alias;
column1 | age | ?column?
++-
2007-01-31 | 2 mons 29 days | 2007-01-31 00:00:00
2007-02-01 | 2 mons 30 days | 2007-02-01 00:00:00
(2 rows)
As I said earlier, I'm worried about changing the behavior of a function
that's been around for so long, so I'm disinclined to back-patch this.
But it seems like a reasonable change to make in 8.3. Comments?
regards, tom lane
Index: timestamp.c
===
RCS file: /cvsroot/pgsql/src/backend/utils/adt/timestamp.c,v
retrieving revision 1.179
diff -c -r1.179 timestamp.c
*** timestamp.c 6 Jul 2007 04:15:59 - 1.179
--- timestamp.c 8 Jul 2007 19:45:04 -
***
*** 3044,3050
if (timestamp2tm(dt1, NULL, tm1, &fsec1, NULL, NULL) == 0 &&
timestamp2tm(dt2, NULL, tm2, &fsec2, NULL, NULL) == 0)
{
! fsec = (fsec1 - fsec2);
tm->tm_sec = tm1->tm_sec - tm2->tm_sec;
tm->tm_min = tm1->tm_min - tm2->tm_min;
tm->tm_hour = tm1->tm_hour - tm2->tm_hour;
--- 3044,3051
if (timestamp2tm(dt1, NULL, tm1, &fsec1, NULL, NULL) == 0 &&
timestamp2tm(dt2, NULL, tm2, &fsec2, NULL, NULL) == 0)
{
! /* form the symbolic difference */
! fsec = fsec1 - fsec2;
tm->tm_sec = tm1->tm_sec - tm2->tm_sec;
tm->tm_min = tm1->tm_min - tm2->tm_min;
tm->tm_hour = tm1->tm_hour - tm2->tm_hour;
***
*** 3064,3069
--- 3065,3081
tm->tm_year = -tm->tm_year;
}
+ /* propagate any negative fields into the next higher field */
+ while (fsec < 0)
+ {
+ #ifdef HAVE_INT64_TIMESTAMP
+ fsec += USECS_PER_SEC;
+ #else
+ fsec += 1.0;
+ #endif
+ tm->tm_sec--;
+ }
+
while (tm->tm_sec < 0)
{
tm->tm_sec += SECS_PER_MINUTE;
***
*** 3082,3097
tm->tm_mday--;
}
! while (tm->tm_m
Re: [HACKERS] Should we bump libpq major version for 8.3?
* Tom Lane ([EMAIL PROTECTED]) wrote: > As of today there are two new functions exported by libpq.so since > 8.2 (lo_truncate and PQconnectionUsedPassword). Currently, > libpq/Makefile sets the major.minor shlib version to 5.1 as compared > to 5.0 in PG 8.2. Should it be 6.0? I seem to recall people > chastizing us for not bumping the major version if there were any > ABI changes at all, forward-compatible or not. No, it should be 5.1 (we havn't released a 5.1 at all yet, have we?). Adding functions is a minor shlib bump and should *not* change the SONAME (which includes the major here, objdump -p libpq.so.5.0). Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [BUGS] BUG #3431: age() gets the days wrong
Pelle Johansson <[EMAIL PROTECTED]> writes: > If you were to use the result for subtracting from the first value, > instead of adding to the second, the conditions are reversed. It's > not really as obvious as I first thought whether there's 2 months and > 29 days or 2 months and 30 days between 2006-11-02 and 2007-02-01... Hmm, that's a really good point; perhaps the original author was thinking of it in those terms, in which case using the first month of the interval is indeed sane. (Almost: I believe that the loop can iterate more than once, and then you need to look to the second month etc. The code's not doing that, so there's still a corner-case bug, plus the fsec issue.) Other than that corner case, it seems the behavior we currently have is if x > y, age() produces a positive interval such that x - age(x, y) = y if x < y, age() produces a negative interval such that y + age(x, y) = x Are we satisfied with just documenting that, or do we want to change it, and if so to what? As the code currently stands, we have the symmetry property age(x,y) = - age(y,x) for all x,y. I don't think we can preserve that if we try to simplify the relationship to interval addition/subtraction. Comments? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [BUGS] BUG #3431: age() gets the days wrong
Hi Tom,
I only have one remark really, which I first thought of after sending
the report.
If you were to use the result for subtracting from the first value,
instead of adding to the second, the conditions are reversed. It's
not really as obvious as I first thought whether there's 2 months and
29 days or 2 months and 30 days between 2006-11-02 and 2007-02-01...
If one want mathematical correctness, it will have to be defined
precisely.
--
Pelle Johansson
8 jul 2007 kl. 22.07 skrev Tom Lane:
"Pelle Johansson" <[EMAIL PROTECTED]> writes:
The age() function seem to work by first counting months until
less than a
month remains to to the second argument, then counting days left.
This
doesn't give the correct result, as shown by this example:
# select column1, age(column1, '2006-11-02'), date '2006-11-02' +
age(column1, '2006-11-02') from (values ('2007-01-31'::date),
('2007-02-01')) as alias;
column1 | age | ?column?
++-
2007-01-31 | 2 mons 29 days | 2007-01-31 00:00:00
2007-02-01 | 2 mons 29 days | 2007-01-31 00:00:00
(2 rows)
I took another look at this example. I believe what is actually going
wrong here is that when timestamp_age converts a month into an
equivalent number of days, it uses the number of days in the first
month of the interval it's dealing with (ie, the month containing
the earlier of the two dates). This is just wrong, because interval
addition adds months first and then days. The appropriate conversion
to use is actually the length of the next-to-last month of the
interval.
As an example, 8.2 and CVS HEAD produce
regression=# select age('2007-03-14', '2007-02-15');
age
-
27 days
(1 row)
which is reasonable, but
regression=# select age('2007-04-14', '2007-02-15');
age
---
1 mon 27 days
(1 row)
is not so reasonable, nor is
regression=# select age('2007-03-14', '2007-01-15');
age
---
1 mon 30 days
(1 row)
If we change the code to use the next-to-last month of the interval
then these two cases produce '1 mon 30 days' and '1 mon 27 days'
respectively.
Another problem is that the code isn't doing the propagate-to-next-
field
bit for negative fractional seconds. Hence it produces
regression=# select age('2007-02-14 01:00:00', '2007-01-15
01:00:00.4');
age
--
30 days -00:00:00.40
(1 row)
which is maybe not incorrect, but certainly fairly inconsistent with
regression=# select age('2007-02-14 01:00:00', '2007-01-15 01:00:01');
age
--
29 days 23:59:59
(1 row)
Hence I propose the attached patch. This does not change any existing
regression test outputs, but it does change the example given in the
documentation: age(timestamp '2001-04-10', timestamp '1957-06-13')
will now produce '43 years 9 mons 28 days' not 27 days. Which
actually
is correct if you try to add back the result to timestamp
'1957-06-13'.
It also appears to fix Palle's example:
regression=# select column1, age(column1, '2006-11-02'), date
'2006-11-02' +
age(column1, '2006-11-02') from (values ('2007-01-31'::date),
('2007-02-01')) as alias;
column1 | age | ?column?
++-
2007-01-31 | 2 mons 29 days | 2007-01-31 00:00:00
2007-02-01 | 2 mons 30 days | 2007-02-01 00:00:00
(2 rows)
As I said earlier, I'm worried about changing the behavior of a
function
that's been around for so long, so I'm disinclined to back-patch this.
But it seems like a reasonable change to make in 8.3. Comments?
regards, tom lane
Index: timestamp.c
===
RCS file: /cvsroot/pgsql/src/backend/utils/adt/timestamp.c,v
retrieving revision 1.179
diff -c -r1.179 timestamp.c
*** timestamp.c 6 Jul 2007 04:15:59 - 1.179
--- timestamp.c 8 Jul 2007 19:45:04 -
***
*** 3044,3050
if (timestamp2tm(dt1, NULL, tm1, &fsec1, NULL, NULL) == 0 &&
timestamp2tm(dt2, NULL, tm2, &fsec2, NULL, NULL) == 0)
{
! fsec = (fsec1 - fsec2);
tm->tm_sec = tm1->tm_sec - tm2->tm_sec;
tm->tm_min = tm1->tm_min - tm2->tm_min;
tm->tm_hour = tm1->tm_hour - tm2->tm_hour;
--- 3044,3051
if (timestamp2tm(dt1, NULL, tm1, &fsec1, NULL, NULL) == 0 &&
timestamp2tm(dt2, NULL, tm2, &fsec2, NULL, NULL) == 0)
{
! /* form the symbolic difference */
! fsec = fsec1 - fsec2;
tm->tm_sec = tm1->tm_sec - tm2->tm_sec;
tm->tm_min = tm1->tm_min - tm2->tm_min;
tm->tm_hour = tm1->tm_hour - tm2->tm_hour;
***
*** 3064,3069
--- 3065,3081
tm->tm_year = -tm->tm_year;
}
+ /* propagate any negative fields into the next higher field */
+
Re: [HACKERS] [GENERAL] Unable to get postgres running after long time no vacuum
Tom Lane wrote: > "Leon Mergen" <[EMAIL PROTECTED]> writes: > > On 7/8/07, Tom Lane <[EMAIL PROTECTED]> wrote: > >> It's actually not that easy to get out of the single-user mode without > >> it doing a checkpoint. I suppose you must have either SIGQUIT or > >> SIGKILL'd it. While there's nothing we can do about SIGKILL, it strikes > >> me that it might be a good safety measure if single-user mode treated > >> SIGQUIT the same as SIGTERM, ie, non-panic shutdown. Comments anyone? > > > What I found with SIGTERM was that it did nothing, since it was still > > waiting for the (single-user) client to exit, and thus had no effect > > unless I sent an end-of-input ctrl+d singal, which would have resulted > > in a shutdown anyway. > > We might need a bit of rejiggering around the edges of the single-user > command reading code to make this work nicely, but what I'm envisioning > is that a keyboard-generated SIGQUIT ought to result in a clean > shutdown, same as EOF does. > > At least on my machine there doesn't seem to be a defined way to > generate SIGTERM from the terminal; so I can see where if someone hasn't > read the postgres man page carefully, their first instinct upon finding > that control-C doesn't get them out of single-user mode might be to type > control-\ (or whatever the local QUIT character is). It doesn't seem > like it should be quite that easy to force a panic stop. That seems reasonable, but I know an awful lot of people that don't know how to generate SIGQUIT at all (I only learned about it about a year ago, I think). In fact anyone who doesn't already know that Ctrl-D closes the session is not very likely to know about Ctrl-\. So while we should do it anyway, IMHO there's not much gain there. I think it may be good to add "exit" and "quit" commands to close the standalone session. -- Alvaro Herrera Developer, http://www.PostgreSQL.org/ "God is real, unless declared as int" ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] Unable to get postgres running after long time no vacuum
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> At least on my machine there doesn't seem to be a defined way to >> generate SIGTERM from the terminal; so I can see where if someone hasn't >> read the postgres man page carefully, their first instinct upon finding >> that control-C doesn't get them out of single-user mode might be to type >> control-\ (or whatever the local QUIT character is). It doesn't seem >> like it should be quite that easy to force a panic stop. > That seems reasonable, but I know an awful lot of people that don't know > how to generate SIGQUIT at all (I only learned about it about a year > ago, I think). In fact anyone who doesn't already know that Ctrl-D > closes the session is not very likely to know about Ctrl-\. Sure, but those who do know how to SIGQUIT might reach for that before they reach for control-D. There's hardly anyone out there who could be called an experienced user of the standalone mode, I think, and so we shouldn't assume that users always know control-D is the way out. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [GENERAL] Unable to get postgres running after long time no vacuum
Tom Lane wrote: > Alvaro Herrera <[EMAIL PROTECTED]> writes: > > Tom Lane wrote: > >> At least on my machine there doesn't seem to be a defined way to > >> generate SIGTERM from the terminal; so I can see where if someone hasn't > >> read the postgres man page carefully, their first instinct upon finding > >> that control-C doesn't get them out of single-user mode might be to type > >> control-\ (or whatever the local QUIT character is). It doesn't seem > >> like it should be quite that easy to force a panic stop. > > > That seems reasonable, but I know an awful lot of people that don't know > > how to generate SIGQUIT at all (I only learned about it about a year > > ago, I think). In fact anyone who doesn't already know that Ctrl-D > > closes the session is not very likely to know about Ctrl-\. > > Sure, but those who do know how to SIGQUIT might reach for that before > they reach for control-D. There's hardly anyone out there who could > be called an experienced user of the standalone mode, I think, and so > we shouldn't assume that users always know control-D is the way out. I agree completely, but is that an argument _against_ a "quit" command? (Maybe the argument against the quit command is feature freeze.) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [GENERAL] Unable to get postgres running after long time no vacuum
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Sure, but those who do know how to SIGQUIT might reach for that before >> they reach for control-D. There's hardly anyone out there who could >> be called an experienced user of the standalone mode, I think, and so >> we shouldn't assume that users always know control-D is the way out. > I agree completely, but is that an argument _against_ a "quit" command? No, it's orthogonal to whether we want a "quit" command. (My opinion is not, because what the heck will we do with it in multiuser mode? And there is no good way to shoehorn it into just the single-user mode, it'd have to be a grammar entry.) 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] [GENERAL] Unable to get postgres running after long time no vacuum
Tom Lane wrote: > No, it's orthogonal to whether we want a "quit" command. (My opinion is > not, because what the heck will we do with it in multiuser mode? And > there is no good way to shoehorn it into just the single-user mode, it'd > have to be a grammar entry.) Hmm, I was thinking that we could just add it in its own grammar, but then I remembered that the separate grammar is for the bootstrap mode, not standalone :-( Sorry for the noise. -- Alvaro Herrera http://www.flickr.com/photos/alvherre/ "Just treat us the way you want to be treated + some extra allowance for ignorance."(Michael Brusser) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PQescapeBytea* version for parameters
Gregory Stark <[EMAIL PROTECTED]> writes: > Do we want something like this which provides a PQescapeByteaParam for > escaping bytea strings before passing them as text-mode parameters in > PQexecParam? Seems a lot easier and more efficient to just pass out-of-line bytea parameters as binary mode. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Checkpoints and buffers that are hint-bit-dirty
"Tom Lane" <[EMAIL PROTECTED]> writes: > Gregory Stark <[EMAIL PROTECTED]> writes: >> When we checkpoint we write out all dirty buffers. But ISTM we don't really >> need to write out buffers which are dirty but which have an LSN older than >> the >> previous checkpoint. Those represent buffers which were dirtied by a >> non-wal-logged modification, ie, hint bit setting. The other non-wal-logged >> operations will sync the buffer themselves when they're done. > > In the current dispensation we don't really care how long a checkpoint > takes, so I don't see the advantage to be gained. I agree that just a shifting of i/o to the checkpoint from bgwriter isn't interesting. Saving i/o is still i/o saved -- if it doesn't shorten the checkpoint it reduces its i/o bandwidth demands. But again, I couldn't come up with any realistic scenario where the actual i/o saved is anything more than a token amount. I thought I would toss the idea up in case I was missing something. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(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
