[HACKERS] gettime() - a timeofday() alternative
Hi all, I propose to add an internal function gettime() that transparently returns the current system time, as a timestamptz with maximum precision. Calling gettime() would be a more elegant approach than calling timeofday() and converting it to a timestamp, and avoids some of the potential problems in that conversion (such as "Sat" being misinterpreted as an Australian timezone). I'm open to alternate suggestions for the name of the function. If there are no objections, I'll start cooking up a patch right away. -- BJ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] unexpected pageaddr on startup/recovery
thanks -- Original Message --- From: Tom Lane <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] Cc: "pgsql-hackers" Sent: Sat, 06 Aug 2005 17:24:46 -0400 Subject: Re: [HACKERS] unexpected pageaddr on startup/recovery > "Jim Buttafuoco" <[EMAIL PROTECTED]> writes: > > 2005-08-06 14:14:26 [3352] LOG: database system was not properly shut > > down; automatic recovery in progress > > 2005-08-06 14:14:26 [3352] LOG: redo starts at 5E5/9C6796A0 > > 2005-08-06 14:17:17 [3352] LOG: unexpected pageaddr 5E3/A7BFA000 in log > > file 1509, segment 171, offset 12558336 > > 2005-08-06 14:17:17 [3352] LOG: redo done at 5E5/ABBF978C > > > Should I worry about the unexpected pageaddr message? > > No, that looks perfectly normal. > > regards, tom lane > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org --- End of Original Message --- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Stefan Simkovics' paper and pgsql version 8.1
sql.sgml just seems to be an intro to sql/relational. The Simkovics paper is absolutely perfect for someone like me who wants to try some experiments but can't commit too much time! Does any one think time is ripe for an update to the paper??? ;) Actually I always wanted a whole book on postgresql's internals. There are several books on the internals of Linux, why not postgresql? At the minimum more colleges students will be forced to go through it by their professors :) On 8/6/05, Tom Lane <[EMAIL PROTECTED]> wrote: > Alvaro Herrera <[EMAIL PROTECTED]> writes: > > Interestingly, the paper is mentioned in the bibliography of the > > official PostgreSQL documentation. Apparently there's a good primer on > > relational theory, SQL and PostgreSQL history. > > Most of that material is still in the source tree, though it seems it's > not being built into the official docs at the moment --- see > doc/src/sgml/sql.sgml > > As a guide to hacking the backend, though, I wouldn't recommend it. > It's too outdated, and IIRC we had to rip out and redo large parts > of the original INTERSECT/EXCEPT patch. > > 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] Stefan Simkovics' paper and pgsql version 8.1
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Interestingly, the paper is mentioned in the bibliography of the > official PostgreSQL documentation. Apparently there's a good primer on > relational theory, SQL and PostgreSQL history. Most of that material is still in the source tree, though it seems it's not being built into the official docs at the moment --- see doc/src/sgml/sql.sgml As a guide to hacking the backend, though, I wouldn't recommend it. It's too outdated, and IIRC we had to rip out and redo large parts of the original INTERSECT/EXCEPT patch. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Stefan Simkovics' paper and pgsql version 8.1
On Sat, Aug 06, 2005 at 05:44:14PM -0400, Shahbaz wrote: > I would like to use postgresql for a couple of database experiments > I've been thinking about. I found Stefan Simkovic's paper > "Enhancement of the ANSI SQL Implementation of PostgreSQL." It is a > great guide, although I'm wondering: > 1. How relevant is this paper now (with release 8.1 in the works)? > 2. Have there been any other papers like that more recently? Wow, it's really outdated. It starts from 6.3.2, and from the abstract I gather that the work done was to add the HAVING clause and the INTERSECT and EXCEPT set operations. Those have been in Postgres from before I started using it, which was 7.0. A copy of the paper can be found here, for those interested http://deim.etse.urv.es/ajuda/manuals/postgresql/internals.pdf Interestingly, the paper is mentioned in the bibliography of the official PostgreSQL documentation. Apparently there's a good primer on relational theory, SQL and PostgreSQL history. -- Alvaro Herrera () Officer Krupke, what are we to do? Gee, officer Krupke, Krup you! (West Side Story, "Gee, Officer Krupke") ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Stefan Simkovics' paper and pgsql version 8.1
Hi, I would like to use postgresql for a couple of database experiments I've been thinking about. I found Stefan Simkovic's paper "Enhancement of the ANSI SQL Implementation of PostgreSQL." It is a great guide, although I'm wondering: 1. How relevant is this paper now (with release 8.1 in the works)? 2. Have there been any other papers like that more recently? Secondly, I noticed that presentations from a 204 Oreilly conference are not on posgresql's developers page. Perhaps it would be a good idea to put all these references together. (I found them after a thorough google search) Actually I didn't even know about this bizgres business. Perhaps the front page needs updating too. Lastly, I see that there is a new book "Power Postgresql" being worked on by some people who's name I remember seeing on this list (I haven't been a regular reader of this list for a long time now, unfortunately). Is that book going to be about developing the database itself or using it to develop apps.? Thanks! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] unexpected pageaddr on startup/recovery
"Jim Buttafuoco" <[EMAIL PROTECTED]> writes: > 2005-08-06 14:14:26 [3352] LOG: database system was not properly shut down; > automatic recovery in progress > 2005-08-06 14:14:26 [3352] LOG: redo starts at 5E5/9C6796A0 > 2005-08-06 14:17:17 [3352] LOG: unexpected pageaddr 5E3/A7BFA000 in log file > 1509, segment 171, offset 12558336 > 2005-08-06 14:17:17 [3352] LOG: redo done at 5E5/ABBF978C > Should I worry about the unexpected pageaddr message? No, that looks perfectly normal. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] unexpected pageaddr on startup/recovery
Hackers, I had a system crash today. When Postgresql started I had the following in my pg.log file. 2005-08-06 14:14:26 [3352] LOG: database system was interrupted at 2005-08-06 11:57:28 EDT 2005-08-06 14:14:26 [3352] LOG: checkpoint record is at 5E5/9CAEA594 2005-08-06 14:14:26 [3352] LOG: redo record is at 5E5/9C6796A0; undo record is at 0/0; shutdown FALSE 2005-08-06 14:14:26 [3352] LOG: next transaction ID: 6273726; next OID: 4274112431 2005-08-06 14:14:26 [3352] LOG: database system was not properly shut down; automatic recovery in progress 2005-08-06 14:14:26 [3352] LOG: redo starts at 5E5/9C6796A0 2005-08-06 14:17:17 [3352] LOG: unexpected pageaddr 5E3/A7BFA000 in log file 1509, segment 171, offset 12558336 2005-08-06 14:17:17 [3352] LOG: redo done at 5E5/ABBF978C Should I worry about the unexpected pageaddr message? and if so, what do I need to do select version(); version - PostgreSQL 7.4.5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.4 (Debian 1:3.3.4-6sarge1) Thanks Jim ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Enhanced containment selectivity function
Hi,
Moving it in contrib/ltree would be more difficult to me because it
depends on other functions declared in selfuncs.c
(get_restriction_variable, etc).
I'd be willing to consider exporting those functions from selfuncs.c.
In the meanwhile here is the latest patch which uses both mcv and
histogram values.
BTW, when restoring my test database I've found out that there were many
errors on ALTER INDEX "something" OWNER TO ... :
ERROR: "something" is not a table, view, or sequence
This using 8.1devel pg_restore and a 8.0.3 compressed dump. I could be
wrong, but I didn't get those errors a few days ago (some cvs updates ago).
Best regards
--
Matteo Beccati
http://phpadsnew.com/
http://phppgads.com/
Index: contrib/ltree/ltree.sql.in
===
RCS file: /projects/cvsroot/pgsql/contrib/ltree/ltree.sql.in,v
retrieving revision 1.9
diff -c -r1.9 ltree.sql.in
*** contrib/ltree/ltree.sql.in 30 Mar 2004 15:45:32 - 1.9
--- contrib/ltree/ltree.sql.in 6 Aug 2005 13:10:35 -
***
*** 230,236
RIGHTARG = ltree,
PROCEDURE = ltree_isparent,
COMMUTATOR = '<@',
! RESTRICT = contsel,
JOIN = contjoinsel
);
--- 230,236
RIGHTARG = ltree,
PROCEDURE = ltree_isparent,
COMMUTATOR = '<@',
! RESTRICT = parentsel,
JOIN = contjoinsel
);
***
*** 248,254
RIGHTARG = ltree,
PROCEDURE = ltree_risparent,
COMMUTATOR = '@>',
! RESTRICT = contsel,
JOIN = contjoinsel
);
--- 248,254
RIGHTARG = ltree,
PROCEDURE = ltree_risparent,
COMMUTATOR = '@>',
! RESTRICT = parentsel,
JOIN = contjoinsel
);
Index: src/backend/utils/adt/selfuncs.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/selfuncs.c,v
retrieving revision 1.187
diff -c -r1.187 selfuncs.c
*** src/backend/utils/adt/selfuncs.c21 Jul 2005 04:41:43 - 1.187
--- src/backend/utils/adt/selfuncs.c6 Aug 2005 13:10:46 -
***
*** 1306,1311
--- 1306,1488
return (Selectivity) selec;
}
+ #define DEFAULT_PARENT_SEL 0.001
+
+ /*
+ *parentsel - Selectivity of parent relationship
for ltree data types.
+ */
+ Datum
+ parentsel(PG_FUNCTION_ARGS)
+ {
+ PlannerInfo *root = (PlannerInfo *) PG_GETARG_POINTER(0);
+ Oid operator = PG_GETARG_OID(1);
+ List *args = (List *) PG_GETARG_POINTER(2);
+ int varRelid = PG_GETARG_INT32(3);
+ VariableStatData vardata;
+ Node *other;
+ boolvaronleft;
+ Datum *values;
+ int nvalues;
+ float4 *numbers;
+ int nnumbers;
+ double selec = 0.0;
+
+ /*
+* If expression is not variable <@ something or something <@ variable,
+* then punt and return a default estimate.
+*/
+ if (!get_restriction_variable(root, args, varRelid,
+ &vardata,
&other, &varonleft))
+ PG_RETURN_FLOAT8(DEFAULT_PARENT_SEL);
+
+ /*
+* If the something is a NULL constant, assume operator is strict and
+* return zero, ie, operator will never return TRUE.
+*/
+ if (IsA(other, Const) &&
+ ((Const *) other)->constisnull)
+ {
+ ReleaseVariableStats(vardata);
+ PG_RETURN_FLOAT8(0.0);
+ }
+
+ if (HeapTupleIsValid(vardata.statsTuple))
+ {
+ Form_pg_statistic stats;
+ double mcvsum = 0.0;
+ double mcvsel = 0.0;
+ double hissel = 0.0;
+
+ stats = (Form_pg_statistic) GETSTRUCT(vardata.statsTuple);
+
+ if (IsA(other, Const))
+ {
+ /* Variable is being compared to a known non-null
constant */
+ Datum constval = ((Const *)
other)->constvalue;
+ boolmatch = false;
+ int i;
+
+ /*
+* Is the constant "<@" to any of the column's most
common
+* values?
+*/
+ if (get_attstatsslot(vardata.statsTuple,
+
vardata.atttype, vardata.atttypmod,
+
STATISTIC_KIND_MCV, InvalidOid,
+&values,
&nvalues,
+&numbers,
&nnumbers))
+ {
[HACKERS] For Review: Allow WAL information to recover corrupted pg_controldata patch
Hi All I had added an option -r to pg_resetxlog to enable the tool can rebuild the corrupted pg_control file from the old xlog files. here is the patch. Sorry I had tried to attached it to the mail, but it failed, I dont know why, here is the link:http://www.geocities.com/yuanjia_pg/pg_resetxlog.diff.txt There are also some changes in the logic of other options.Option -n: only print out the control values in the existing pg_control file, if the file is corrupted , inform the use to rebuild it first only.Option -f: if pg_control file is fine, then reset the xlog file; if pg_control is corrupted , then try to rebuild the control file from old xlog file, if it fails, then just guessing the value, then reset the xlog file. The algorithm of restoring the pg_control value from old xlog file: 1. Retrieve all of the active xlog files from xlog directory into a list by increasing order, according their timeline, log id, segment id. (Tom had informed me that we can not know which segment file is latest just by the name itself, so before adding the segment file to the list, it should be checked that it is an active segment file.)2. Search the list to find the oldest xlog file of the latest time line. (Although it is better to let the user to select the time line which is used for rebuild the xlog file, but I think there is not so necessary. I had tried to use only the last file in the latest time line, but I found that in many cases, there are possible that the last checkpoint record and the previous checkpoint record are stored separately in different segment file, so I had to search from the oldest one.)3. Search the records from the oldest xlog file of latest time line to the latest xlog file of latest time line, if the checkpoint record has been found, update the latest checkpoint and previous checkpoint. Some of the code is borrowed from Tom Lane xlogdump.c file. Hope for your advice. Best RegardsYuanjia Lee __Do You Yahoo!?Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
