Re: [HACKERS] Open 7.4 items
Stephan Szabo [EMAIL PROTECTED] writes: Improve speed of building of constraints during restore Did we get consensus on what to do with this, Not really, it was still up in the air I thought. However, the discussion will become moot if we don't have an implementation of the faster-checking alternative to look at pretty soon. Do you have something nearly ready to show? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Thoughts on maintaining 7.3
Bruce Momjian [EMAIL PROTECTED] writes: By doing REINDEX always, we eliminate some folks are are happy doing VACUUM FULL at night, because very few tuples are expired. But if they have very few tuples expired, why do they need VACUUM FULL? Seems to me that VACUUM FULL should be designed to cater to the case of significant updates. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] COUNT(*) again (was Re: [HACKERS] Index/Function organized table layout)
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: I think that's not happening, conditionally or otherwise. The atomicity problems alone are sufficient reason why not, even before you look at the performance issues. What are the atomicity problems of adding a create/expire xid to the index tuples? You can't update a tuple's status in just one place ... you have to update the copies in the indexes too. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_dump bug in 7.4
On Sat, Oct 04, 2003 at 14:24:40 -0400, Tom Lane [EMAIL PROTECTED] wrote: Bruno Wolff III [EMAIL PROTECTED] writes: The following is still a problem in current cvs (as of 2 hours ago). Not any more ;-) Thanks. I tried it out and it is now working for me. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Index/Function organized table layout
Greg Stark kirjutas P, 05.10.2003 kell 00:17: I've never seen anyone use this feature, and I never seriously considered it myself. It sort of has the feel of an antiquated feature that traded too much flexibility and abstraction for raw performance on very slow disk hardware. Read A Conversation with Jim Gray referenced from this slashdot article: http://slashdot.org/article.pl?sid=03/09/17/1246255mode=threadtid=126 for info on how disk drives are slower than ever (relatively), and how one should treat them as such, especially for large data volumes. However I wonder if the nested tables feature doesn't use it under the hood though. It seems they would both be useful for the same types of tables. I'm not sure what this means for Postgres. I'm not sure if Postgres should use a different name to avoid confusion and possibly to leave room in the future for the possibility of supporting something like this. Or perhaps something like this would be useful for Postgres now or in the near future? Or perhaps the consensus is as I said, that this is an old idea that no longer gets any respect and postgres should just pretend it doesn't exist? We can't pretend CLUSTER does not exist until we have some better technology to offer instead. Hannu ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] Day of week question
Do to a bug in pgtypeslin I've been experimenting with the day of week
stuff a little bit and I found that we do not agree with the output of
the cal command on older dates. I have no idea which one is correct:
mm=# select to_char('1000/01/01'::date,'D');
to_char
-
4
(1 row)
Thus we believe 1000/1/1 is a Wednesday. cal says:
Januar 1000
So Mo Di Mi Do Fr Sa
1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31
Thus 1000/1/1 is a Monday. Is this a bug in cal? Or do we produce the
wrong output? Or do I simply misread the output?
Michael
--
Michael Meskes
Email: Michael at Fam-Meskes dot De
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED]
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Index/Function organized table layout
Hannu Krosing wrote: Greg Stark kirjutas P, 05.10.2003 kell 00:17: I've never seen anyone use this feature, and I never seriously considered it myself. It sort of has the feel of an antiquated feature that traded too much flexibility and abstraction for raw performance on very slow disk hardware. Read A Conversation with Jim Gray referenced from this slashdot article: http://slashdot.org/article.pl?sid=03/09/17/1246255mode=threadtid=126 for info on how disk drives are slower than ever (relatively), and how one should treat them as such, especially for large data volumes. Too bad PostgreSQL is misspelled (Postgress) and MySQL dominates the open source discussion. And the MySQL questions are coming from: David Patterson, who holds the Pardee Chair of Computer Science at the University of California at Berkeley. Outrageous! :-) Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Day of week question
Michael Meskes writes:
mm=# select to_char('1000/01/01'::date,'D');
to_char
-
4
(1 row)
Thus we believe 1000/1/1 is a Wednesday. cal says:
Thus 1000/1/1 is a Monday. Is this a bug in cal? Or do we produce the
wrong output? Or do I simply misread the output?
cal takes into account the change from the Julian to the Gregorian
calendar. PostgreSQL assumes that the Gregorian calendar is valid for all
times in the past. So both answers are correct given those assumptions.
--
Peter Eisentraut [EMAIL PROTECTED]
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [HACKERS] Day of week question
Looks like it is caused by the switch to the Gregorian calendar in 1752,
when 11 days were chopped out of September ( in England and America -
elsewhere anywhere between Oct 1582 and early 20th century). A quick scan of
the code didn't show postgres taking account of this oddity, but I might
have missed something.
cheers
andrew
- Original Message -
From: Michael Meskes [EMAIL PROTECTED]
To: PostgreSQL Hacker [EMAIL PROTECTED]
Sent: Sunday, October 05, 2003 7:06 AM
Subject: [HACKERS] Day of week question
Do to a bug in pgtypeslin I've been experimenting with the day of week
stuff a little bit and I found that we do not agree with the output of
the cal command on older dates. I have no idea which one is correct:
mm=# select to_char('1000/01/01'::date,'D');
to_char
-
4
(1 row)
Thus we believe 1000/1/1 is a Wednesday. cal says:
Januar 1000
So Mo Di Mi Do Fr Sa
1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31
Thus 1000/1/1 is a Monday. Is this a bug in cal? Or do we produce the
wrong output? Or do I simply misread the output?
Michael
--
Michael Meskes
Email: Michael at Fam-Meskes dot De
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED]
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Open 7.4 items
Tom Lane wrote: Stephan Szabo [EMAIL PROTECTED] writes: Improve speed of building of constraints during restore Did we get consensus on what to do with this, Not really, it was still up in the air I thought. However, the discussion will become moot if we don't have an implementation of the faster-checking alternative to look at pretty soon. Do you have something nearly ready to show? Last I remember, there was the idea to make ALTER TABLE use a query to check all constraints at once, rather than per row, _and_ there was an idea to turn it off by the superuser. However, if we get the first one, and it is fast, we might not even use the second one. -- 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] Thoughts on maintaining 7.3
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: By doing REINDEX always, we eliminate some folks are are happy doing VACUUM FULL at night, because very few tuples are expired. But if they have very few tuples expired, why do they need VACUUM FULL? Seems to me that VACUUM FULL should be designed to cater to the case of significant updates. Right, they could just run vacuum, and my 10% idea was bad because the vacuum full would take an unpredictable amount of time to run depending on whether it does a reindex. One idea would be to allow VACUUM, VACUUM DATA (no reindex), and VACUUM FULL (reindex). However, as you said, we might not need VACUUM DATA --- I am just not sure. -- 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]
[HACKERS] PQfnumber and quoted identifiers
There was a discussion on -interfaces that might need more consideration. http://archives.postgresql.org/pgsql-interfaces/2003-09/msg00026.php Apparently, it has so far been an undocumented feature of libpq's function PGfnumber (return column number from column name) that the column name needs to be double-quoted if it contains upper-case letters. That, is you need to write PQfnumber(res, \Bar\) I think this is completely bizarre and pointless. This is a C interface and not SQL. Other libpq functions that accept names of SQL objects don't do this. Also, PQfname and PQfnumber ought to be inverses. Since this behavior was undocumented and no one had noticed it in the last 10 years, I think we can away with removing it. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] PQfnumber and quoted identifiers
Peter Eisentraut wrote: There was a discussion on -interfaces that might need more consideration. http://archives.postgresql.org/pgsql-interfaces/2003-09/msg00026.php Apparently, it has so far been an undocumented feature of libpq's function PGfnumber (return column number from column name) that the column name needs to be double-quoted if it contains upper-case letters. That, is you need to write PQfnumber(res, \Bar\) I think this is completely bizarre and pointless. This is a C interface and not SQL. Other libpq functions that accept names of SQL objects don't do this. Also, PQfname and PQfnumber ought to be inverses. Since this behavior was undocumented and no one had noticed it in the last 10 years, I think we can away with removing it. I don't agree; you'll certainly break all libpq apps that contact databases with columns that have uppercase or special chars, and the failure might be very subtle because in most cases you wouldn't expect that this function call fails after you successfully created a rowset. There's no way how an app could determine which flavor of escaping is necessary for PQfnumber. I completely agree that PQfnumber should have been designed C-like right from the start, at least this is how C programmers would expect it. I had to learn the hard way that doesn't. While I don't have a problem with either version, IMHO now it's far too late to change the behaviour. As an alternative, a new function could be invented. BTW, I'd suggest that libpq gets a PQversion() function or macro, so that slight changes in behaviour could be taken in account on the app side if necessary. Regards, Andreas ---(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] Day of week question
Andrew Dunstan [EMAIL PROTECTED] writes: Looks like it is caused by the switch to the Gregorian calendar in 1752, when 11 days were chopped out of September ( in England and America - elsewhere anywhere between Oct 1582 and early 20th century). There was some discussion awhile back about extending PG's date code to know about the Julian calendar, but the idea pretty much died when it was pointed out that you'd need locale-specific information about exactly when the switchover occurred. SQL99 makes it perfectly clear that all datetime values are Gregorian, for example we find wording like this in the literal section: 9) If date value is specified, then it is interpreted as a date in the Gregorian calendar. So one could argue that the existing PG behavior is SQL-compliant. I tend to regard this as an easy out, but nonetheless it's an available defense if someone tries to beat you up about PG's wrong answers. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PQfnumber and quoted identifiers
[ pgsql-interfaces added to cc list ] Andreas Pflug [EMAIL PROTECTED] writes: Peter Eisentraut wrote: There was a discussion on -interfaces that might need more consideration. http://archives.postgresql.org/pgsql-interfaces/2003-09/msg00026.php Apparently, it has so far been an undocumented feature of libpq's function PGfnumber (return column number from column name) that the column name needs to be double-quoted if it contains upper-case letters. That, is you need to write PQfnumber(res, \Bar\) I think this is completely bizarre and pointless. This is a C interface and not SQL. Other libpq functions that accept names of SQL objects don't do this. Also, PQfname and PQfnumber ought to be inverses. Since this behavior was undocumented and no one had noticed it in the last 10 years, I think we can away with removing it. I don't agree; you'll certainly break all libpq apps that contact databases with columns that have uppercase or special chars, and the failure might be very subtle because in most cases you wouldn't expect that this function call fails after you successfully created a rowset. That was pretty much the argument that carried the day in the earlier thread. However, I'm not sure how many people really use PQfnumber (as opposed to hard-wiring assumptions about returned column numbers), and it would seem that the intersection of those people with people who use mixed-case column names may be nearly the empty set. If a lot of people did this, the behavior would have been discussed and documented (or changed) long ago. So I'm not convinced that we'd really break very many apps by changing to the behavior that everyone seems to agree is more sensible. A data point is that we did make comparable changes to the handling of database names a couple releases ago, and we got few if any gripes. Another data point is that the original Berkeley coding of PQfnumber did not have the case folding/dequoting behavior. The history seems to be: Original code: straight strcmp() of argument against returned column name 1997-05-19 23:38: replace strcmp() with strcasecmp() (no dequoting logic, pretty obviously a broken idea in hindsight) 1997-11-10 00:10: attempted to implement the current behavior of dequoting+downcasing, but due to a typo, the actual effect was to revert the behavior to exact match 1999-02-03 15:19: fix typo, installing the current behavior So other than the shortlived 6.2 release, releases before 6.5 had the behavior Peter wants. I find it interesting that it took more than a year for anyone to notice that the putative dequoting+downcasing logic installed for 6.3 didn't work. regards, tom lane ---(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] PQfnumber and quoted identifiers
Tom Lane wrote: That was pretty much the argument that carried the day in the earlier thread. However, I'm not sure how many people really use PQfnumber (as opposed to hard-wiring assumptions about returned column numbers), and it would seem that the intersection of those people with people who use mixed-case column names may be nearly the empty set. If a lot of people did this, the behavior would have been discussed and documented (or changed) long ago. So I'm not convinced that we'd really break very many apps by changing to the behavior that everyone seems to agree is more sensible. ... So other than the shortlived 6.2 release, releases before 6.5 had the behavior Peter wants. I find it interesting that it took more than a year for anyone to notice that the putative dequoting+downcasing logic installed for 6.3 didn't work. pgAdmin3 beta testers found it... If you change it, please give me a chance to code it version-aware so quoting/non-quoting can be performed dependent on libpq in use. Regards, Andreas ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] PQfnumber and quoted identifiers
On Sun, 2003-10-05 at 13:43, Andreas Pflug wrote: Tom Lane wrote: So other than the shortlived 6.2 release, releases before 6.5 had the behavior Peter wants. I find it interesting that it took more than a year for anyone to notice that the putative dequoting+downcasing logic installed for 6.3 didn't work. pgAdmin3 beta testers found it... If you change it, please give me a chance to code it version-aware so quoting/non-quoting can be performed dependent on libpq in use. Is there a reason why it can't accecpt both formats? That way PQfname and PQfnumber could be inverses of each other. The only wart is that PQfnumber would also accecpt the \Bar\ format also. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] PQfnumber and quoted identifiers
Peter Eisentraut wrote: There was a discussion on -interfaces that might need more consideration. http://archives.postgresql.org/pgsql-interfaces/2003-09/msg00026.php Apparently, it has so far been an undocumented feature of libpq's function PGfnumber (return column number from column name) that the column name needs to be double-quoted if it contains upper-case letters. That, is you need to write PQfnumber(res, \Bar\) I think this is completely bizarre and pointless. This is a C interface and not SQL. Other libpq functions that accept names of SQL objects don't do this. Also, PQfname and PQfnumber ought to be inverses. Since this behavior was undocumented and no one had noticed it in the last 10 years, I think we can away with removing it. I agree. I would never expect to add quotes to a C string to preserve case (outside SQL), and the fact that PQfname doesn't return the string in quotes is another inconsistency. It is sort of like case coming out is significant, but case going in has to be quoted --- am I getting this right? Do we do this in other areas? -- 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] PQfnumber and quoted identifiers
Andreas Pflug [EMAIL PROTECTED] writes: If you change it, please give me a chance to code it version-aware so quoting/non-quoting can be performed dependent on libpq in use. If you want a run-time test, the most reliable way would be to directly test what PQfnumber does --- for instance, make a query known to contain the column name foo, and see what PQfnumber(res, \foo\) returns. I'm not sure there is any other solution, since we can't retroactively install a version identifier in old libpq releases. For a compile-time test, you could perhaps look to see if PG_DIAG_SQLSTATE or one of the other new macros in postgres_ext.h is defined. Not sure you really want a compile-time test though; it'd break very easily if you get linked against some other version of the library. As for the more general question of whether to offer libpq version identification going forward, I have no strong opinion on whether it's really useful or not. If it's wanted, I'm tempted to suggest that PQparameterStatus() could be extended to recognize libpq_version paralleling server_version. Not sure about a clean way to expose the version at compile time. regards, tom lane ---(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] PQfnumber and quoted identifiers
Matthew T. O'Connor [EMAIL PROTECTED] writes: Is there a reason why it can't accecpt both formats? Does it downcase FOO or not? You can't have it both ways. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PQfnumber and quoted identifiers
Bruce Momjian [EMAIL PROTECTED] writes: Peter Eisentraut wrote: Since this behavior was undocumented and no one had noticed it in the last 10 years, I think we can away with removing it. I agree. I would never expect to add quotes to a C string to preserve case (outside SQL), and the fact that PQfname doesn't return the string in quotes is another inconsistency. Actually I was planning to quiz you about the history. I can see from the CVS logs that you installed all the patches that added the quoting/downcasing behavior. Was there any discussion about it? I dug through the mail archives and found http://archives.postgresql.org/pgsql-ports/1997-05/msg00081.php http://archives.postgresql.org/pgsql-bugs/1997-05/msg00023.php http://archives.postgresql.org/pgsql-hackers/1997-11/msg00170.php but I could not find any actual discussion about whether there was a real bug or whether the complainants should be told to fix their code. regards, tom lane ---(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] Open 7.4 items
On Sun, 5 Oct 2003, Tom Lane wrote:
Stephan Szabo [EMAIL PROTECTED] writes:
Improve speed of building of constraints during restore
Did we get consensus on what to do with this,
Not really, it was still up in the air I thought. However, the
discussion will become moot if we don't have an implementation
of the faster-checking alternative to look at pretty soon. Do you
have something nearly ready to show?
It's not cleaned up, but yes. It appears to work for the simple tests I've
done and should fall back if the permissions don't work to do a single
query on both tables.
I wasn't sure what to do about some of the spi error conditions. For many
of them I'm just returning false now so that it will try the other
mechanism in case that might work. I'm not really sure if that's worth it,
or if I should just elog(ERROR) and give up.Index: src/backend/commands/tablecmds.c
===
RCS file: /projects/cvsroot/pgsql-server/src/backend/commands/tablecmds.c,v
retrieving revision 1.85
diff -c -r1.85 tablecmds.c
*** src/backend/commands/tablecmds.c2 Oct 2003 06:36:37 - 1.85
--- src/backend/commands/tablecmds.c5 Oct 2003 18:30:43 -
***
*** 3437,3442
--- 3437,3443
return indexoid;
}
+
/*
* Scan the existing rows in a table to verify they meet a proposed FK
* constraint.
***
*** 3454,3531
List *list;
int count;
! /*
!* Scan through each tuple, calling RI_FKey_check_ins (insert trigger)
!* as if that tuple had just been inserted. If any of those fail, it
!* should ereport(ERROR) and that's that.
!*/
! MemSet(trig, 0, sizeof(trig));
! trig.tgoid = InvalidOid;
! trig.tgname = fkconstraint-constr_name;
! trig.tgenabled = TRUE;
! trig.tgisconstraint = TRUE;
! trig.tgconstrrelid = RelationGetRelid(pkrel);
! trig.tgdeferrable = FALSE;
! trig.tginitdeferred = FALSE;
!
! trig.tgargs = (char **) palloc(sizeof(char *) *
! (4 +
length(fkconstraint-fk_attrs)
! +
length(fkconstraint-pk_attrs)));
!
! trig.tgargs[0] = trig.tgname;
! trig.tgargs[1] = RelationGetRelationName(rel);
! trig.tgargs[2] = RelationGetRelationName(pkrel);
! trig.tgargs[3] = fkMatchTypeToString(fkconstraint-fk_matchtype);
! count = 4;
! foreach(list, fkconstraint-fk_attrs)
! {
! char *fk_at = strVal(lfirst(list));
!
! trig.tgargs[count] = fk_at;
! count += 2;
! }
! count = 5;
! foreach(list, fkconstraint-pk_attrs)
! {
! char *pk_at = strVal(lfirst(list));
!
! trig.tgargs[count] = pk_at;
! count += 2;
! }
! trig.tgnargs = count - 1;
!
! scan = heap_beginscan(rel, SnapshotNow, 0, NULL);
!
! while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
! {
! FunctionCallInfoData fcinfo;
! TriggerData trigdata;
!
! /*
!* Make a call to the trigger function
!*
!* No parameters are passed, but we do set a context
!*/
! MemSet(fcinfo, 0, sizeof(fcinfo));
!
/*
!* We assume RI_FKey_check_ins won't look at flinfo...
*/
! trigdata.type = T_TriggerData;
! trigdata.tg_event = TRIGGER_EVENT_INSERT | TRIGGER_EVENT_ROW;
! trigdata.tg_relation = rel;
! trigdata.tg_trigtuple = tuple;
! trigdata.tg_newtuple = NULL;
! trigdata.tg_trigger = trig;
! fcinfo.context = (Node *) trigdata;
!
! RI_FKey_check_ins(fcinfo);
}
-
- heap_endscan(scan);
-
- pfree(trig.tgargs);
}
/*
--- 3455,3533
List *list;
int count;
! if (!RI_Check_Table(fkconstraint, rel, pkrel)) {
/*
!* Scan through each tuple, calling RI_FKey_check_ins (insert trigger)
!* as if that tuple had just been inserted. If any of those fail, it
!* should ereport(ERROR) and that's that.
*/
! MemSet(trig, 0, sizeof(trig));
! trig.tgoid = InvalidOid;
! trig.tgname = fkconstraint-constr_name;
! trig.tgenabled = TRUE;
! trig.tgisconstraint = TRUE;
! trig.tgconstrrelid = RelationGetRelid(pkrel);
! trig.tgdeferrable = FALSE;
! trig.tginitdeferred = FALSE;
!
! trig.tgargs = (char **) palloc(sizeof(char *) *
!
Re: [HACKERS] Open 7.4 items
Stephan Szabo [EMAIL PROTECTED] writes: On Sun, 5 Oct 2003, Tom Lane wrote: Not really, it was still up in the air I thought. However, the discussion will become moot if we don't have an implementation of the faster-checking alternative to look at pretty soon. Do you have something nearly ready to show? It's not cleaned up, but yes. It appears to work for the simple tests I've done and should fall back if the permissions don't work to do a single query on both tables. Okay, I'll look this over, make any improvements I can think of, and post another version in a couple of hours. One thing I can see I'd like to do is merge the error-reporting code with the main line, so that there's not any difference in the output format (I don't like the induced change in the regression test outputs...) regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PQfnumber and quoted identifiers
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Peter Eisentraut wrote: Since this behavior was undocumented and no one had noticed it in the last 10 years, I think we can away with removing it. I agree. I would never expect to add quotes to a C string to preserve case (outside SQL), and the fact that PQfname doesn't return the string in quotes is another inconsistency. Actually I was planning to quiz you about the history. I can see from the CVS logs that you installed all the patches that added the quoting/downcasing behavior. Was there any discussion about it? I dug through the mail archives and found http://archives.postgresql.org/pgsql-ports/1997-05/msg00081.php http://archives.postgresql.org/pgsql-bugs/1997-05/msg00023.php http://archives.postgresql.org/pgsql-hackers/1997-11/msg00170.php but I could not find any actual discussion about whether there was a real bug or whether the complainants should be told to fix their code. Wow, 1997 --- seems like a time long ago. As I remember, I crudely coded up a fix for the complaint --- that's the way we did it back then. :-) There probably wasn't a lot of discussion, if any --- people complained, and we coded to fix the complaint. In hindsight, I should have told them the API was working properly, but the idea of telling a user they were wrong wasn't something we did back then --- we needed every user we could get. My guess is that the email reports from May, 1997 were in my mailbox, and in Novemeber I saw it again (I was a volunteer then) and I coded up the fix, posted it to the lists, then applied it. No one said anything about it, so it stayed in. Strange no one complained about it until now. I suppose that is because few folks use that function _and_ upper-case identifiers, as you mentioned. We could code the proper behavior, post a mention on general, and document it in the release notes, or wait for 7.5, or do nothing. -- 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Thoughts on maintaining 7.3
On Sat, Oct 04, 2003 at 11:53:49PM -0400, Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Imagine having to VACUUM FULL a huge table. Not only it will take the lot required to do the VACUUM in the heap itself, it will also have to rebuild all indexes from scratch. A very large chunk of VACUUM FULL's runtime is spent fooling with the indexes. Have you looked at the code in any detail? It goes like this: Hmm. No, I haven't looked at that code too much. You are probably right, of course. Maybe the indexes could be dropped altogether and then recreated after the vacuum is over, similar to what the cluster code does. This would be similar to REINDEX, I suppose. (I haven't actually looked at the REINDEX code either.) I think there are scenarios where the REINDEX will be much worse, say when there are not too many deleted tuples (but in that case, why is the user doing VACUUM FULL in the first place?). Yeah, I think that's exactly the important point. These days there's not a lot of reason to do VACUUM FULL unless you have a major amount of restructuring to do. I would once have favored maintaining two code paths with two strategies, but now I doubt it's worth the trouble. (Or I should say, we have two code paths, the other being lazy VACUUM --- do we need three?) There are two points that could be made here: 1. We do not want users having to think too hard about what kind of VACUUM they want. This probably botches Bruce's idea of an additional VACUUM DATA command. 2. We do not want to expose the VACUUM command family at all. The decisions about what code paths should be taken are best left to the backend-integrated vacuum daemon, which has probably much better information than users. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) You knock on that door or the sun will be shining on places inside you that the sun doesn't usually shine (en Death: The High Cost of Living) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PQfnumber and quoted identifiers
-Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED] Sent: 05 October 2003 20:41 To: Tom Lane Cc: Peter Eisentraut; PostgreSQL Development Subject: Re: [HACKERS] PQfnumber and quoted identifiers Strange no one complained about it until now. I suppose that is because few folks use that function _and_ upper-case identifiers, as you mentioned. We could code the proper behavior, post a mention on general, and document it in the release notes, or wait for 7.5, or do nothing. Can it wait for 7.5 (and some method of checking the libpq versions such as Tom's PQparameterStatus() suggestion) please? We already released pgAdmin III taking into account this behaviour in 7.4 and a change now would not be so good for us. Regards, Dave. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PQfnumber and quoted identifiers
Dave Page wrote: -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED] Sent: 05 October 2003 20:41 To: Tom Lane Cc: Peter Eisentraut; PostgreSQL Development Subject: Re: [HACKERS] PQfnumber and quoted identifiers Strange no one complained about it until now. I suppose that is because few folks use that function _and_ upper-case identifiers, as you mentioned. We could code the proper behavior, post a mention on general, and document it in the release notes, or wait for 7.5, or do nothing. Can it wait for 7.5 (and some method of checking the libpq versions such as Tom's PQparameterStatus() suggestion) please? We already released pgAdmin III taking into account this behaviour in 7.4 and a change now would not be so good for us. It is something we could clearly advertise as changing in 7.5. Wasn't there something else we planned to change in 7.5. I forgot it. -- 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] Open 7.4 items
Stephan Szabo [EMAIL PROTECTED] writes:
It's not cleaned up, but yes. It appears to work for the simple tests I've
done and should fall back if the permissions don't work to do a single
query on both tables.
Here's my code-reviewed version of the patch. Anyone else want to take
a look?
I wasn't sure what to do about some of the spi error conditions. For many
of them I'm just returning false now so that it will try the other
mechanism in case that might work. I'm not really sure if that's worth it,
or if I should just elog(ERROR) and give up.
I think you may as well keep it the same as the other RI routines and
just elog() on SPI error. If SPI is broken, the trigger procedure is
gonna fail too.
I changed that, consolidated the error-reporting code, and fixed a couple
other little issues, notably:
* The generated query applied ONLY to the FK table but not the PK table.
I assume this was just an oversight.
* The query is now run using SPI_execp_current and selecting current
snapshot. Without this, we could fail in a serializable transaction
if someone else has already committed changes to either relation.
For example:
create pk and fk tables;
begin serializable xact;
insert into pk values(1);
insert into fk values(1);
begin;
insert into fk values(2);
commit;
alter table fk add foreign key ...;
The ALTER will not be blocked from acquiring exclusive lock, since
T2 already committed. But if we run the query in the serializable
snapshot, it won't see the violating row fk=2.
The old trigger-based check avoids this error because the scan loop uses
SnapshotNow to select live rows from the FK table. There is a dual race
condition where T2 deletes a row from the PK table. In current CVS tip
this will be detected and reported as a serialization failure, because
T1 won't be able to get SELECT FOR UPDATE lock on the deleted row. With
the proposed patch you'll instead see a no such key failure, which I
think is fine, even though it nominally violates serializability.
Comments? Can anyone else do a code review (Jan??)?
regards, tom lane
*** src/backend/commands/tablecmds.c.orig Thu Oct 2 15:24:52 2003
--- src/backend/commands/tablecmds.cSun Oct 5 16:29:51 2003
***
*** 3455,3460
--- 3455,3467
int count;
/*
+* See if we can do it with a single LEFT JOIN query. A FALSE result
+* indicates we must proceed with the fire-the-trigger method.
+*/
+ if (RI_Initial_Check(fkconstraint, rel, pkrel))
+ return;
+
+ /*
* Scan through each tuple, calling RI_FKey_check_ins (insert trigger)
* as if that tuple had just been inserted. If any of those fail, it
* should ereport(ERROR) and that's that.
*** src/backend/utils/adt/ri_triggers.c.origWed Oct 1 17:30:52 2003
--- src/backend/utils/adt/ri_triggers.c Sun Oct 5 16:42:37 2003
***
*** 40,45
--- 40,46
#include rewrite/rewriteHandler.h
#include utils/lsyscache.h
#include utils/typcache.h
+ #include utils/acl.h
#include miscadmin.h
***
*** 164,170
Datum *vals, char *nulls);
static void ri_ReportViolation(RI_QueryKey *qkey, const char *constrname,
Relation pk_rel, Relation fk_rel,
! HeapTuple violator, bool spi_err);
/* --
--- 165,172
Datum *vals, char *nulls);
static void ri_ReportViolation(RI_QueryKey *qkey, const char *constrname,
Relation pk_rel, Relation fk_rel,
! HeapTuple violator, TupleDesc tupdesc,
! bool spi_err);
/* --
***
*** 2540,2546
--- 2542,2743
}
+ /* --
+ * RI_Initial_Check -
+ *
+ *Check an entire table for non-matching values using a single query.
+ *This is not a trigger procedure, but is called during ALTER TABLE
+ *ADD FOREIGN KEY to validate the initial table contents.
+ *
+ *We expect that an exclusive lock has been taken on rel and pkrel;
+ *hence, we do not need to lock individual rows for the check.
+ *
+ *If the check fails because the current user doesn't have permissions
+ *to read both tables, return false to let our caller know that they will
+ *need to do something else to check the constraint.
+ * --
+ */
+ bool
+ RI_Initial_Check(FkConstraint *fkconstraint, Relation rel, Relation pkrel)
+ {
+ const char *constrname = fkconstraint-constr_name;
+ charquerystr[MAX_QUOTED_REL_NAME_LEN * 2 + 250 +
+
Re: [HACKERS] Open 7.4 items
Wow, that's a heap of code --- that's my only comment. :-) --- Tom Lane wrote: Stephan Szabo [EMAIL PROTECTED] writes: It's not cleaned up, but yes. It appears to work for the simple tests I've done and should fall back if the permissions don't work to do a single query on both tables. Here's my code-reviewed version of the patch. Anyone else want to take a look? I wasn't sure what to do about some of the spi error conditions. For many of them I'm just returning false now so that it will try the other mechanism in case that might work. I'm not really sure if that's worth it, or if I should just elog(ERROR) and give up. I think you may as well keep it the same as the other RI routines and just elog() on SPI error. If SPI is broken, the trigger procedure is gonna fail too. I changed that, consolidated the error-reporting code, and fixed a couple other little issues, notably: * The generated query applied ONLY to the FK table but not the PK table. I assume this was just an oversight. * The query is now run using SPI_execp_current and selecting current snapshot. Without this, we could fail in a serializable transaction if someone else has already committed changes to either relation. For example: create pk and fk tables; begin serializable xact; insert into pk values(1); insert into fk values(1); begin; insert into fk values(2); commit; alter table fk add foreign key ...; The ALTER will not be blocked from acquiring exclusive lock, since T2 already committed. But if we run the query in the serializable snapshot, it won't see the violating row fk=2. The old trigger-based check avoids this error because the scan loop uses SnapshotNow to select live rows from the FK table. There is a dual race condition where T2 deletes a row from the PK table. In current CVS tip this will be detected and reported as a serialization failure, because T1 won't be able to get SELECT FOR UPDATE lock on the deleted row. With the proposed patch you'll instead see a no such key failure, which I think is fine, even though it nominally violates serializability. Comments? Can anyone else do a code review (Jan??)? regards, tom lane Content-Description: RIcheck.patch *** src/backend/commands/tablecmds.c.orig Thu Oct 2 15:24:52 2003 --- src/backend/commands/tablecmds.c Sun Oct 5 16:29:51 2003 *** *** 3455,3460 --- 3455,3467 int count; /* + * See if we can do it with a single LEFT JOIN query. A FALSE result + * indicates we must proceed with the fire-the-trigger method. + */ + if (RI_Initial_Check(fkconstraint, rel, pkrel)) + return; + + /* * Scan through each tuple, calling RI_FKey_check_ins (insert trigger) * as if that tuple had just been inserted. If any of those fail, it * should ereport(ERROR) and that's that. *** src/backend/utils/adt/ri_triggers.c.orig Wed Oct 1 17:30:52 2003 --- src/backend/utils/adt/ri_triggers.c Sun Oct 5 16:42:37 2003 *** *** 40,45 --- 40,46 #include rewrite/rewriteHandler.h #include utils/lsyscache.h #include utils/typcache.h + #include utils/acl.h #include miscadmin.h *** *** 164,170 Datum *vals, char *nulls); static void ri_ReportViolation(RI_QueryKey *qkey, const char *constrname, Relation pk_rel, Relation fk_rel, !HeapTuple violator, bool spi_err); /* -- --- 165,172 Datum *vals, char *nulls); static void ri_ReportViolation(RI_QueryKey *qkey, const char *constrname, Relation pk_rel, Relation fk_rel, !HeapTuple violator, TupleDesc tupdesc, !bool spi_err); /* -- *** *** 2540,2546 --- 2542,2743 } + /* -- + * RI_Initial_Check - + * + * Check an entire table for non-matching values using a single query. + * This is not a trigger procedure, but is called during ALTER TABLE + * ADD FOREIGN KEY to validate the initial table contents. + * + * We expect that an exclusive lock has been taken on rel and pkrel; + * hence, we do not need to lock individual rows for the check. + * + * If the check fails because the current user doesn't have permissions + * to read both tables, return false to let our caller know that they will + * need to do something else to check the constraint. + * --
Re: [HACKERS] Open 7.4 items
Bruce Momjian [EMAIL PROTECTED] writes: Wow, that's a heap of code --- that's my only comment. :-) Most of it is pretty direct cribbing of code that already exists in the other routines in ri_triggers.c, so it's not really completely new code, just boilerplate. regards, tom lane ---(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
[HACKERS] Learning PostgreSQL
Dear PostgreSQL masters, I know this might look like a childish question and you probably might have a good laugh over this but I would like to learn how PostgreSQL works inside-out. Could anyone please give me some pointers of where to start in/from the source code? I am grateful for any help. Gevik. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Open 7.4 items
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Wow, that's a heap of code --- that's my only comment. :-) Most of it is pretty direct cribbing of code that already exists in the other routines in ri_triggers.c, so it's not really completely new code, just boilerplate. Oh, that makes me feel better. Do we have timings for this code? -- 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] PQfnumber and quoted identifiers
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: It is something we could clearly advertise as changing in 7.5. I think assuming that advertising a planned change will accomplish much is just fooling ourselves :-(. There'll be approximately the same number of complaints either way, because people won't fix their code in advance (indeed can't, unless we provide a version inquiry method now). I'm willing to bow to Dave's schedule-based concern about not doing it in 7.4, though. Agreed. We can add it to TODO. That is advertising the change. -- 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 8: explain analyze is your friend
Re: [HACKERS] Learning PostgreSQL
Gevik Babakhani wrote: Dear PostgreSQL masters, I know this might look like a childish question and you probably might have a good laugh over this but I would like to learn how PostgreSQL works inside-out. Could anyone please give me some pointers of where to start in/from the source code? Sure, check out the developers web page. It has some basic outlines, and an FAQ. After that, it is mostly digging. You will need an editor that supports tags or some way to pull symbols out of the code, and hopefully cross-references. -- 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 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] PQfnumber and quoted identifiers
Bruce Momjian [EMAIL PROTECTED] writes: Agreed. We can add it to TODO. That is advertising the change. I'd be inclined to put something in the SGML docs describing PQfnumber(), also. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Open 7.4 items
On Sun, 5 Oct 2003, Bruce Momjian wrote: Wow, that's a heap of code --- that's my only comment. :-) And you reposted the *whole* patch for that?? *tsk* *tsk* ---(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] Learning PostgreSQL
On Sun, 2003-10-05 at 17:21, Bruce Momjian wrote: Sure, check out the developers web page. It has some basic outlines, and an FAQ. After that, it is mostly digging. You will need an editor that supports tags or some way to pull symbols out of the code, and hopefully cross-references. Depending on what part of the source you're interested in, a book on DBMS implementation might also be useful, such as http://www.amazon.com/exec/obidos/tg/detail/-/0072465638/qid=1065389816/sr=8-1/ref=sr_8_1/104-3447470-6475940?v=glances=booksn=507846 -Neil ---(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] Thoughts on maintaining 7.3
Alvaro Herrera wrote:
Yeah, I think that's exactly the important point. These days there's
not a lot of reason to do VACUUM FULL unless you have a major amount of
restructuring to do. I would once have favored maintaining two code
paths with two strategies, but now I doubt it's worth the trouble.
(Or I should say, we have two code paths, the other being lazy VACUUM
--- do we need three?)
There are two points that could be made here:
1. We do not want users having to think too hard about what kind of
VACUUM they want. This probably botches Bruce's idea of an additional
VACUUM DATA command.
2. We do not want to expose the VACUUM command family at all. The
decisions about what code paths should be taken are best left to the
backend-integrated vacuum daemon, which has probably much better
information than users.
Agreed. We need to head in a direction where vacuum is automatic. I
guess the question is whether an automatic method would ever user VACUUM
DATA?
I just did a simple test. I did:
test= CREATE TABLE test (x INT, y TEXT);
CREATE TABLE
test= INSERT INTO test VALUES (1, 'lk;jasdflkjlkjawsiopfjqwerfokjasdflkj');
INSERT 17147 1
test= INSERT INTO test SELECT * FROM test;
{ repeat until 65k rows are inserted, so there are 131k rows}
test= INSERT INTO test SELECT 2, y FROM test;
INSERT 0 131072
test= DELETE FROM test WHERE x=1;
DELETE 131072
test= \timing
Timing is on.
test= VACUUM FULL;
VACUUM
Time: 4661.82 ms
test= INSERT INTO test SELECT 3, y FROM test;
INSERT 0 131072
Time: 7925.57 ms
test= CREATE INDEX i ON test(x);
CREATE INDEX
Time: 3337.96 ms
test= DELETE FROM test WHERE x=2;
DELETE 131072
Time: 3204.18 ms
test= VACUUM FULL;
VACUUM
Time: 10523.69 ms
test= REINDEX TABLE test;
REINDEX
Time: 2193.14 ms
Now, as I understand it, this is the worst-case for VACUUM FULL. What
we have here is 4661.82 for VACUUM FULL without an index, and 10523.69
for VACUUM FULL with an index, and REINDEX takes 2193.14. If we assume
VACUUM FULL with REINDEX will equal the time of VACUUM without the index
plus the REINDEX time, we have 4661.82 + 2193.14, or 6854.96 vs.
10523.69, so clearly VACUUM REINDEX is a win for this case. What I
don't know is what percentage of a table has to be expired for REINDEX
to be a win. I assume if only one row is expired, you get 4661.82 +
2193.14 vs. just 4661.82, roughly.
--
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 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Learning PostgreSQL
Neil Conway wrote: On Sun, 2003-10-05 at 17:21, Bruce Momjian wrote: Sure, check out the developers web page. It has some basic outlines, and an FAQ. After that, it is mostly digging. You will need an editor that supports tags or some way to pull symbols out of the code, and hopefully cross-references. Depending on what part of the source you're interested in, a book on DBMS implementation might also be useful, such as http://www.amazon.com/exec/obidos/tg/detail/-/0072465638/qid=1065389816/sr=8-1/ref=sr_8_1/104-3447470-6475940?v=glances=booksn=507846 Wow, $100. Does it cover internals? -- 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 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Open 7.4 items
Marc G. Fournier wrote: On Sun, 5 Oct 2003, Bruce Momjian wrote: Wow, that's a heap of code --- that's my only comment. :-) And you reposted the *whole* patch for that?? *tsk* *tsk* Oops, sorry. -- 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Learning PostgreSQL
On Sun, 5 Oct 2003, Gevik Babakhani wrote: Dear PostgreSQL masters, I know this might look like a childish question and you probably might have a good laugh over this but I would like to learn how PostgreSQL works inside-out. Could anyone please give me some pointers of where to start in/from the source code? I am grateful for any help. As Bruce says, the developers website (http://developer.postgresql.org/ - I think). On the other hand I'd suggest a good read of the user documentation first. Get an idea of what it can do from the user perspective then start poking around inside. When it comes to poking around inside a decent start might be to take a look at one of the PLs and contrib items. -- Nigel ---(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] pg_dump and REVOKE on function
Fixed.
Bruce Momjian writes:
This item has been added to the 7.4 open items list:
ftp://momjian.postgresql.org/pub/postgresql/open_items
---
Rod Taylor wrote:
-- Start of PGP signed section.
Below is output from 7.3 pg_dump that is being loaded into 7.4 beta1.
It would seem that revoking the permissions of the owner doesn't work
out so well.
r=# CREATE FUNCTION weekdate (date) RETURNS timestamp with time zone
r-# AS '
r'# SELECT cast(to_date(''01 01 ''|| extract(''year'' FROM $1), ''DD MM
'') +
r'# (cast(extract(''week'' FROM $1) AS numeric) *7-8) * interval ''1
day'' as timestamp with time zone);'
r-# LANGUAGE sql;
CREATE FUNCTION
r=#
r=#
r=# --
r=# -- TOC entry 752 (OID 18968885)
r=# -- Name: weekdate (date); Type: ACL; Schema: public; Owner: rbt
r=# --
r=#
r=# REVOKE ALL ON FUNCTION weekdate (date) FROM PUBLIC;
REVOKE
r=# GRANT ALL ON FUNCTION weekdate (date) TO PUBLIC;
GRANT
r=# REVOKE ALL ON FUNCTION weekdate (date) FROM rbt;
ERROR: dependent privileges exist
HINT: Use CASCADE to revoke them too.
-- End of PGP section, PGP failed!
--
Peter Eisentraut [EMAIL PROTECTED]
---(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] Day of week question
Tom Lane wrote:
Andrew Dunstan [EMAIL PROTECTED] writes:
Looks like it is caused by the switch to the Gregorian calendar in 1752,
when 11 days were chopped out of September ( in England and America -
elsewhere anywhere between Oct 1582 and early 20th century).
There was some discussion awhile back about extending PG's date code
to know about the Julian calendar, but the idea pretty much died when
it was pointed out that you'd need locale-specific information about
exactly when the switchover occurred.
SQL99 makes it perfectly clear that all datetime values are Gregorian,
for example we find wording like this in the literal section:
9) If date value is specified, then it is interpreted as a date
in the Gregorian calendar.
So one could argue that the existing PG behavior is SQL-compliant.
I tend to regard this as an easy out, but nonetheless it's an available
defense if someone tries to beat you up about PG's wrong answers.
Perhaps we need a function or two to convert pre-gregorian dates to
gregorian dates and vice versa, with the cutover date either a
configuration variable (default the 1752 date) or a parameter of the
function.
e.g. j_to_g('1700-01-01'::date) = '1700-01-13'
j_to_g('1800-01-01'::date) = '1800-01-01'
just a thought
andrew
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Learning PostgreSQL
On Sun, 2003-10-05 at 17:45, Bruce Momjian wrote: Neil Conway wrote: Depending on what part of the source you're interested in, a book on DBMS implementation might also be useful, such as Wow, $100. Well, it's a CS textbook -- I have several textbooks this semester that are $100. Does it cover internals? Yeah, although it's more of a broad survey of DB-related topics, so the internals coverage isn't that in-depth. It talks about storage/indexing (the heap, ISAM/B+-tree indexes, hash indexes, etc.), query evaluation, query optimization, transaction management concurrency control. That book just happens to be the one on my desk, but there are plenty of alternatives that cover the same subject matter. Perhaps you could add this to the developer's FAQ? -Neil ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] more on initdb
Is there any reason other than historical that the System Views setup isn't a separate script fed to postgres by initdb, like, say, the information schema file? If there isn't a good reason should we unwire it as part of moving to a C version of initdb? cheers andrew ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Learning PostgreSQL
On Sun, Oct 05, 2003 at 11:15:56PM +0200, Gevik Babakhani wrote: Gevik, I know this might look like a childish question and you probably might have a good laugh over this but I would like to learn how PostgreSQL works inside-out. Could anyone please give me some pointers of where to start in/from the source code? You probably won't hear too many laughs. I don't think it's a childish question myself. The best place to start is probably Bruce Momjian's presentations. You can find some of them at http://developer.postgresql.org. Make sure you read the Flowchart of the PostgreSQL backend and follow all the links you can find there. Also read the whole Internals chapter in the documentation, at http://candle.pha.pa.us/main/writings/pgsql/sgml/internals.html Then you will probably need to poke at the README files spread through the source code. After that, there probably isn't much else short of reading the actual source code. If you like it, we will be hopefully hearing back from you again as you try to contribute something... -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Aprender sin pensar es inútil; pensar sin aprender, peligroso (Confucio) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Learning PostgreSQL
Neil Conway wrote: On Sun, 2003-10-05 at 17:45, Bruce Momjian wrote: Neil Conway wrote: Depending on what part of the source you're interested in, a book on DBMS implementation might also be useful, such as Wow, $100. Well, it's a CS textbook -- I have several textbooks this semester that are $100. Does it cover internals? Yeah, although it's more of a broad survey of DB-related topics, so the internals coverage isn't that in-depth. It talks about storage/indexing (the heap, ISAM/B+-tree indexes, hash indexes, etc.), query evaluation, query optimization, transaction management concurrency control. That book just happens to be the one on my desk, but there are plenty of alternatives that cover the same subject matter. Perhaps you could add this to the developer's FAQ? Yes, is this the book we should recommend? I know we have Gray's transaction book on there already. -- 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] Cannot dump/restore text value \N
To be clear, this is not about \N as the default external
representation for NULL, I'm talking about a string consisting of the
two characters backslash and uppercase-N.
CREATE TABLE nonu (tx text NOT NULL);
INSERT INTO nonu VALUES ('\\N');
SELECT * FROM nonu;
COPY nonu TO stdout;
This correctly gives:
\\N
Now try to feed that back into the table:
DELETE FROM nonu;
COPY nonu FROM stdin;
\\N
\.
ERROR: copy: line 1, CopyFrom: Fail to add null value in not null
attribute tx
lost synchronization with server, resetting connection
This happened with 7.3.4, while trying to restore a 1.3 GB dump :-(
ERROR: copy: line 809051, CopyFrom: Fail to add null value in not
null attribute text
FATAL: Socket command type 0 unknown
The bug is still in 7.4Beta3; didn't test with Beta 4 yet.
Servus
Manfred
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Open 7.4 items
On Sun, 5 Oct 2003, Tom Lane wrote: Stephan Szabo [EMAIL PROTECTED] writes: I wasn't sure what to do about some of the spi error conditions. For many of them I'm just returning false now so that it will try the other mechanism in case that might work. I'm not really sure if that's worth it, or if I should just elog(ERROR) and give up. I think you may as well keep it the same as the other RI routines and just elog() on SPI error. If SPI is broken, the trigger procedure is gonna fail too. Okay. I changed that, consolidated the error-reporting code, and fixed a couple other little issues, notably: * The generated query applied ONLY to the FK table but not the PK table. I assume this was just an oversight. Yep, dumb oversight. * The query is now run using SPI_execp_current and selecting current snapshot. Without this, we could fail in a serializable transaction if someone else has already committed changes to either relation. You'd think I'd have remembered this could happen given the recent discussions. I was wondering if we could get the serialization failure with for update, but that's disallowed on the nullable side of the outer join. It's probably okay to give the no such key error in the delete case (at some point it'd be nice to make it give serialization failure, but that might take alot more work than is warrented at this time for 7.4) ---(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] Cannot dump/restore text value \N
Manfred Koizar [EMAIL PROTECTED] writes: To be clear, this is not about \N as the default external representation for NULL, I'm talking about a string consisting of the two characters backslash and uppercase-N. Now that I look at it, this must have been broken since the beginning of time, or at least since we made the null representation configurable. Surprising no one noticed before. The problem is that the WITH NULL string is compared to the attribute value *after* debackslashing, and so there is no way to prevent a match to an actual valid data string. In older code it seems that the representation of NULL as \N was hardwired, and this was tested for in the process of debackslashing, so that the valid data string \\N wouldn't be mistaken for \N. For the purposes of recognizing the default \N null representation, it seems we have to compare the null representation string to the pre-debackslashing input. (This is probably fairly easy to make happen in CVS tip, but it might be pretty painful in 7.3.) Arguably this is the right semantics because in the other direction we don't backslash the outgoing null-representation string. I wonder whether it would break any existing apps though. Comments? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [COMMITTERS] pgsql-server/src/backend/catalog aclchk.c
[EMAIL PROTECTED] (Peter Eisentraut - PostgreSQL) writes: When revoking privileges from the owner, don't revoke the grant options, to avoid recursively revoking everything from everyone. So an owner can never revoke his own grant options? That seems reasonable offhand, and compatible with our previous notion that the owner's ability to GRANT was inherent and nonrevocable. But I wonder how this squares with the SQL spec... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Open 7.4 items
Bruce Momjian [EMAIL PROTECTED] writes: Oh, that makes me feel better. Do we have timings for this code? This is just a single data point, but I made a table of 1 million rows containing just the int4 primary key column (values 0-1million in a somewhat random order). Then I copied the same data, sans index, to produce a foreign key table. Then I tried ALTER ADD PRIMARY KEY. The results were: Time to load the 1 million rows: 8 sec Time to create the PK index: 10 sec Time to ADD PRIMARY KEY: with CVS-tip code (fire trigger per row): 78 sec with proposed patch: anywhere from 5 to 25 sec depending on plan The default plan if there is no index on the FK table (meaning the planner will not know its true size) is a nestloop with inner index scan taking about 17 sec. If any index has been created on the FK table, you'll probably get a merge or hash join. I found these took about 20 sec with the default sort_mem setting, but with sort_mem boosted to 5 or more, the hash join got lots faster --- down in the 6-7 second range --- presumably because it didn't need multiple hash batches. It'd clearly be worth our while to mention boosting sort_mem as a helpful thing to do during bulk data load --- it should speed up btree index creation too. I don't think that tip appears anywhere in the docs at the moment. So the patch definitely seems worthwhile, but someone might still care to argue that there should be a bypass switch available too. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Open 7.4 items
Tom Lane wrote: It'd clearly be worth our while to mention boosting sort_mem as a helpful thing to do during bulk data load --- it should speed up btree index creation too. I don't think that tip appears anywhere in the docs at the moment. Added recently, see last sentence: termvarnamesort_mem/varname (typeinteger/type)/term listitem para Specifies the amount of memory to be used by internal sort operations and hash tables before switching to temporary disk files. The value is specified in kilobytes, and defaults to 1024 kilobytes (1 MB). Note that for a complex query, several sort or hash operations might be running in parallel; each one will be allowed to use as much memory as this value specifies before it starts to put data into temporary files. Also, several running sessions could be doing sort operations simultaneously. So the total memory used could be many times the value of varnamesort_mem/varname. Sort operations are used by literalORDER BY/, merge joins, and commandCREATE INDEX/. Hash tables are used in hash joins, hash-based aggregation, and hash-based processing of literalIN/ subqueries. Because commandCREATE INDEX/ is used when restoring a database, it might be good to temporarily increase this value during a restore. /para -- 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] Open 7.4 items
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: It'd clearly be worth our while to mention boosting sort_mem as a helpful thing to do during bulk data load --- it should speed up btree index creation too. I don't think that tip appears anywhere in the docs at the moment. Added recently, see last sentence: That's a fairly useless place to put it, though, since someone would only think to look at sort_mem if they already had a clue. It should be mentioned under bulk data load (in performance tips chapter) and perhaps also in dump/restore procedures. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Cannot dump/restore text value \N
I have solved my restore problem by editing (the relevant part of) the dump (:%s/^IN^I/^IN ^I/), a one-off solution g Anyway, thanks for your investigation. On Sun, 05 Oct 2003 19:12:50 -0400, Tom Lane [EMAIL PROTECTED] wrote: it seems we have to compare the null representation string to the pre-debackslashing input. Sounds reasonable, IMHO. I wonder whether it would break any existing apps though. Couldn't be worse than silently converting valid non-null values to NULL ... Servus Manfred ---(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] Open 7.4 items
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: It'd clearly be worth our while to mention boosting sort_mem as a helpful thing to do during bulk data load --- it should speed up btree index creation too. I don't think that tip appears anywhere in the docs at the moment. Added recently, see last sentence: That's a fairly useless place to put it, though, since someone would only think to look at sort_mem if they already had a clue. It should be mentioned under bulk data load (in performance tips chapter) and perhaps also in dump/restore procedures. There were several places it is needed, so I just hit the one place --- feel free to add some more. -- 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 7: don't forget to increase your free space map settings
Re: [HACKERS] Day of week question
Do to a bug in pgtypeslin I've been experimenting with the day of week
stuff a little bit and I found that we do not agree with the output of
the cal command on older dates. I have no idea which one is correct:
mm=# select to_char('1000/01/01'::date,'D');
to_char
-
4
(1 row)
Thus we believe 1000/1/1 is a Wednesday. cal says:
Januar 1000
So Mo Di Mi Do Fr Sa
1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31
Thus 1000/1/1 is a Monday. Is this a bug in cal? Or do we produce the
wrong output? Or do I simply misread the output?
What do we do for September 1752?
bash-2.05a$ cal 9 1752
September 1752
Su Mo Tu We Th Fr Sa
1 2 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] LOCK.tag(figuring out granularity of lock)
On Fri, Aug 08, 2003 at 03:49:36PM -0400, Bruce Momjian wrote: Alvaro Herrera wrote: Right now the sectors on the hard disk run clockwise, but I heard a rumor that you can squeeze 0.2% more throughput by running them counterclockwise. It's worth the effort. Recommended. (Gerry Pourwelle) In relation to your signature, I assume you have seen this joke: http://www.netfunny.com/rhf/jokes/95q1/jpreviews.html The helicopter part is the funniest. :-D Yeah, I read that and actually extracted the quote from there. As a small boy I used to read Jerry Pournelle's commentary on Byte. (Or was it PC World? I don't remember.) They were strange to me, and I always thought life was like that for people in the USA, i.e. hardware falling from the sky and always the getting latest and greatest of all sorts of machinery and software. I couldn't imagine what was this about phoning someone to get support, much less paying fees. The satire made me remember all that and I couldn't help quoting it on my sigs. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Porque Kim no hacia nada, pero, eso sí, con extraordinario éxito (Kim, Kipling) ---(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] Open 7.4 items
Stephan Szabo [EMAIL PROTECTED] writes: It's probably okay to give the no such key error in the delete case (at some point it'd be nice to make it give serialization failure, but that might take alot more work than is warrented at this time for 7.4) Per prior discussion, I think the no such key error is more useful than the serialization error, even if it's the wrong thing according to a narrow interpretation; so I really don't feel much need to revisit this later. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] more on initdb
Andrew Dunstan [EMAIL PROTECTED] writes: Is there any reason other than historical that the System Views setup isn't a separate script fed to postgres by initdb, like, say, the information schema file? If there isn't a good reason should we unwire it as part of moving to a C version of initdb? Just historical, and go for it. regards, tom lane PS: please don't send stuff bcc to pgsql-hackers. I was fortunate to notice this in my probable-spam folder before deleting it ... ---(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
[HACKERS] extra_float_digits question
Hi guys, During a pg_dump, does the extra_float_digits option only affect the COPY data, or could it possibly also affect column defaults, view definitions, etc? Chris ---(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] Open 7.4 items
On Sun, 2003-10-05 at 19:58, Tom Lane wrote: That's a fairly useless place to put it, though, since someone would only think to look at sort_mem if they already had a clue. It should be mentioned under bulk data load (in performance tips chapter) Attached is a doc patch that does this. The way I've worded it may not be the best, though. and perhaps also in dump/restore procedures. It's already mentioned there. Should we also suggest turning off fsync when doing restores? (BTW, is there a reason the docs consistently call them B-tree indexes, not B+-tree indexes?) -Neil Index: doc/src/sgml/perform.sgml === RCS file: /var/lib/cvs/pgsql-server/doc/src/sgml/perform.sgml,v retrieving revision 1.33 diff -c -r1.33 perform.sgml *** doc/src/sgml/perform.sgml 11 Sep 2003 18:30:38 - 1.33 --- doc/src/sgml/perform.sgml 6 Oct 2003 00:21:48 - *** *** 751,761 para Use commandCOPY FROM STDIN/command to load all the rows in one ! command, instead of using ! a series of commandINSERT/command commands. This reduces parsing, ! planning, etc. ! overhead a great deal. If you do this then it is not necessary to turn ! off autocommit, since it is only one command anyway. /para /sect2 --- 751,760 para Use commandCOPY FROM STDIN/command to load all the rows in one ! command, instead of using a series of commandINSERT/command ! commands. This reduces parsing, planning, etc. overhead a great ! deal. If you do this then it is not necessary to turn off ! autocommit, since it is only one command anyway. /para /sect2 *** *** 764,772 para If you are loading a freshly created table, the fastest way is to ! create the table, bulk-load with commandCOPY/command, then create any ! indexes needed ! for the table. Creating an index on pre-existing data is quicker than updating it incrementally as each row is loaded. /para --- 763,771 para If you are loading a freshly created table, the fastest way is to ! create the table, bulk load the table's data using ! commandCOPY/command, then create any indexes needed for the ! table. Creating an index on pre-existing data is quicker than updating it incrementally as each row is loaded. /para *** *** 780,785 --- 779,797 /para /sect2 + sect2 id=populate-sort-mem +titleIncrease varnamesort_mem/varname/title + +para + Temporarily increasing the varnamesort_mem/varname + configuration variable when restoring large amounts of data can + lead to improved performance. This is because when a B-tree index + is created from scratch, the existing content of the table needs + to be sorted. Allowing the merge sort to use more buffer pages + means that fewer merge passes will be required. +/para + /sect2 + sect2 id=populate-analyze titleRun commandANALYZE/command Afterwards/title Index: doc/src/sgml/runtime.sgml === RCS file: /var/lib/cvs/pgsql-server/doc/src/sgml/runtime.sgml,v retrieving revision 1.210 diff -c -r1.210 runtime.sgml *** doc/src/sgml/runtime.sgml 3 Oct 2003 19:26:49 - 1.210 --- doc/src/sgml/runtime.sgml 6 Oct 2003 00:06:45 - *** *** 928,935 by literalORDER BY/, merge joins, and commandCREATE INDEX/. Hash tables are used in hash joins, hash-based aggregation, and hash-based processing of literalIN/ subqueries. Because ! commandCREATE INDEX/ is used when restoring a database, it might ! be good to temporarily increase this value during a restore. /para /listitem /varlistentry --- 928,936 by literalORDER BY/, merge joins, and commandCREATE INDEX/. Hash tables are used in hash joins, hash-based aggregation, and hash-based processing of literalIN/ subqueries. Because ! commandCREATE INDEX/ is used when restoring a database, ! increasing varnamesort_mem/varname before doing a large ! restore operation can improve performance. /para /listitem /varlistentry ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Open 7.4 items
Neil Conway [EMAIL PROTECTED] writes: (BTW, is there a reason the docs consistently call them B-tree indexes, not B+-tree indexes?) The latter might be technically more correct, but most people are going to think it's a typo. I think B-tree is fine for the purposes of our docs. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] COUNT(*) again (was Re: [HACKERS] Index/Function organized
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: I think that's not happening, conditionally or otherwise. The atomicity problems alone are sufficient reason why not, even before you look at the performance issues. What are the atomicity problems of adding a create/expire xid to the index tuples? You can't update a tuple's status in just one place ... you have to update the copies in the indexes too. But we don't update the tuple status for a commit, we just mark the xid as committed. We do have lazy status bits that prevent later lookups in pg_clog, but we have those in the index already also. What am I missing? -- 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]
