[HACKERS] int64/double for time/timestamp
Hi! I work on memory leaks during creation index on time/timestamp column using GiST and found follow problem (?): For timestamp storage and defines are defined as (from utils/timestamp.h): #ifdef HAVE_INT64_TIMESTAMP typedef int64 Timestamp; #define TimestampGetDatum(X) Int64GetDatum(X) #define DatumGetTimestamp(X) ((Timestamp) DatumGetInt64(X)) #else typedef double Timestamp; #define TimestampGetDatum(X) Float8GetDatum(X) #define DatumGetTimestamp(X) ((Timestamp) DatumGetFloat8(X)) #endif It looks consistently, but for time (from utils/date.h): ifdef HAVE_INT64_TIMESTAMP typedef int64 TimeADT; #else typedef float8 TimeADT; #endif #define TimeADTGetDatum(X)Float8GetDatum(X) #define DatumGetTimeADT(X)((TimeADT) DatumGetFloat8(X)) So, in case HAVE_INT64_TIMESTAMP int64 may use as float8. Is it correct? It seems to me, that my last changes in btree_gist produce a error for btree_time on some architectures for this reason, but the same changes for timestamp doesn't produce ones. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Question about Unrecognized SPI code ...
I just found an interesting issue in recent PostgreSQL releases: CREATE VIEW view_nonsense AS SELECT 1 AS a, 2 AS b; CREATE RULE myrule AS ON INSERT TO view_nonsense DO INSTEAD NOTHING; CREATE OR REPLACE FUNCTION debug() RETURNS boolean AS ' DECLARE BEGIN INSERT INTO view_nonsense VALUES (10, 20); RETURN true; END; ' LANGUAGE 'plpgsql'; SELECT debug(); The INSERT statement is not doing something useful here [EMAIL PROTECTED] tmp]$ psql test view.sql CREATE VIEW CREATE RULE CREATE FUNCTION ERROR: SPI_execute_plan failed executing query INSERT INTO view_nonsense VALUES (10, 20): Unrecognized SPI code 0 CONTEXT: PL/pgSQL function debug line 4 at SQL statement SPI_result_code_string(int code) and PL/pgSQL don't seem to be aware of DO NOTHING rules. Is it desired behaviour? Best regards, Hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/660/816 40 77 www.cybertec.at, www.postgresql.at ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Get rid of system attributes in pg_attribute?
On Monday 21 February 2005 04:23, Christopher Kings-Lynne wrote:
I'm wondering how useful it is to store explicit representations of the
system attributes in pg_attribute. We could very easily hard-wire those
things instead, which would make for a large reduction in the number of
entries in pg_attribute. (In the current regression database nearly
half of the rows have attnum 0.) I think the impact on the backend
would be pretty minimal, but I'm wondering if removing these entries
would be likely to break any client-side code. Does anyone know of
client code that actually pays attention to pg_attribute rows with
negative attnums?
Well, apart from a attnum 0 clause in phpPgAdmin, I don't think so...
Well, the corner case would be for those times when we use oid for updating
specific rows in a table, if a user creates there own oid column then you
could have trouble. Actually we already have a safegaurd for this in
phppgadmin so we wont cause mistakes, it's just that those updates probably
won't work... others might not have been so thorough though.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] Get rid of system attributes in pg_attribute?
On Sunday 20 February 2005 12:30, Tom Lane wrote:
Robert Treat [EMAIL PROTECTED] writes:
One of us is not understanding the other :-) I'm asking if I have a
piece of code that does something like select attname from pg_attribute
where attrelid = 'stock'::regclass::oid with the intent of displaying all
those attnames, then the system atts will no longer show up in that
list, correct?
Correct. What I'm asking is whether that's a problem for anyone.
OK... I can't seem to find my theoretically problem code so I guess it is in
the clear (the code I can find references the system columns explicitly) One
thing I wonder about is will this toss driver implementors a loop? ISTR a
flag in the ODBC driver whether to include the oid column (or maybe system
columns)... could be some trouble there.
One other question, do you see a scheme for selecting system columns even
explicitly once a user has created their own column with a conflicting name.
ISTM that we wouldn't be able to select the system ctid once a user creates
thier own ctid column... somewhere in the back of my head a voice is
grumbling about sql specs and multiple columns with the same name in a table.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[HACKERS] big problem
Hi all, I couldn't find anything related to my problem on web or irc, so i'm posting here. I deleted valuable data from wrong table :) pretty common problem i think. Guy on #postgresql at freenode told me that my data is still there, but tricky part is how to undo my delete. I'm using pg 7.4.7 on fbsd, i dont' use any special config and pg_xlog is fine. I hope there is a solution :) Thanks for answer and best regards, Bostjan ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] big problem
I deleted valuable data from wrong table :) pretty common problem i think. Guy on #postgresql at freenode told me that my data is still there, but tricky part is how to undo my delete. I'm using pg 7.4.7 on fbsd, i dont' use any special config and pg_xlog is fine. I hope there is a solution :) That guy on IRC is probably me. I told him to take a copy of his data dir for safekeeping, and not to vacuum. I don't know how to get a look at old row versions, however. It's new data with no backup, and no PITR running. Chris ---(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] Get rid of system attributes in pg_attribute?
Robert Treat [EMAIL PROTECTED] writes: Does anyone know of client code that actually pays attention to pg_attribute rows with negative attnums? Well, the corner case would be for those times when we use oid for updating specific rows in a table, if a user creates there own oid column then you could have trouble. Actually we already have a safegaurd for this in phppgadmin so we wont cause mistakes, it's just that those updates probably won't work... others might not have been so thorough though. Anyone who's not checking that has been at risk ever since we invented WITHOUT OIDS: regression=# create table foo (oid text); ERROR: column name oid conflicts with a system column name regression=# create table foo (oid text) without oids; CREATE TABLE Probably ctid is the more interesting case; I'm pretty sure ODBC relies on ctid as a short-term-unique row identifier. 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] big problem
Christopher Kings-Lynne [EMAIL PROTECTED] writes: I deleted valuable data from wrong table :) pretty common problem i think. Guy on #postgresql at freenode told me that my data is still there, but tricky part is how to undo my delete. I'm using pg 7.4.7 on fbsd, i dont' use any special config and pg_xlog is fine. I hope there is a solution :) That guy on IRC is probably me. I told him to take a copy of his data dir for safekeeping, and not to vacuum. I don't know how to get a look at old row versions, however. It's new data with no backup, and no PITR running. The easiest avenue might be to back up the XID counter with pg_resetxlog. You'd have to dump and restore the table of course, else the rows will go missing again as soon as the XID climbs up past the transaction that deleted 'em. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Get rid of system attributes in pg_attribute?
On Tuesday 22 February 2005 10:32, Tom Lane wrote:
Robert Treat [EMAIL PROTECTED] writes:
Does anyone know of
client code that actually pays attention to pg_attribute rows with
negative attnums?
Well, the corner case would be for those times when we use oid for
updating specific rows in a table, if a user creates there own oid column
then you could have trouble. Actually we already have a safegaurd for
this in phppgadmin so we wont cause mistakes, it's just that those
updates probably won't work... others might not have been so thorough
though.
Anyone who's not checking that has been at risk ever since we invented
WITHOUT OIDS:
regression=# create table foo (oid text);
ERROR: column name oid conflicts with a system column name
regression=# create table foo (oid text) without oids;
CREATE TABLE
Actually I was thinking more the case where someone creates their own column
names oid where they have no intention of those values being unique. If you
weren't already checking for duplicate oid's you could be in for trouble.
Probably ctid is the more interesting case; I'm pretty sure ODBC relies
on ctid as a short-term-unique row identifier.
Yeah... how many utility tools out there reference system columns explicitly?
I think we need a scheme for allowing them to keep working even with user
defined columns of the same name.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Get rid of system attributes in pg_attribute?
Robert Treat [EMAIL PROTECTED] writes: On Tuesday 22 February 2005 10:32, Tom Lane wrote: Probably ctid is the more interesting case; I'm pretty sure ODBC relies on ctid as a short-term-unique row identifier. Yeah... how many utility tools out there reference system columns explicitly? I think we need a scheme for allowing them to keep working even with user defined columns of the same name. Well, that probably knocks out my thought that we could stop reserving the system column names (at least ctid and xmin, which are the two that actually seem useful to ordinary clients, need to stay reserved). But it still seems like we don't have to represent these columns explicitly in pg_attribute. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] psql: recall previous command?
On February 21, 2005 08:26 pm, Neil Conway wrote: Is there a way to recall the previous command in psql? Obviously, up arrow or Ctrl-P using readline and the default readline bindings is close, but it recalls the previous _line_ of input. That is not at all the same thing in the case of a multiple line command, for example. Currently I use \e to bring up the last command (multiline) in the editor, from this point I can just save it to execute it. If there is no way at present, I think there should be. Using up arrow is quite annoying when dealing with multiple line SQL statements. Two issues: - how to handle slash commands? Slash commands and SQL statements can be intermixed: neilc=# select 1 \timing Timing is off. neilc-# ; ?column? -- 1 (1 row) So I'm not quite sure what the right behavior here is. We could always just ignore slash commands (the command would recall the previous SQL statement) -- since few slash commands are multi-line, I don't think that would be too bad. - when a multiple-line command is recalled, how should it be presented in the psql input buffer? Perhaps we could strip newlines from recalled command text, so that the recalled command would fit on a single line. That would mean the recalled command would look somewhat different than how the user typed it, although of course the semantics of the query should be the same. Any better ideas? -Neil ---(end of broadcast)--- TIP 8: explain analyze is your friend -- Darcy Buskermolen Wavefire Technologies Corp. ph: 250.717.0200 fx: 250.763.1759 http://www.wavefire.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Get rid of system attributes in pg_attribute?
Tom Lane wrote: Well, that probably knocks out my thought that we could stop reserving the system column names (at least ctid and xmin, which are the two that actually seem useful to ordinary clients, need to stay reserved). But it still seems like we don't have to represent these columns explicitly in pg_attribute. Hm, technically you might be right. Still, I like pgAdmin3 to show that columns (when show system objects is enabled) for teaching purposes, so users/newbies browsing the objects will learn hey, there are some reserved columns, they could have some meaning. I'd be not too excited about emulating system column pg_attribute entries... Regards, Andreas ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Get rid of system attributes in pg_attribute?
Andreas Pflug [EMAIL PROTECTED] writes: Tom Lane wrote: it still seems like we don't have to represent these columns explicitly in pg_attribute. Hm, technically you might be right. Still, I like pgAdmin3 to show that columns (when show system objects is enabled) for teaching purposes, so users/newbies browsing the objects will learn hey, there are some reserved columns, they could have some meaning. Not unreasonable, but is it worth a factor of 2 in the size of pg_attribute? regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Question about Unrecognized SPI code ...
=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= [EMAIL PROTECTED] writes: ERROR: SPI_execute_plan failed executing query INSERT INTO view_nonsense VALUES (10, 20): Unrecognized SPI code 0 CONTEXT: PL/pgSQL function debug line 4 at SQL statement SPI_result_code_string(int code) and PL/pgSQL don't seem to be aware of DO NOTHING rules. Hmm. What's happening is that _SPI_execute_plan() initializes its local result variable to 0, and then that ends up getting returned because the execute-one-query loop executes zero times. Since 0 isn't a defined SPI result code, this seems bad. The question is what to return instead. Of the currently defined SPI result codes, SPI_OK_UTILITY seems the closest, but it implies that something happened when nothing did. Is it worth inventing a new result code SPI_OK_NOTHING (or similar) to describe this case? That would possibly imply changing a lot of SPI-using code to handle the new result alternative. 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] int64/double for time/timestamp
Teodor Sigaev [EMAIL PROTECTED] writes: It looks consistently, but for time (from utils/date.h): ifdef HAVE_INT64_TIMESTAMP typedef int64 TimeADT; #else typedef float8 TimeADT; #endif #define TimeADTGetDatum(X)Float8GetDatum(X) #define DatumGetTimeADT(X)((TimeADT) DatumGetFloat8(X)) So, in case HAVE_INT64_TIMESTAMP int64 may use as float8. Is it correct? Urgh. This is clearly a bug. All the code in utils/adt seems to be correctly set up to treat TimeADT as an integral value, but then the two macros quoted are converting the value to float8 and back again ... so what's actually on disk is the float8 equivalent of what the int64 value is supposed to be :-(. As long as the macros are used *consistently* to fetch and store time datums, no one would notice --- you could only see a difference if the int64 values got large enough to not be represented completely accurately as floats, which I believe is impossible for type time. So the fact that you're seeing a bug in btree_gist suggests that someplace you're cheating and bypassing the FooGetDatum/DatumGetFoo macros. We'll obviously want to fix this going forward for efficiency reasons, but it's an initdb-forcer because it'll change the on-disk representation of time columns. So we can't change it in 8.0 or before. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Get rid of system attributes in pg_attribute?
Tom Lane wrote: Andreas Pflug [EMAIL PROTECTED] writes: Tom Lane wrote: it still seems like we don't have to represent these columns explicitly in pg_attribute. Hm, technically you might be right. Still, I like pgAdmin3 to show that columns (when show system objects is enabled) for teaching purposes, so users/newbies browsing the objects will learn hey, there are some reserved columns, they could have some meaning. Not unreasonable, but is it worth a factor of 2 in the size of pg_attribute? Do we need to save space? On a DB with quite some tables I have pg_attribute size=7.5MB, pg_class size 5.8MB (13166 pg_attribute rows total, 5865 system columns, most tables without oids). This doesn't seem unacceptable big to me. Regards, Andreas ---(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] [NOVICE] Question on TRUNCATE privleges
Keith Worthington [EMAIL PROTECTED] writes: I have just discovered that I can speed up one of my functions by a factor of 600 by changing an unqualified DELETE to a TRUNCATE. Unfortunately, the function is run by multiple users and I get the error message TESTDB= TRUNCATE inventory.tbl_item; ERROR: must be owner of relation tbl_item There is nothing in the documentation (http://www.postgresql.org/docs/8.0/interactive/sql-truncate.html) about this restriction ( You see Michael I am still reading the documentation. ;-) ) Do I get to post my first user comment on the documentation pages? Do I? Hunh? Can I? :-) Yup ;-) Is there a way to have multiple owners of a table or otherwise achive this behavior? I'm not entirely sure that requiring ownership of the table is the appropriate restriction for TRUNCATE. It made some sense back when TRUNCATE wasn't transaction-safe, but now that it is, you could almost argue that ordinary DELETE privilege should allow TRUNCATE. Almost. The hole in the argument is that TRUNCATE doesn't run ON DELETE triggers and so it could possibly be used to bypass things the table owner wants to have happen. You could equate TRUNCATE to DROP TRIGGER(s), DELETE, CREATE TRIGGER(s) ... but DROP TRIGGER requires ownership. CREATE TRIGGER only requires TRIGGER privilege which is grantable. So one answer is to change DROP TRIGGER to require TRIGGER privilege (which would mean user A could remove a trigger installed by user B, if both have TRIGGER privileges on the table) and then say you can TRUNCATE if you have both DELETE and TRIGGER privileges. It looks to me like the asymmetry between CREATE TRIGGER and DROP TRIGGER is actually required by SQL99, though, so changing it would be a hard sell (unless SQL2003 fixes it?). Comments anyone? 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] [NOVICE] Question on TRUNCATE privleges
On Tue, 2005-02-22 at 14:00, Tom Lane wrote:
Keith Worthington [EMAIL PROTECTED] writes:
I have just discovered that I can speed up one of my functions by a factor
of
600 by changing an unqualified DELETE to a TRUNCATE. Unfortunately, the
function is run by multiple users and I get the error message
TESTDB= TRUNCATE inventory.tbl_item;
ERROR: must be owner of relation tbl_item
There is nothing in the documentation
(http://www.postgresql.org/docs/8.0/interactive/sql-truncate.html) about
this
restriction ( You see Michael I am still reading the documentation. ;-) )
Do
I get to post my first user comment on the documentation pages? Do I? Hunh?
Can I? :-)
Yup ;-)
Is there a way to have multiple owners of a table or otherwise achive this
behavior?
I'm not entirely sure that requiring ownership of the table is the
appropriate restriction for TRUNCATE. It made some sense back when
TRUNCATE wasn't transaction-safe, but now that it is, you could almost
argue that ordinary DELETE privilege should allow TRUNCATE.
Almost. The hole in the argument is that TRUNCATE doesn't run ON DELETE
triggers and so it could possibly be used to bypass things the table
owner wants to have happen. You could equate TRUNCATE to DROP TRIGGER(s),
DELETE, CREATE TRIGGER(s) ... but DROP TRIGGER requires ownership.
CREATE TRIGGER only requires TRIGGER privilege which is grantable.
So one answer is to change DROP TRIGGER to require TRIGGER privilege
(which would mean user A could remove a trigger installed by user B,
if both have TRIGGER privileges on the table) and then say you can
TRUNCATE if you have both DELETE and TRIGGER privileges.
It looks to me like the asymmetry between CREATE TRIGGER and DROP
TRIGGER is actually required by SQL99, though, so changing it would
be a hard sell (unless SQL2003 fixes it?).
Comments anyone?
Isn't this a case for a SECURITY DEFINER function?
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [pgsql-hackers-win32] UNICODE/UTF-8 on win32
The installer does not permit it, but initdb lets you do anything yuo want - I think that's where we are. If you know what you're doing, you can use it by manually initdbing. There is no such thing as unicode locale. Unicode (UTF8) is an encoding, that has to be paired with a locale. I assume you mean C locale. While UPPER/LOWER does not matter, sort order does - for indexes if nothing else. I'm unsure if this works - I think I read reports about itn ot working, but I haven't tried it out myself. I was hoping for a final solution for 8.1 which actually fixes it so it works all the way. Not sure if I can make that happen myself, but I can always try unless someone else does it. //mha -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED] Sent: den 22 februari 2005 04:43 To: Tatsuo Ishii Magnus, where are we on this? Seems we should allow unicode encoding and just not unicode locale in pginstaller. Also, Unicode is changing to UTF-8 in 8.1. --- Tatsuo Ishii wrote: I do understand the problem, but don't undertstand the decision you guys made. The fact that UPPER/LOWER and some other functions does not work in win32 is surely a problem for some languages, but not a problem for otheres. For example, Japanese (and probably Chinese and Korean) does not have a concept upper/lower. So the fact UPPER/LOWER does not work with UTF-8/win32 is not problem for Japanese (and for some other languages). Just using C locale with UTF-8 is enough in this case. In summary, I think you guys are going to overkill the multibyte support functionality on UTF-8/win32 because of the fact that some langauges do not work. Same thing can be said to EUC-JP, EUC-CN and EUC-KR and so on as well. I strongly object the policy to try to unconditionaly disable UTF-8 support on win32. -- Tatsuo Ishii From: Magnus Hagander [EMAIL PROTECTED] Subject: RE: [pgsql-hackers-win32] UNICODE/UTF-8 on win32 Date: Sat, 1 Jan 2005 14:48:04 +0100 Message-ID: [EMAIL PROTECTED] UNICODE/UTF-8 does not work on the win32 server. The reason is that strcoll() and friends don't work with it. To support it on win32, it needs to be converted to UTF16 and use the wide-character versions of the fucntion. Which we do not do. (See http://archives.postgresql.org/pgsql-hackers-win32/2004-11/msg00036.php and http://archives.postgresql.org/pgsql-hackers-win32/2004-12/msg0 0106.php) I don't *think* we need to disable ito n the client. AFAIK, the client interfaces don't use any of these functions, and I've seen reports of people using that long before we had a native win32 server. //Magnus -Original Message- From: Tatsuo Ishii [mailto:[EMAIL PROTECTED] Sent: den 1 januari 2005 01:10 To: [EMAIL PROTECTED] Cc: Magnus Hagander; [EMAIL PROTECTED] Subject: Re: [pgsql-hackers-win32] UNICODE/UTF-8 on win32 Sorry, but I don't subscribe to pgsql-hackers-win32 list. What's the problem here? -- Tatsuo Ishii Magnus Hagander [EMAIL PROTECTED] writes: We know it's broken and won't be fixed for 8.0. If we just #ifndef WIN32 the definitions in utils/mb/encnames.c it won't be possible to select that encoding, right? Will that have any other unwanted effects (such as breaking client encodings)? If not, I suggest this is done. I believe the subscripts in those arrays have to match the encoding enum type, so you can't just ifdef out individual entries. (Or perhaps something can be done in pg_valid_server_encoding?) Making the valid_server_encoding function reject it might work. Tatsuo-san would know for sure. Should we also reject it as a client encoding, or does that work OK? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Design notes for BufMgrLock rewrite
On Mon, 2005-02-21 at 18:45 -0500, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: ...but do you agree with my comments on the lack of scalability in cache miss situations? No. Grabbing a lock during a cache miss is the least of your worries; you're going to do I/O, or at least a kernel call, so it hardly matters as long as you're not holding the lock for a time that's long in comparison to that overhead. The I/O does alleviate contention to a certain extent, but if you have a well laid out system that can soak up the I/O you're throwing AND you have multiple CPUs trying to get at blocks, then you have contention. The other problem is the OS cache. A PostgreSQL cache miss isn't necessarily an I/O. If PostgreSQL more easily supported very large shared_buffers then I would be more in agreement. The only test case I've seen that exposes a significant amount of bufmgr contention is one that involves zero I/O (100% cache hit rate), so that the fraction of time spent holding the BufMgrLock is a significant part of the total time. As soon as you move off 100%, the bufmgr isn't the critical path anymore. So I think the fact that this redesign is able to reduce the contention at all in that case is just gravy. (It does reduce contention because ReleaseBuffer doesn't take a global lock anymore, and because BufMappingLock and BufFreelistLock are separate locks.) Let's talk about Mark's TPC-C like tests. As soon as the cache is full, the response times go to hell. (see http://www.osdl.org/projects/dbt2dev/results/dev4-010/264/) Once the cache is full, each dirty cache miss costs two BufMgrLock calls. On larger caches, very roughly 80% of the cache is dirty, so the overall rise in contention is around 1.6 times what it was before. I see that as a possible indicator of the effects of BufMgrLock contention. (It does reduce contention because ReleaseBuffer doesn't take a global lock anymore, and because BufMappingLock and BufFreelistLock are separate locks.) Yes, understood. If testing shows that we still have contention issues with this design then we can try subdividing the BufFreelistLock --- but right now my guess is that we'd just be giving up more cache management efficiency in return for not much. OK to that. [and please remember, all, that I'm discussing the very highest end of performance architecture...] Best Regards, Simon Riggs ---(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] Repleacement for src/port/snprintf.c
On Mon, Feb 21, 2005 at 10:53:08PM -0500, Bruce Momjian wrote:
Applied.
Thanks a lot. The patch attached solves the tread
safety problem. Please review it before applying,
I am not sure I am doing the right thing
On Tue, 22 Feb 2005 19:57:15 +0100, Kurt Roeckx [EMAIL PROTECTED] wrote:
The configure test is a little broken. It needs to quote the
$'s.
I've rewritten the test a little.
This one needs applying too. $'s do get scrambled.
Best regards,
Nicolai.
*** ./src/port/snprintf.c.orig sali Şub 22 20:02:03 2005
--- ./src/port/snprintf.c sali Şub 22 21:59:48 2005
***
*** 80,96
* for string length. This covers a nasty loophole.
*
* The other functions are there to prevent NULL pointers from
! * causing nast effects.
**/
/*static char _id[] = $PostgreSQL: pgsql/src/port/snprintf.c,v 1.6 2005/02/22 04:57:24 momjian Exp $;*/
- static char *end;
- static int SnprfOverflow;
int snprintf(char *str, size_t count, const char *fmt,...);
int vsnprintf(char *str, size_t count, const char *fmt, va_list args);
int printf(const char *format, ...);
! static void dopr(char *buffer, const char *format, va_list args);
int
printf(const char *fmt,...)
--- 80,94
* for string length. This covers a nasty loophole.
*
* The other functions are there to prevent NULL pointers from
! * causing nasty effects.
**/
/*static char _id[] = $PostgreSQL: pgsql/src/port/snprintf.c,v 1.6 2005/02/22 04:57:24 momjian Exp $;*/
int snprintf(char *str, size_t count, const char *fmt,...);
int vsnprintf(char *str, size_t count, const char *fmt, va_list args);
int printf(const char *format, ...);
! static void dopr(char *buffer, const char *format, va_list args, char *end);
int
printf(const char *fmt,...)
***
*** 97,103
{
int len;
va_list args;
! static char* buffer[4096];
char* p;
va_start(args, fmt);
--- 95,101
{
int len;
va_list args;
! char* buffer[4096];
char* p;
va_start(args, fmt);
***
*** 125,134
int
vsnprintf(char *str, size_t count, const char *fmt, va_list args)
{
str[0] = '\0';
end = str + count - 1;
! SnprfOverflow = 0;
! dopr(str, fmt, args);
if (count 0)
end[0] = '\0';
return strlen(str);
--- 123,132
int
vsnprintf(char *str, size_t count, const char *fmt, va_list args)
{
+ char *end;
str[0] = '\0';
end = str + count - 1;
! dopr(str, fmt, args, end);
if (count 0)
end[0] = '\0';
return strlen(str);
***
*** 138,148
* dopr(): poor man's version of doprintf
*/
! static void fmtstr(char *value, int ljust, int len, int zpad, int maxwidth);
! static void fmtnum(long_long value, int base, int dosign, int ljust, int len, int zpad);
! static void fmtfloat(double value, char type, int ljust, int len, int precision, int pointflag);
! static void dostr(char *str, int cut);
! static void dopr_outch(int c);
static char *output;
--- 136,146
* dopr(): poor man's version of doprintf
*/
! static void fmtstr(char *value, int ljust, int len, int zpad, int maxwidth, char *end);
! static void fmtnum(long_long value, int base, int dosign, int ljust, int len, int zpad, char *end);
! static void fmtfloat(double value, char type, int ljust, int len, int precision, int pointflag, char *end);
! static void dostr(char *str, int cut, char *end);
! static void dopr_outch(int c, char *end);
static char *output;
***
*** 152,158
#define FMTCHAR 4
static void
! dopr(char *buffer, const char *format, va_list args)
{
int ch;
long_long value;
--- 150,156
#define FMTCHAR 4
static void
! dopr(char *buffer, const char *format, va_list args, char *end)
{
int ch;
long_long value;
***
*** 415,425
case '%':
break;
default:
! dostr(???, 0);
}
break;
default:
! dopr_outch(ch);
break;
}
}
--- 413,423
case '%':
break;
default:
! dostr(???, 0, end);
}
break;
default:
! dopr_outch(ch, end);
break;
}
}
***
*** 446,465
case FMTSTR:
fmtstr(fmtparptr[i]-value, fmtparptr[i]-ljust,
fmtparptr[i]-len, fmtparptr[i]-zpad,
! fmtparptr[i]-maxwidth);
break;
case FMTNUM:
fmtnum(fmtparptr[i]-numvalue, fmtparptr[i]-base,
fmtparptr[i]-dosign, fmtparptr[i]-ljust,
! fmtparptr[i]-len, fmtparptr[i]-zpad);
break;
case FMTFLOAT:
fmtfloat(fmtparptr[i]-fvalue, fmtparptr[i]-type,
fmtparptr[i]-ljust, fmtparptr[i]-len,
! fmtparptr[i]-precision, fmtparptr[i]-pointflag);
break;
case FMTCHAR:
! dopr_outch(fmtparptr[i]-charvalue);
break;
}
Re: [HACKERS] left-deep plans?
Kenneth Marshall wrote: GEQO is an attempt to provide a near-optimal join order without using an exhaustive search. An exhaustive, deterministic search of a subset of the search space has a non-zero probability of finding only a local minimum in execution time. I'm not sure what you mean. By an exhaustive, deterministic search of a subset of the search space, I was referring to using the normal planner, but restricting the search space to only left-deep plans. Since GEQO will also only consider left-deep plans, ISTM there is no issue of local minima that does not apply to an equal degree to GEQO itself. Since purely random selection, with learning, works so well, it may be worth developing a new random join-order optimization algorithm Yeah, I agree. I've read a few papers on randomized algorithms for join order selection, but none of them really seemed to be clear winners. Do you have a reference for the paper you read? -Neil ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] UTF8 or Unicode
I do not object the changing UNICODE-UTF-8, but all these discussions sound a little bit funny to me. If you want to blame UNICODE, you should blame LATIN1 etc. as well. LATIN1(ISO-8859-1) is actually a character set name, not an encoding name. ISO-8859-1 can be encoded in 8-bit single byte stream. But it can be encoded in 7-bit too. So when we refer to LATIN1(ISO-8859-1), it's not clear if it's encoded in 7/8-bit. -- Tatsuo Ishii From: Bruce Momjian pgman@candle.pha.pa.us Subject: Re: [HACKERS] UTF8 or Unicode Date: Mon, 21 Feb 2005 22:08:25 -0500 (EST) Message-ID: [EMAIL PROTECTED] Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: I think we just need to _favor_ UTF8. I agree. The question is where are we favoring Unicode rather than UTF8? It's the canonical name of the encoding, both in the code and the docs. regression=# create database e encoding 'utf-8'; CREATE DATABASE regression=# \l List of databases Name| Owner | Encoding +--+--- e | postgres | UNICODE regression | postgres | SQL_ASCII template0 | postgres | SQL_ASCII template1 | postgres | SQL_ASCII (5 rows) As soon as we decide whether the canonical name is UTF8 or UTF-8 ;-) we can fix it. I checked and it looks like UTF-8 is the correct usage: http://www.unicode.org/glossary/ -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] left-deep plans?
On Tue, Feb 22, 2005 at 05:40:40PM +1100, Neil Conway wrote: Tom Lane wrote: Yes, and it's been rejected. The notion is obviously bogus; it amounts to assuming that every database is a star schema with only one core table. Interesting; yes, I suppose that's true. Once we get into GEQO territory, we are using the left-deep-only heuristic because that's the only kind of plan GEQO can construct. But at that point you've already given up any notion of exhaustive search. I think most applications would prefer an exhaustive, deterministic search of a subset of the search space over a non-exhaustive, non-deterministic search of the same subset, given approximately the same performance. In other words, if confining the search to left-deep plans allows people to use the normal planner in situations where they would normally be forced to use GEQO to get acceptable performance, I think that would be a win. GEQO is an attempt to provide a near-optimal join order without using an exhaustive search. An exhaustive, deterministic search of a subset of the search space has a non-zero probability of finding only a local minimum in execution time. Most users really are only concerned with final execution time and if the subset is mis-chosen the resulting exhaustive search would completely and possibly disasterously miss the optimal execution time. In a paper on join-order optimization that I read recently, it was shown that non-uniform sampling, with the application of cost-based pruning of the search space, provided good results with quick convergence to within a delta of the optimum plan. This has the nice property that you can balence result quality with the amount of time spent optimizing the join order. The piece missing from the current GECO algorithm is to ensure that once the cost of a join plan is larger (or a piece of a plan), never visit that plan again. The memory/learning piece is what provided the ability to explore much more of the desirable (low execution time) search space. Since purely random selection, with learning, works so well, it may be worth developing a new random join-order optimization algorithm, particularly since most of the genetic features of the GECO we are not using. Ken ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Postgres on VXworks+SH4
Hi! Im considering the possibility of using postgres in embededded environment with VxWorks (RTOS) Hitachi SH4 processor. Can anyone tell me if using postgres on such a platform is possible? You may provide me some reference links for further information too. Thanks in advance. Regards, ~Anupam Chaudhury EPE (Automotive Electronics) WIPRO TECHNOLOGIES +91-80-25502001 ext:3117 09448754390 Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments.
Re: [HACKERS] [PATCHES] [pgsql-hackers-win32] Repleacement for src/port/snprintf.c
On Mon, Feb 21, 2005 at 10:53:08PM -0500, Bruce Momjian wrote:
Applied.
The configure test is a little broken. It needs to quote the
$'s.
I've rewritten the test a little.
Kurt
Index: config/c-library.m4
===
RCS file: /projects/cvsroot/pgsql/config/c-library.m4,v
retrieving revision 1.30
diff -u -r1.30 c-library.m4
--- config/c-library.m4 22 Feb 2005 03:55:50 - 1.30
+++ config/c-library.m4 22 Feb 2005 18:53:23 -
@@ -279,19 +279,17 @@
[AC_MSG_CHECKING([printf supports argument control])
AC_CACHE_VAL(pgac_cv_printf_arg_control,
[AC_TRY_RUN([#include stdio.h
+#include string.h
-int does_printf_have_arg_control()
+int main()
{
char buf[100];
/* can it swap arguments? */
- snprintf(buf, 100, %2$d|%1$d, 3, 4);
- if (strcmp(buf, 4|3) != 0)
-return 0;
- return 1;
-}
-main() {
- exit(! does_printf_have_arg_control());
+ snprintf(buf, 100, %2\$d %1\$d, 3, 4);
+ if (strcmp(buf, 4 3) != 0)
+return 1;
+ return 0;
}],
[pgac_cv_printf_arg_control=yes],
[pgac_cv_printf_arg_control=no],
---(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
[HACKERS] Cannot link to postgres 8.0.0 databases using ODBC from Access
I have cross posted this issue here following suggestion on the pg_odbc list I'd try turning off log_discconnections, and see if you get any joy. If that works and the log still displays problems related to BackendRun, I'd email the pg-hackers list. We have an Access 97 database front end connected via ODBC (V8.00.00.04) Windows 2000 Desktop to a Postgres 8.0.0 backend running on Windows 2000 Server. A long running complex transaction failed possibly because of a timeout. After the failure the server logging was changed to identify the application fault. We then could not unable relink to any database on the server from any client. The error returned is The database does not exist on the server or user authentication failed. (#105). The advice in other similar posts is to check the hba.conf file and database user access (snips below). I cannot see anything wrong with the set up although I see that the postgres server log shows the first two lines with UNKNOWN user and client IP address. The remaining lines are as I would expect. Further investigation has identified a fault that I suppose is applicable to the combination of Postgres 8.0.0 ODBC driver running on Windows 2000 server and desktop. I have two postgresql.conf files, one is the default out-of-the-box postgresql.conf file, the other has many of the logging options turned on (I have put the diff between the two files at the end) The default config works fine, the modified config fails in that the postgres ODBC does not work (tested with Access 97,2000 and Perl ODBC connections) giving the error The database does not exist on the server or user authentication failed. (#105). Only ODBC seems to be affected. All other connections from php, pgadmin work fine. I have demonstrated this problem consistently on two completely different and brand new installations of P8.0.0 (W2K server and desktop). I have tried many (but not all) combinations but have not been able to pin down exactly what combination of settings generates the error. Turning off log_disconnections does not in itself cure the problem. Regards Laurie The diff of the two config files is 207a208 client_min_messages = log 212a214 log_min_messages=info 214a217 log_error_verbosity = verbose 218a222 log_min_error_statement = info 230a235 log_connections = true 231a237 log_disconnections = true 232a239 log_duration = true 240a248,249 log_line_prefix = '%u %d %r %t %c %x' 241a251 log_statement = 'all' hba.conf host all all 127.0.0.1/0 trust host all all 10.35.10.248 255.255.240.0trust and the sample database used in the ODBC link. CREATE DATABASE rjl_pilot WITH OWNER = ldb ENCODING = 'UNICODE' TABLESPACE = pg_default; GRANT ALL ON DATABASE rjl_pilot TO public; GRANT ALL ON DATABASE rjl_pilot TO ldb; The psqllog file extract: conn=159252552, PGAPI_DriverConnect( in)='DSN=rjl_pilot;', fDriverCompletion=1 DSN info: DSN='rjl_pilot',server='za29rugbr',port='5433',dbase ='rjl_pilot',user='ldb',passwd='x' onlyread='0',protocol='6.4',showoid='0',fakeoidindex ='0',showsystable='0' conn_settings='',conn_encoding='OTHER' translation_dll='',translation_option='' Global Options: Version='08.00.0002', fetch=100, socket=4096, unknown_sizes=0, max_varchar_size=254, max_longvarchar_size=8190 disable_optimizer=1, ksqo=1, unique_index=1, use_declarefetch=0 text_as_longvarchar=1, unknowns_as_longvarchar=0, bools_as_char=1 NAMEDATALEN=64 extra_systable_prefixes='dd_;', conn_settings='' conn_encoding='OTHER' conn=159252552, query=' ' NOTICE from backend during send_query: 'LOG: statement: ' NOTICE from backend during send_query: 'LOG: duration: 0.000 ms ' CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect', errnum=105, errmsg='The database does not exist on the server or user authentication failed.' henv=158138320, conn=159252552, status=0, num_stmts=16 sock=158129576, stmts=158109160, lobj_type=-999 Socket Info --- socket=908, reverse=0, errornumber=0, errormsg='(NULL)' buffer_in=159281008, buffer_out=158112112 buffer_filled_in=30, buffer_filled_out=0, buffer_read_in=30 The mylog file extract: [6180] PGAPI_DriverConnect: fDriverCompletion=1, connStrIn ='DSN=rjl_pilot;' [6180]our_connect_string = 'DSN=rjl_pilot;' [6180]attribute = 'DSN', value = 'rjl_pilot' [6180]copyAttributes: DSN='rjl_pilot',server='',dbase='',user='',passwd ='x',port='',onlyread='',protocol='',conn_settings ='',disallow_premature=-1) [6180]globals.extra_systable_prefixes = 'dd_;' [6180]our_connect_string = 'DSN=rjl_pilot;' [6180]attribute = 'DSN', value = 'rjl_pilot' [6180]CopyCommonAttributes:
Re: [HACKERS] [NOVICE] Question on TRUNCATE privleges
The author doesn't mention why he got a 600x increase- perhaps he bypassed the delete triggers which was OK for his situation. I don't like the notion that an optimization requires additional privileges...why not detect an unqualified delete and call truncate instead IFF there are no delete triggers on the table? I'm not entirely sure that requiring ownership of the table is the appropriate restriction for TRUNCATE. It made some sense back when TRUNCATE wasn't transaction-safe, but now that it is, you could almost argue that ordinary DELETE privilege should allow TRUNCATE. Almost. The hole in the argument is that TRUNCATE doesn't run ON DELETE triggers and so it could possibly be used to bypass things the table owner wants to have happen. You could equate TRUNCATE to DROP TRIGGER(s), DELETE, CREATE TRIGGER(s) ... but DROP TRIGGER requires ownership. CREATE TRIGGER only requires TRIGGER privilege which is grantable. So one answer is to change DROP TRIGGER to require TRIGGER privilege (which would mean user A could remove a trigger installed by user B, if both have TRIGGER privileges on the table) and then say you can TRUNCATE if you have both DELETE and TRIGGER privileges. It looks to me like the asymmetry between CREATE TRIGGER and DROP TRIGGER is actually required by SQL99, though, so changing it would be a hard sell (unless SQL2003 fixes it?). Comments anyone? 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
