Re: [HACKERS] HOT WIP Patch - version 1
Bruce Momjian wrote: Just to summarize: o Every tuple gets a heap ctid o Only the root tuple gets an index entry o We can easily remove dead tuples that aren't the root because by definition, nothing points to them, including backends and indexes I am still wondering about the easily here. Basically this needs some kind of wal entry to be crash safe. Else some later tx might reuse the slot: - some update on page produces page image in wal - slot removed - slot reused and comitted - page not written - crash - wal fullpage restores the page to the version before slot removed (- would need a wal replay for slot removed from hot chain here) - wal restores slot reuse, but the slot is now part of a wrong hot chain and the chain is broken (unless we have the above step) Do we have this wal entry ? The problem is that a dead root tuple has to stay around because while no backends can see it, the index does. We could move a live tuple into root ctid slot, but if we do that, the live tuple changes its ctid while it is visible. Could we insert index tuples for the live tuple and then remove the root tuple, perhaps later? So basically we break the chain at that time. The problem there is that we basically have nothing better than what we have now --- we are just delaying the index insert, and I don't see what that buys us. yes, not really promising. Could a _new_ tuple take over the root tuple slot? It is new, so it doesn't have a ctid yet to change. I think that means the index walking could go forward or backward, but on the same page. To illustrate, with ctid slot numbers: [1] root INSERT [2] [3] [1] root INSERT [2] UPDATE [3] [1] root INSERT (dead) [2] UPDATE 1 [3] [1] root UPDATE 2 [2] UPDATE 1 [3] Imho no, because that would need a back pointer in [1] to [2] so that readers arriving at [1] (e.g. through index) can see [2] until [1] is visible. I think we don't want backpointers. (rather go the tuple swapping route) Andreas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] HOT WIP Patch - version 1
Zeugswetter Andreas ADI SD wrote: I am still wondering about the easily here. Basically this needs some kind of wal entry to be crash safe. Else some later tx might reuse the slot: - some update on page produces page image in wal - slot removed - slot reused and comitted - page not written - crash - wal fullpage restores the page to the version before slot removed (- would need a wal replay for slot removed from hot chain here) - wal restores slot reuse, but the slot is now part of a wrong hot chain and the chain is broken (unless we have the above step) Do we have this wal entry ? We already log tuple removals by normal vacuums. We can't use that wal entry as it is: if a dead tuple is in the middle of an update chain, it needs to be unlinked from the chain. But I don't see any particular problem with that, it just needs to be wal logged like every other data changing operation. Do we actually ever want to remove dead tuples from the middle of the chain? If a tuple in the middle of the chain is dead, surely every tuple before it in the chain is dead as well, and we want to remove them as well. I'm thinking, removing tuples from the middle of the chain can be problematic, because we'd need to fiddle with the xmin/xmax of the other tuples to make them match. Or change the tuple-following logic to not do the xmin=xmax check, but it's a nice robustness feature. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] HOT WIP Patch - version 1
On 2/15/07, Heikki Linnakangas [EMAIL PROTECTED] wrote: Do we actually ever want to remove dead tuples from the middle of the chain? If a tuple in the middle of the chain is dead, surely every tuple before it in the chain is dead as well, and we want to remove them as well. I'm thinking, removing tuples from the middle of the chain can be problematic, because we'd need to fiddle with the xmin/xmax of the other tuples to make them match. Or change the tuple-following logic to not do the xmin=xmax check, but it's a nice robustness feature. I am precisely working on this right now. In the next patch version that I intend to send shortly, I am thinking of removing the dead tuples in the middle of the chain. We don't have agreement on how to deal with the root tuple, but we can safely remove the intermediate dead tuples and vacuum them. Also when all the tuples in the chain are dead because the last tuple is either deleted or COLD updated, the entire chain along with the root tuple and the index entry can be vacuumed. The operation must be WAL logged and you caught the xmin/xmax problem very rightly. One option is to change the xmax of root tuple to the xmin of the first live/recently-dead tuple, if we remove a set of intermediate dead tuples. This xmin of the first live/recently-dead tuple is also the xmax of the last dead tuple we removed and hence must be older than the oldtestXmin. So assigning that to the root tuple should not break any visibility rules for the root tuple (it would still be dead). Do we see any problem with this ? Thanks, Pavan -- EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
Bruce, Let me answer to your question. Question 1: How much faster is it ? To answer to your question, I measured execution times of INSERT operations on concurrent accesses on dual-core x 2 CPU (each 2.80GHz), In the experiment, each client issues 5000 INSERT operations concurrently. The following shows the average times (seconds) of 5 measurements. #Cli: The number of concurrent clients P-D: PostgreSQL Default (i.e. usual file system) P-T: PostgreSQL tmpfs S-D: Sigres Default (i.e. usual file system) S-T: Sigres tmpfs P-T/S-T Improve ratio of S-T to P-T The result shows S-T is 10% to 18% faster than P-T. Thus my answer to your question is 10% to 18% when concurrency is from1 to 100. #CliP-DP-TS-DS-TP-T/S-T 1 1.72 0.50 0.46 0.45 1.10 2 2.87 0.62 0.58 0.54 1.15 3 3.08 0.89 0.77 0.77 1.15 4 3.14 0.98 0.86 0.84 1.16 5 3.31 1.23 1.09 1.07 1.15 6 3.57 1.44 1.31 1.27 1.14 7 3.91 1.68 1.51 1.48 1.14 8 4.49 1.89 1.71 1.67 1.13 9 4.78 2.21 1.91.92 1.15 10 5.33 2.47 2.22 2.14 1.15 20 11.50 5.66 5.16 4.86 1.16 50 32.96 16.54 14.92 13.97 1.18 100 79.60 43.71 39.55 38.38 1.14 Question 2: Is that worth adding extra code to improve it ? Yes, I think it is worth. It is because in the case of commercial DBMS, only 5% improvement is achieved with version-up. BTW, I and a friend of mine try to design implement a parallel access way to the wal buffer on a shared memory. I think this is promising direction since WALInsertLock is more frequently issued than WALWriteLock, and the number of CPU-cores will increase definitely. -- Hideyuki Bruce Momjian wrote: Hideyuki Kawashima wrote: Bruce, Thanks for your comments, and let me answer to your question. Sigres is *not* significantly faster than just creating a file system on the permanent memory and putting xlog on there. Sigres is slightly faster than the case because each backend does not call XLogWrite while bgWriter does. The question then is how much faster is it, and is that worth adding extra code to improve it. --- -- Hideyuki Bruce Momjian wrote: Tom Lane wrote: Gene [EMAIL PROTECTED] writes: ... just my two cents. on a side note, would putting the wal on a tmpfs partition give you something similar? Indeed, I'm wondering why one needs to hack the Postgres core to throw away data integrity guarantees; there are plenty of ways to do that already :-(. Hideyuki-san has not explained exactly what integrity assumptions he wants to make or not make. I'm surely willing to listen to supporting a different set of assumptions than we currently use, but I'd like to see a clear explanation of what assumptions are being made and why they represent a useful case. I am unsure why Sigres is significantly faster than just creating a file system on the permanent memory and putting xlog on there. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- Hideyuki Kawashima (Ph.D), University of Tsukuba, Graduate School of Systems and Information Engineering Assistant Professor, TEL: +81-29-853-5322 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
Tom, In my experience, in last April, a BBWC solution did not accelerate PostgreSQL well. The device which I tried was i-ram by gigabyte (http://techreport.com/reviews/2006q1/gigabyte-iram/index.x?pg=1 ). The i-ram showed only a little performance improvement compared to PostgreSQL with fsync to disk. (However, in then case of PostgreSQL fsync=off, the performance improvement was great). Thus I think Sigres is better than BBWC, to the best of my knowledge. However, I do not know other BBWC technologies such as HP smart array E200 controller. (http://h18004.www1.hp.com/products/servers/proliantstorage/arraycontrollers/smartarraye200/index.html) So, I am sorry if I describe wrong conclusion. Best Regards, -- Hideyuki Tom Lane wrote: Gene [EMAIL PROTECTED] writes: I was curious to see how postgres would perform with wal on a tmpfs vs disk here are some numbers I got from pgbench. Let me know if I did something stupid, this is the first time I've used pgbench. The wal on tmpfs method is not significantly faster. This comparison is not very useful because you were using battery-backed write cache, which gives pretty much all the performance improvement that is to be looked for in this area. Try it against a plain vanilla disk drive (that's not lying about write complete) and you'll find the maximum TPS rate is closely related to the disk's rotation rate. At the same time though, the existence of BBWC solutions makes me wonder why we need another. regards, tom lane -- Hideyuki Kawashima (Ph.D), University of Tsukuba, Graduate School of Systems and Information Engineering Assistant Professor, TEL: +81-29-853-5322 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] ERROR: failed to build any 8-way joins
Interesting problem, seems to be planer related:
select 1
from beitraege bei,
b_zuordnungen bz,
(select bei_id
from b_zuordnungen bz,
ben_zuordnungen z,
strukturelemente se
where se.id = z.str_id
and se.sty_id = (select id from strukturtypen where code='GEO')
and z.str_id = bz.str_id
and z.ben_id = 100
union
select id from beitraege where kz_edit 'N' and useraend = 100
) as foo
where bz.bei_id = bei.id
and foo.bei_id = bei.id
and bei.red_id in (select gba.grp_id
from grp_ben_applikationen gba,
grp_gruppen grp
where grp.id = gba.grp_id
and grp.kz_aktiv='J'
and gba.app_id in (select id from grp_applikationen where
code in ('app1', 'app2'))
and gba.ben_id = 100)
and (bei.bei_id_frei is null or bei.kz_edit='N')
and (bei.bt_id, bz.str_id) in ((96,1259036), (96,2688382) )
and bei.red_id=112
and bei.id in (
select bzu.bei_id
from b_zuordnungen bzu,
strukturelemente se
where bzu.str_id = se.id
and se.id = 1773715
)
and bei.id=10157309;
ERROR: failed to build any 8-way joins
Interesting: remove any of the above where conditions solves the problem go
away, e.g. removing and bei.id=10157309.
Testet with 8.2.1 and 8.1.4, same effect on both systems. Because of the large
number of tables involved it's difficult to find a self contained patch, but if
necessary I'll give it a try.
I could give 8.2.3 a try, but I doubt this will help.
Any ideas?
Regards
Mario Weilguni
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Plan for compressed varlena headers
Tom Lane [EMAIL PROTECTED] writes:
Gregory Stark [EMAIL PROTECTED] writes:
1) Replace the VARATT_SIZEP macro with SET_VARLENA_LEN.
If we're going to do this then it's time to play the name game;
Least...fun...game...evar...
A first-cut proposal:
VARHDRSZsame as now, ie, size of 4-byte header
VARSIZE(x) for *reading* a 4-byte-header length word
VARDATA(x) same as now, ie, ptr + 4 bytes
SET_VARSIZE(x, len) for *writing* a 4-byte-header length word
There's also VARATT_CDATA which I suppose I should rename to VARCDATA. I
may not even need it once I hit tuptoaster.c since that file works directly
with the structure members anyways.
I supposed we also rename VARATT_IS_{COMPRESSED,EXTERNAL,EXTENDED} ?
Is VAR_IS_* ok or does that sound too generic?
We'll also need names for the macros that can read the length and find
the data of a datum in either-1-or-4-byte-header format. These should
probably be named as variants of VARSIZE and VARDATA, but I'm not sure
what exactly; any thoughts?
I can't think of any good names for the automatic macros. Right now I have
VARSIZE_ANY(ptr) but that doesn't seem particularly pleasing.
For the internal macros for each specific size I have:
#define VARDATA_4B(PTR) ((PTR)-va_4byte.va_data)
#define VARDATA_2B(PTR) ((PTR)-va_2byte.va_data)
#define VARDATA_1B(PTR) ((PTR)-va_1byte.va_data)
#define VARSIZE_IS_4B(PTR) ((PTR)-va_1byte.va_header ~0x3F ==
0x00)
#define VARSIZE_IS_2B(PTR) ((PTR)-va_1byte.va_header ~0x1F ==
0x20)
#define VARSIZE_IS_1B(PTR) ((PTR)-va_1byte.va_header ~0x7F ==
0x80)
#define VARSIZE_4B(PTR) (ntohl((PTR)-va_4byte.va_header)
0x3FFF)
#define VARSIZE_2B(PTR) (ntohs((PTR)-va_2byte.va_header)
0x1FFF)
#define VARSIZE_1B(PTR) ( ((PTR)-va_1byte.va_header)
0x7F)
#define SET_VARSIZE_4B(PTR,len) ((PTR)-va_4byte.va_header = htonl(len))
#define SET_VARSIZE_2B(PTR,len) ((PTR)-va_2byte.va_header = htons((len) |
0x2000))
#define SET_VARSIZE_1B(PTR,len) ((PTR)-va_1byte.va_header = (len) | 0x80)
I had a separate version for little-endian but it was driving me nuts having
two versions to keep tweaking. I also had the magic constants as #defines but
it really didn't enhance readability at all so I took them out when I rewrote
this just now.
Incidentally I profiled htonl against a right shift on my machine (an intel
2Ghz core duo). htonl is four times slower but that's 3.2ns versus 0.8ns.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] ERROR: failed to build any 8-way joins
Mario Weilguni wrote: Interesting: remove any of the above where conditions solves the problem go away, e.g. removing and bei.id=10157309. Testet with 8.2.1 and 8.1.4, same effect on both systems. Because of the large number of tables involved it's difficult to find a self contained patch, but if necessary I'll give it a try. I could give 8.2.3 a try, but I doubt this will help. I think a similar problem was fixed after 8.2.3 was released, so you may want to check out the REL8_2_STABLE branch. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] ERROR: failed to build any 8-way joins
Thanks for the info. Is there a fix for 8.1 branch, the production system is still 8.1. Regards Mario Weilguni Am Donnerstag, 15. Februar 2007 16:25 schrieb Alvaro Herrera: Mario Weilguni wrote: Interesting: remove any of the above where conditions solves the problem go away, e.g. removing and bei.id=10157309. Testet with 8.2.1 and 8.1.4, same effect on both systems. Because of the large number of tables involved it's difficult to find a self contained patch, but if necessary I'll give it a try. I could give 8.2.3 a try, but I doubt this will help. I think a similar problem was fixed after 8.2.3 was released, so you may want to check out the REL8_2_STABLE branch. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Plan for compressed varlena headers
Gregory Stark [EMAIL PROTECTED] writes:
There's also VARATT_CDATA which I suppose I should rename to VARCDATA. I
may not even need it once I hit tuptoaster.c since that file works directly
with the structure members anyways.
I supposed we also rename VARATT_IS_{COMPRESSED,EXTERNAL,EXTENDED} ?
Is VAR_IS_* ok or does that sound too generic?
I think the VARATT_xxx names are OK for stuff that is intended to be
private to the TOAST-related code (or at least not intended for
widespread use). Maybe part of the problem is just that postgres.h
fails to document which macros are preferred for widespread use and
which are semi-private.
For the internal macros for each specific size I have:
#define VARDATA_4B(PTR) ((PTR)-va_4byte.va_data)
#define VARDATA_2B(PTR) ((PTR)-va_2byte.va_data)
#define VARDATA_1B(PTR) ((PTR)-va_1byte.va_data)
I thought we had abandoned the 2-byte-header variant? Maybe you need to
start a separate thread about exactly which of the bit-level proposals
you want to implement. There were quite a few tradeoffs discussed in
the previous thread IIRC.
Incidentally I profiled htonl against a right shift on my machine (an intel
2Ghz core duo). htonl is four times slower but that's 3.2ns versus 0.8ns.
Yeah, but what about machines that have stupider compilers, or maybe
htonl isn't inlined at all? With a shift you pretty much know what
you're getting, with htonl I'm not so sure.
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] ERROR: failed to build any 8-way joins
Mario Weilguni [EMAIL PROTECTED] writes: ERROR: failed to build any 8-way joins Could you provide a self-contained test case for this? You probably don't need any data, just the table schemas. I fixed a problem with a similar symptom a couple days ago, but that was in logic that was new in 8.2 ... regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] pg_restore fails with a custom backup file
On Fri, Dec 29, 2006 at 05:30:48PM +0100, Magnus Hagander wrote:
On Tue, Dec 19, 2006 at 04:58:22PM +0100, Zeugswetter Andreas ADI SD wrote:
MinGW has fseeko64 and ftello64 with off64_t.
Maybe we need separate macros for MSVC and MinGW. Given the other
You mean something quick and dirty like this ? That would work.
Yes, except does that actually work? If so you found the place in the
headers to stick it without breaking things that I couldn't find ;-)
Compiles clean without warnings on MinGW, but not tested, sorry also no
time.
Does not compile on my MinGW - errors in the system headers (unistd.h,
io.h) due to changing the argument format for chsize(). The change of
off_t propagated into parts of the system headers, thus chaos was
ensured.
I still think we need to use a pgoff_t. Will look at combining these two
approaches.
Here's a patch that tries this.
*needs more testing*. But built with this patch, I can dump and
restore a table at the end of a 10gb database without errors.
Does the method/patch seem reasonable? Anybody else who can run a couple
of tests on it?
//Magnus
Index: src/bin/pg_dump/pg_backup_archiver.c
===
RCS file: /projects/cvsroot/pgsql/src/bin/pg_dump/pg_backup_archiver.c,v
retrieving revision 1.141
diff -c -r1.141 pg_backup_archiver.c
*** src/bin/pg_dump/pg_backup_archiver.c1 Feb 2007 19:10:28 -
1.141
--- src/bin/pg_dump/pg_backup_archiver.c11 Feb 2007 15:00:55 -
***
*** 1311,1334
}
size_t
! WriteOffset(ArchiveHandle *AH, off_t o, int wasSet)
{
int off;
/* Save the flag */
(*AH-WriteBytePtr) (AH, wasSet);
! /* Write out off_t smallest byte first, prevents endian mismatch */
! for (off = 0; off sizeof(off_t); off++)
{
(*AH-WriteBytePtr) (AH, o 0xFF);
o = 8;
}
! return sizeof(off_t) + 1;
}
int
! ReadOffset(ArchiveHandle *AH, off_t *o)
{
int i;
int off;
--- 1311,1334
}
size_t
! WriteOffset(ArchiveHandle *AH, pgoff_t o, int wasSet)
{
int off;
/* Save the flag */
(*AH-WriteBytePtr) (AH, wasSet);
! /* Write out pgoff_t smallest byte first, prevents endian mismatch */
! for (off = 0; off sizeof(pgoff_t); off++)
{
(*AH-WriteBytePtr) (AH, o 0xFF);
o = 8;
}
! return sizeof(pgoff_t) + 1;
}
int
! ReadOffset(ArchiveHandle *AH, pgoff_t *o)
{
int i;
int off;
***
*** 1348,1355
else if (i == 0)
return K_OFFSET_NO_DATA;
! /* Cast to off_t because it was written as an int. */
! *o = (off_t) i;
return K_OFFSET_POS_SET;
}
--- 1348,1355
else if (i == 0)
return K_OFFSET_NO_DATA;
! /* Cast to pgoff_t because it was written as an int. */
! *o = (pgoff_t) i;
return K_OFFSET_POS_SET;
}
***
*** 1379,1386
*/
for (off = 0; off AH-offSize; off++)
{
! if (off sizeof(off_t))
! *o |= ((off_t) ((*AH-ReadBytePtr) (AH))) (off * 8);
else
{
if ((*AH-ReadBytePtr) (AH) != 0)
--- 1379,1386
*/
for (off = 0; off AH-offSize; off++)
{
! if (off sizeof(pgoff_t))
! *o |= ((pgoff_t) ((*AH-ReadBytePtr) (AH))) (off *
8);
else
{
if ((*AH-ReadBytePtr) (AH) != 0)
***
*** 1647,1653
AH-createDate = time(NULL);
AH-intSize = sizeof(int);
! AH-offSize = sizeof(off_t);
if (FileSpec)
{
AH-fSpec = strdup(FileSpec);
--- 1647,1653
AH-createDate = time(NULL);
AH-intSize = sizeof(int);
! AH-offSize = sizeof(pgoff_t);
if (FileSpec)
{
AH-fSpec = strdup(FileSpec);
***
*** 2768,2778
if (fseeko(fp, 0, SEEK_CUR) != 0)
return false;
! else if (sizeof(off_t) sizeof(long))
/*
!* At this point, off_t is too large for long, so we return
based on
!* whether an off_t version of fseek is available.
*/
#ifdef HAVE_FSEEKO
return true;
--- 2768,2778
if (fseeko(fp, 0, SEEK_CUR) != 0)
return false;
! else if (sizeof(pgoff_t) sizeof(long))
/*
!* At this point, pgoff_t is too large for long, so we
Re: [HACKERS] pg_restore fails with a custom backup file
Hi Magnus-san. Great!! Although not tested yet, I seem to equip it with the tolerance to 32GB.? P.S) In Japan, there is a user who is employing 300GB of database on Windows2003. I have received some problems other than this. however, this user does not permit public presentation of the information Then, I have asked that the information is exhibited. ..There is no still good reply. Regards, Hiroshi Saito On Fri, Dec 29, 2006 at 05:30:48PM +0100, Magnus Hagander wrote: On Tue, Dec 19, 2006 at 04:58:22PM +0100, Zeugswetter Andreas ADI SD wrote: MinGW has fseeko64 and ftello64 with off64_t. Maybe we need separate macros for MSVC and MinGW. Given the other You mean something quick and dirty like this ? That would work. Yes, except does that actually work? If so you found the place in the headers to stick it without breaking things that I couldn't find ;-) Compiles clean without warnings on MinGW, but not tested, sorry also no time. Does not compile on my MinGW - errors in the system headers (unistd.h, io.h) due to changing the argument format for chsize(). The change of off_t propagated into parts of the system headers, thus chaos was ensured. I still think we need to use a pgoff_t. Will look at combining these two approaches. Here's a patch that tries this. *needs more testing*. But built with this patch, I can dump and restore a table at the end of a 10gb database without errors. Does the method/patch seem reasonable? Anybody else who can run a couple of tests on it? //Magnus ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Plan for compressed varlena headers
Tom Lane [EMAIL PROTECTED] writes: #define VARDATA_4B(PTR) ((PTR)-va_4byte.va_data) #define VARDATA_2B(PTR) ((PTR)-va_2byte.va_data) #define VARDATA_1B(PTR) ((PTR)-va_1byte.va_data) I thought we had abandoned the 2-byte-header variant? Hm, I don't remember anyone saying that. I had actually considered doing that but concluded we couldn't or there wouldn't enough bits to indicate inline compression. Now that I think about it we could just do it but I don't see much of a case to do it. Maybe you need to start a separate thread about exactly which of the bit-level proposals you want to implement. There were quite a few tradeoffs discussed in the previous thread IIRC. If I get the macro api right we can flip around what things indicate easily enough. Currently I'm just doing the second of the two you posted. That's the one with the hard coded external toast datum size but able to handle 1-byte headers for data up to 127 bytes long. The other one you posted had one fewer cases for deform_tuple to consider but only handled datums up to 63 bytes long in single byte headers. Both of those were uniformly better than the previous alternatives. Incidentally I profiled htonl against a right shift on my machine (an intel 2Ghz core duo). htonl is four times slower but that's 3.2ns versus 0.8ns. Yeah, but what about machines that have stupider compilers, or maybe htonl isn't inlined at all? With a shift you pretty much know what you're getting, with htonl I'm not so sure. I'm not against doing the shifting, I'm just going to get this working first and then we can always add a separate set of equivalent macros for little-endian machines. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Plan for compressed varlena headers
On Thu, Feb 15, 2007 at 10:42:49AM -0500, Tom Lane wrote: #define VARDATA_4B(PTR) ((PTR)-va_4byte.va_data) #define VARDATA_2B(PTR) ((PTR)-va_2byte.va_data) #define VARDATA_1B(PTR) ((PTR)-va_1byte.va_data) I thought we had abandoned the 2-byte-header variant? Maybe you need to start a separate thread about exactly which of the bit-level proposals you want to implement. There were quite a few tradeoffs discussed in the previous thread IIRC. I agreed with Tom in the last thread. The 2 byte case doesn't seem like good value for the return. Simpler analysis results in easier to optimize code for the compiler, and less complexity stored on disk. Please remove 2B. :-) Cheers, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] automatic password for pg_dump to be used for a batch file in vb6
i want to add database backup to my program i developed in visual basic 6, of course i can save the syntax for pg_dump in a batch file and call it inside vb6 via shell, but my problem is that the batch file executes but stops to prompt for a password. how can i supply a password to it automatically or i mean within visual basic 6 so that no user interaction is needed, i don't mind about the security since only the administrator has access for the program. please, any help will be greatly appreciated. thank you. oliver A man is what he is, not what he used to be Looking for earth-friendly autos? Browse Top Cars by Green Rating at Yahoo! Autos' Green Center. http://autos.yahoo.com/green_center/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Visual C++ function issues
Hi,
I'm trying to implement the add_one sample in the PG docs using VC++
(for purposes of debugging). After some pain (had to add the
dllexport declspec below), I am able to get the CREATE FUNCTION
command to recognize the library and function. However, the function
blows up. I am able to attach the postgres.exe process and debug this
dll (I set a breakpoint on the int x = 1 line). I do get that far,
but then blow up trying to retrieve my argument on the following
line. Digging into this, I find that the fcinfo struct being passed
to me as an address of 0x02 - that looks a little non-sensical to
me.
I am wondering if I've got the necessary project settings in Visual
Studio. How do we create a VC++ dll that is compatible with the gcc
PG build? Does anyone have a clue as to what these might be? I have
been doing a lot of digging and haven't seen anything directly
addressing this issue.
Many thanksEric
__declspec(dllexport)
Datum
add_one(PG_FUNCTION_ARGS)
{
int x = 1;
int32 arg = PG_GETARG_INT32(0);
PG_RETURN_INT32(arg + 1);
}
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [HACKERS] anyelement2 pseudotype
Tom Dunstan wrote: Tom Lane wrote: As for actually adding it, grep for all references to ANYELEMENT and add code accordingly; shouldn't be that hard. Note you'd need to add an anyarray2 at the same time for things to keep working sanely. The enum patch [1] does exactly this with an ANYENUM pseudo-type. It should provide a pretty good overview of what will be required. Whoops. I just had a look at the mail that Matt referenced at the top of this thread. An anyelement2 would require a bit more than what anyenum does, as the type-matching code that ensures that all generic args are of the same type would have to be changed, unlike anyenum. Hope I didn't lead you down the wrong path, Matt. OTOH, following the enum patch should land you in roughly the right areas, and you'd still need to add ANYELEMENT2 references in all the places that I had to add ANYENUM as well. Cheers Tom ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Writing triggers in C++
On Feb 14, 11:26 am, [EMAIL PROTECTED] (Florian G. Pflug) wrote: Neil Conway wrote: On Wed, 2007-02-14 at 13:19 -0300, Alvaro Herrera wrote: Probably stack allocation doesn't matter much, as I think that would be unwinded by the longjmp call. I don't know a lot about C++, but if there are allocations in the data area then those would probably not be freed. But it makes me wonder -- is longjmp very compatible with C++ exceptions at all? C-style stack unwinding (using setjmp and longjmp from csetjmp) is incompatible with exception-handling and is best avoided. (Stroustrup, p. 433). Which presumably means that in practice, the interaction between these features is implementation-defined. Well, as long as you don't longjmp past an C++ catch block, and don't throw an C++ exception past an setjmp handler, there should be no problem I think. Or at least I can't imagine how a problem could arise.. Also, don't jump out of (past) the scope of any local variable with a destructor. If you are in a C++ program, use exceptions. If you are in a C program, fake the equivalent using setjmp/longjmp. Don't mix the two - it's too tricky. -- Bjarne Stroustrup; http://www.research.att.com/~bs ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] automatic password for pg_dump to be used for a batch file in vb6
oliver rombaoa wrote: i want to add database backup to my program i developed in visual basic 6, of course i can save the syntax for pg_dump in a batch file and call it inside vb6 via shell, but my problem is that the batch file executes but stops to prompt for a password. how can i supply a password to it automatically or i mean within visual basic 6 so that no user interaction is needed, i don't mind about the security since only the administrator has access for the program. please, any help will be greatly appreciated. thank you. 1. pgsql-hackers is not really the right list to ask this question. In future, please ask usage question on pgsql-general 2. have your vb program write out a pgpass file, if necessary also setting up the PGPASSFILE environment setting in the .bat file to point to it. 2-a. alternatively, use some auth method that does not require use of passwords. cheers andrew ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] automatic password for pg_dump to be used for a batch file in vb6
oliver rombaoa wrote: i want to add database backup to my program i developed in visual basic 6, of course i can save the syntax for pg_dump in a batch file and call it inside vb6 via shell, but my problem is that the batch file executes but stops to prompt for a password. how can i supply a password to it automatically or i mean within visual basic 6 so that no user interaction is needed, i don't mind about the security since only the administrator has access for the program. please, any help will be greatly appreciated. thank you. Use the pgpass.conf file or the PGPASSWORD environment variable to supply the password. //Magnus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] automatic password for pg_dump to be used for a batch file in vb6
Firstly, this is the wrong list; this one is to discuss the development OF postgres, NOT with. You need novice or general. Secondly: look for pgpass in the documentation ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Visual C++ function issues
[EMAIL PROTECTED] wrote: Digging into this, I find that the fcinfo struct being passed to me as an address of 0x02 - that looks a little non-sensical to me. Perhaps you forgot PG_FUNCTION_INFO_V1(). -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Fixing insecure security definer functions
On 2/13/07, Tom Lane [EMAIL PROTECTED] wrote: I would suggest that the search path be added as an explicit parameter to CREATE FUNCTION, with a default of the current setting. The main reason for this is that it's going to be a real PITA for pg_dump if we don't allow an explicit specification. yikes! If you guys go through with forcing functions to attach to objects when they are created, it will break almost every project I've ever worked on :(. The schema/function combo fits into all kinds of de facto partitioning strategies and organization methods. I can understand invalidating plans when the search_path changes, though. merlin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] anyelement2 pseudotype
Tom Lane wrote:
Actually ... now that I re-read that remark, I think you may have done
the wrong things with ANYENUM. I think that ANYENUM may in fact be
closer to ANYARRAY than it is to ANYELEMENT, because ANYELEMENT pretty
nearly means anything at all whereas ANYARRAY identifies a subset of
types that share some properties, which is an accurate description of
ANYENUM as well. In particular, it is sensible to have b-tree index
opclasses that are declared to operate on ANYARRAY. If you've
got b-tree support for ANYENUM, as I hope you do, then you'll have to
patch that same spot in ri_triggers that now knows about ANYARRAY.
So you might want to take another pass through the code and see if you
shouldn't be modeling ANYENUM more closely on ANYARRAY than ANYELEMENT.
OK, thanks, I'll do that. Of course, they get used together all over the
place as well, lots of
if(typiod == ANYARRAY || typoid == ANYELEMENT) {
type of stuff in the code.
I do have b-tree (and hash) support for enums, so it sounds like I'll
have to hit the same spot. I've got what I thought was a reasonably
comprehensive test of all the enum features which passes make check, so
if there's a likely failure in that code then I'm missing a test
somewhere. Did you have a test case for the ri_triggers stuff that you
did? What's going to fail?
Thanks
Tom
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] anyelement2 pseudotype
Tom Dunstan [EMAIL PROTECTED] writes: I do have b-tree (and hash) support for enums, so it sounds like I'll have to hit the same spot. I've got what I thought was a reasonably comprehensive test of all the enum features which passes make check, so if there's a likely failure in that code then I'm missing a test somewhere. Did you have a test case for the ri_triggers stuff that you did? What's going to fail? ri_HashCompareOp, which I think is mainly invoked in cases of UPDATEing a PK or FK row (to see whether the relevant columns changed). If one of the columns is an enum type, it's going to need to be able to realize that coercing that to ANYENUM is a no-op. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] anyelement2 pseudotype
Come to think of it, we really do need some refactoring in
parse_coerce.c. I just realized what CVS HEAD's RI code
does with array types:
regression=# create table aa (f1 int[] primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index aa_pkey for
table aa
CREATE TABLE
regression=# create table bb (b1 real[] references aa);
CREATE TABLE
regression=# insert into bb values('{1,1}');
ERROR: operator does not exist: integer[] pg_catalog.= real[]
It should have rejected the FK constraint right off the bat, but the
test in ATAddForeignKeyConstraint is effectively just does real[]
coerce to anyarray which is not good enough in this context. Your
patch will have the same misbehavior: it'll allow an FK reference to a
different enum type to be declared, but then fail at runtime.
So it seems neither can_coerce_type() nor find_coercion_pathway() are
really particularly well thought out in terms of what they test or don't
test. I'm not very sure what a good refactoring would look like,
but I am sure that I don't want all their call sites having to
individually account for ANYfoo types. Any thoughts?
regards, tom lane
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [HACKERS] anyelement2 pseudotype
Tom Lane wrote: Come to think of it, we really do need some refactoring in parse_coerce.c. [snip] I'm not very sure what a good refactoring would look like, but I am sure that I don't want all their call sites having to individually account for ANYfoo types. Any thoughts? I was just thinking earlier that we need some sort of ANYany(oid) test. I guess a very simple minded approach would just macro expand it, to something like what's there now, or if we were more adventurous we could rearrange things so that a bitmask test would work. cheers andrew ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Chatter on DROP SOMETHING IF EXISTS
On Thu, Feb 08, 2007 at 01:54:13PM -0500, Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: I'm honestly looking for some practical use of this. We have debated other NOTICE messages over the years, but they at least tell you something you can use after the command. The objection I had to the original patch (which didn't return a notice) was that this seemed actively misleading: foo= DROP TABLE IF EXISTS not_there; DROP TABLE foo= I would be satisfied if the returned command tag were something else, maybe NO OPERATION. TABLE blah DID NOT EXIST might be less confusing... -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Fixing insecure security definer functions
Merlin Moncure [EMAIL PROTECTED] writes: yikes! If you guys go through with forcing functions to attach to objects when they are created, it will break almost every project I've ever worked on :(. The schema/function combo fits into all kinds of de facto partitioning strategies and organization methods. If you read a bit further, I did suggest providing an option to retain the current behavior. I don't think it should be the default though. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
Joshua, I revised. Now Sigres can be activated by setting sigres = on in postgresql.conf. You can download the version (0.1.2) from http://sourceforge.jp/projects/sigres . And, I attach the diff between PostgreSQL-8.2.1 and Sigres-0.1.2 to this mail. Thanks for your comments. -- Hideyuki Joshua D. Drake wrote: Hideyuki Kawashima wrote: Joshua, I appreciate your great suggestion! It is great honor for me if Sigres will be merged to PostgreSQL. Since the changes of Sigres from PostgreSQL-8.2.1 are not many, and moreover, all of changes are surrounded with #ifdef SIGRES --- #endif, incorporating Sigres into PostgreSQL would be easy. The best way is to create a patch against -head and submit that patch with a complete description of why, and what. If you have test cases that show the improvement all the better. I would suggest though if you are going to submit the patch that you take a look at how you could disable/enable the feature within the postgresql.conf via a guc. Sincerely, Joshua D. Drake However, Sigres modifies WAL which is the most important point of DBMS on stability. Although I myself could not find any bugs in Sigres, I am really afraid of it. It a bug exists on Sigres, it puts everyone to huge inconvenience... Therefore, before incorporating Sigres into PostgreSQL, the code must be checked, and the behaviors of Sigres must be checked carefully. Since PostgreSQL is a famous and wide spread software, I strongly want to avoid losing its great reputation. Unfortunately in Japan, I do not know any WAL hackers except for a friend of mine, and he is too busy to check Sigres. So, if pgsql-hackers checks Sigres, I am really happy. Best Regards, -- Hideyuki Joshua D. Drake wrote: Hideyuki Kawashima wrote: Joshua, :) The reason why I made the Sigres is, the advances of recent non volatile memories. Just now we do not usually use non volatile memories. But in the near future, situation would change. I think if a non volatile memories can be considered as a persistence device, PostgreSQL WAL mechanism should be modified. However, I do not use such devices usually. Thus I made Sigres which requires UPS. This is actually very interesting. We (www.commandprompt.com) have had several customers ask us how we can make PostgreSQL more reasonable within a flash environment. I agree with you that in the future you will see many such databases including PostgreSQL living on these devices. Tom? What do you think? Is there some room for movement here within the postgresql.conf to make something like sigres usable within PostgreSQL proper? Currently I have just ignored XLogWrite and WALWriteLock, but a friend of mine (a Japanese great hacker of PostgreSQL) has more idea to improve WAL if a battery supplied memory can be considered as a persistent device. We are coming up very quickly on a feature freeze for the next version of PostgreSQL. If... we can has something out quickly enough and in a thought out fashion, the hackers may be willing to accept a patch for 8.3.. If not there is always 8.4.. Sincerely, Joshua D. Drake -- Hideyuki Kawashima (Ph.D), University of Tsukuba, Graduate School of Systems and Information Engineering Assistant Professor, TEL: +81-29-853-5322 Only in sigres-0.1.2: CHANGELOG Only in postgresql-8.2.1: README Only in sigres-0.1.2: README.pgsql Only in sigres-0.1.2: README.sigres diff -c -r postgresql-8.2.1/src/backend/access/transam/xlog.c sigres-0.1.2/src/backend/access/transam/xlog.c *** postgresql-8.2.1/src/backend/access/transam/xlog.c Fri Dec 1 03:29:11 2006 --- sigres-0.1.2/src/backend/access/transam/xlog.c Fri Feb 16 09:45:05 2007 *** *** 10,15 --- 10,18 * $PostgreSQL: pgsql/src/backend/access/transam/xlog.c,v 1.258 2006/11/30 18:29:11 tgl Exp $ * *- + * + * Extended to Sigres by Hideyuki Kawashima ([EMAIL PROTECTED]) + * */ #include postgres.h *** *** 142,147 --- 145,154 bool XLOG_DEBUG = false; #endif + /* Entities are in globals.c, for SIGRES */ + extern int BgWriterPid; + extern bool enableSigres; + /* * XLOGfileslop is used in the code as the allowed fuzz in the number of * preallocated XLOG segments --- we try to have at least XLOGfiles advance *** *** 474,480 static bool XLogCheckBuffer(XLogRecData *rdata, bool doPageWrites, XLogRecPtr *lsn, BkpBlock *bkpb); static bool AdvanceXLInsertBuffer(bool new_segment); ! static void XLogWrite(XLogwrtRqst WriteRqst, bool flexible, bool xlog_switch); static int XLogFileInit(uint32 log, uint32 seg, bool *use_existent, bool use_lock); static bool InstallXLogFileSegment(uint32 *log, uint32
Re: [HACKERS] pg_restore fails with a custom backup file
Hi, From: Magnus Hagander [EMAIL PROTECTED] Subject: Re: [HACKERS] pg_restore fails with a custom backup file Date: Thu, 15 Feb 2007 17:38:59 +0100 On Fri, Dec 29, 2006 at 05:30:48PM +0100, Magnus Hagander wrote: On Tue, Dec 19, 2006 at 04:58:22PM +0100, Zeugswetter Andreas ADI SD wrote: MinGW has fseeko64 and ftello64 with off64_t. Maybe we need separate macros for MSVC and MinGW. Given the other You mean something quick and dirty like this ? That would work. Yes, except does that actually work? If so you found the place in the headers to stick it without breaking things that I couldn't find ;-) Compiles clean without warnings on MinGW, but not tested, sorry also no time. Does not compile on my MinGW - errors in the system headers (unistd.h, io.h) due to changing the argument format for chsize(). The change of off_t propagated into parts of the system headers, thus chaos was ensured. I still think we need to use a pgoff_t. Will look at combining these two approaches. Here's a patch that tries this. *needs more testing*. But built with this patch, I can dump and restore a table at the end of a 10gb database without errors. I tried the attached patch. But I got the following error. pg_backup_archiver.o(.text+0x1fa4): In function `allocAH': C:/msys/1.0/home/y-asaba/postgresql-8.2.3-patch/src/bin/pg_dump/pg_backup_archiver.c:1580: undefined reference to `fseeko64' ... make[3]: *** [pg_dump] Error 1 $ uname -sr MINGW32_NT-5.1 1.0.10(0.46/3/2) Is MINGW version too old? -- Yoshiyuki Asaba [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
