Re: [HACKERS] enums
On 10/27/05, Andrew Dunstan [EMAIL PROTECTED] wrote: Yes, MySQL is broken in some regards, as usual. However, the API isn't bad (except for the fact that it doesn't care what invalid crap you throw at it), and more importantly there are thousands of apps and developers who think around that interface. We should copy it without the brokenness as much as possible unless we have good cause otherwise. mmm ... no. It is too broken. We should do enums orthogonally with other type definitions in PostgreSQL. Where I would like to get to is that we have a flavor of CREATE TYPE that will create the enum type for us, including all the support that I build into my little kit. And if you want to change the enumeration set on a column, you would use ALTER TABLE foo ALTER COLUMN bar TYPE newtype USING ... eh, Well that we have a reasonable user extensiable type system is reasonable reason. What I was mostly objecting to was the use of lexical collation the don't mess with what people already expect argument was just the most handy strawman available. :) And in doing so you could insert a enum in the middle of the existing list without breaking the values already in the table? If so that would be very useful. Inline declarations of enums does not strike me as good. You're right, it's a property of a type. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] ERROR: invalid memory alloc request size a_big_number_here
Hi Tom, Attached is a completed patch, which I've had no time to test yet, but I have to leave for the evening right now --- so here it is in case anyone is awake and wants to poke at it. The patch was applied correctly only when I reverted Alvaro's first patch, so I suppose it was meant to be an alternative to it. Unfortunately it doesn't solve the invalid alloc request issue. Should I try Alvaro's second patch that you said not going to work? Best regards -- Matteo Beccati http://phpadsnew.com http://phppgads.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Ideas for easier debugging of backend problems
On Thu, Oct 27, 2005 at 11:44:24AM -0500, Jim C. Nasby wrote: The second option would help us where users are stymied by the system trying to change the core size ulimit, why not make it easier? It would also be very good if there was a way to verify that the backend should be able to generate a core, such as being able to see what ulimits the backend is running under. This would have saved me some pain snip Well, I've sent something to -patches that allows you to set an option so you get one of the following messages: NOTICE: Core dumps hard disabled by admin NOTICE: Core dumps already enabled by admin (size) NOTICE: Core limit successfully changed to (size) You use it like: $ PGOPTIONS=-C ./psql test NOTICE: Core limit successfully changed to (unlimited) Welcome to psql 8.1beta2, the PostgreSQL interactive terminal. snip I think a GUC would be a waste of space. It's not like you want to skip the first three segfaults and dump on the fourth. It shouldn't be a global option. It shouldn't be easy to enable, but the option should be there. This way doesn't require any changes to clients, as it can be controlled by the environment. Bloat, I don't know, maybe. I think the gain outweighs the costs, but I'll leave it to TPTB to decide that. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgp4Lj9vuqLtQ.pgp Description: PGP signature
Re: [HACKERS] ERROR: invalid memory alloc request size a_big_number_here
Hi, Should I try Alvaro's second patch that you said not going to work? I'll add that this works for me, that's it prevents invalid alloc requests to show. Best regards -- Matteo Beccati http://phpadsnew.com http://phppgads.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: SOLVED Re: [HACKERS] _penalty gist method invoked with one key
On 2005-10-28, at 07:37, Oleg Bartunov wrote: On Fri, 28 Oct 2005, Christopher Kings-Lynne wrote: Grzegorz - it'd be great if you submitted documentation improvements :) I don't see any GiST specific problem in Grzegorz's case. Other than just stupid bug, I know. It was just hard to find. Luckily, I have test cases for internal functions. This is separate program, that I can valgrind (me hugs valgrind). Valgrind showed me that there is branch based on not initialized value. So I thought, maybe also length isn't initialized there, and I was right. So, here's a tip from me: if your type has variable length, create separate function to locate all memory, and to fill out all fields. In my case it was possible, and helped. Where is about docs, yes, I do plan to put out some tut based on my experiences. I will definitely give it to you guys to review here. Thanks. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] enums
Gregory Maxwell wrote:
And in doing so you could insert a enum in the middle of the existing
list without breaking the values already in the table? If so that
would be very useful.
You do it by altering the column type, not by altering the type itself.
MySQL's way of doing this is made necessary by its horrid non-orthogonal
way of doing enums. Here's how it works in PostgreSQL. (To make this
example work I had to add a text conversion - an inadvertant omission
from the original. This is in a revised version of the enumkit,
available at the same location.)
andrew=# create table foo (i serial, c rgb);
NOTICE: CREATE TABLE will create implicit sequence foo_i_seq for
serial column foo.i
CREATE TABLE
andrew=# insert into foo (c) values ('blue');
INSERT 8711471 1
andrew=# insert into foo (c) values ('green');
INSERT 8711472 1
andrew=# insert into foo (c) values ('red');
INSERT 8711473 1
andrew=# select * from foo order by c;
i | c
---+---
3 | red
2 | green
1 | blue
(3 rows)
andrew=# insert into foo (c) values ('yellow');
ERROR: invalid input value for enum: yellow
andrew=# alter table foo alter column c type rainbow using c::text;
ALTER TABLE
andrew=# select * from foo order by c;
i | c
---+---
3 | red
2 | green
1 | blue
(3 rows)
andrew=# insert into foo (c) values ('yellow');
INSERT 8711477 1
andrew=# select * from foo order by c;
i | c
---+
3 | red
4 | yellow
2 | green
1 | blue
(4 rows)
cheers
andrew
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] enums
Andrew wrote: Jim C. Nasby wrote: Personally, I don't see why enum can't just be syntactic sugar on top of a side-table of values and a foreign key. And I guess a view to hide the internals from normal viewing. That would certainly allow the most flexibility, although it probably wouldn't perform as well as what you wrote. The other issue is ease of use. We used lookup tables in bugzilla when it was converted to work with Postgres. But many users will find having to do that annoying, to say the least. I think there's a very good case for providing true enums. There is a technical part of the puzzle I can't quite see yet, though :-) Hm, I agree with Jim here. IMO, enum=FK syntax sugar...enum should be a lookup table with two fields, one being enum value which is the PK, and two being the sequencing value. I think many people are opposed to this approach because they assume this relationship is via ID-ID link (IIRC this is what mysql does under the hood). In fact, the enum table's only purpose is for constraint checking, not to lookup the value (there is no 'id'). I like the way sequences work. They are first class SQL objects although they are normally accessed via helper functions. Enums could be the same. Dependancy could be preserved to the creating table or not (I prefer not). Merlin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] enums
Rod Taylor wrote: The other issue is ease of use. We used lookup tables in bugzilla when it was converted to work with Postgres. But many users will find having to do that annoying, to say the least. I think there's a very good case for providing true enums. Then why did you use lookup tables instead of a varchar and a constraint? Probably performance. To be honest, I forget why. Possible because we also needed to be able to get a list of allowed values, although I don't know how one does that in mysql. Maybe because it just seemed like a good idea at the time and nobody spoke up against it. A much more general purpose but just as good solution would be the ability to create a hidden surrogate key for a structure. CREATE TABLE status (code varchar(20) PRIMARY KEY) WITH SURROGATE; CREATE TABLE account (name varchar(60), status varchar(20) references status); Behind the scenes (transparent to the user) this gets converted to: CREATE TABLE status (id SERIAL UNIQUE, code varchar(20) PRIMARY KEY) WITH SURROGATE; CREATE TABLE account (name varchar(60), status integer references status(id)); SELECT * FROM account; would be rewritten as SELECT * FROM (SELECT name, code FROM account JOIN status USING (id)) AS account; Enum might be good for a short list of items but something like the above should be good for any common value that we manually create surrogate keys for today but without the clutter or the application needing to know. If PostgreSQL had an updatable view implementation it would be pretty simple to implement. That won't make it easier to change the ordering or the value set, which some people seem concerned about. But it too might be a nice feature. I suspect it would be a lot more work than simple enums, for which there is significant demand. cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] aix build question re: duplicate symbol warning
Tom Lane wrote: I wrote: Hmm. pqStrerror is defined in libpgport (which is linked into the backend) as well as libpq. ISTM that libpq should not be linked with -Wl,-bI:../../../src/backend/postgres.imp, since it's not intended to be loaded into the backend. Without having looked at the code, I'm wondering if the AIX makefiles stick that option into LDFLAGS_SL rather than someplace more restricted. It seems that the right place to put this is BE_DLLLIBS, which is a macro that probably didn't exist when the AIX support was last looked at. But both Windows and Darwin ports use it now, so we may as well bring AIX up to speed. Would you try the attached patch and see if it gets rid of the warnings? Also check that regression tests and contrib build/regression tests still work. Tom, That patch worked, as far as eliminating those duplicate symbol warnings. I have some dependency issues (libintl) that I have to resolve in order to test the build. -Kevin Murphy ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] ERROR: invalid memory alloc request size a_big_number_here
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Ok. I had hoped to reproduce the problem with pristine sources, in order to verify that I was able to show it not appearing with my patch. However I have been unable to create a situation in which the problem appears. So I attach the patch that I came up with. Please test it. On further reflection, this isn't gonna work :-(. The problem with the waste-a-slot approach is that it creates an ambiguity near the offsets wraparound point: if you are looking at an mxid with starting offset just under 2^32, and you see the next mxid has start offset 1, did your mxid include the xid in offset 0 or not? This is certainly a problem, but I think we can just assume that it did and cope later with the possibility that it didn't. Which means that we should allow GetMultiXactIdMembers() check whether one element is InvalidTransactionId, and skip it if so. (AFAICS this should only happen if the MultiXact members ends just before offset 0). I'm currently experimenting with an alternative approach, which leaves the nextOffset arithmetic as it was and instead special-cases the zero offset case this way: I think I understand your approach, but I wonder why Matteo didn't find an improvement with your patch. Maybe there's a bug on it? Were you able to create a test case? I tried several things, including stopping a backend in the middle of creating a MultiXactId, but no luck yet. -- Alvaro Herrerahttp://www.advogato.org/person/alvherre La Primavera ha venido. Nadie sabe como ha sido (A. Machado) ---(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] [GENERAL] aix build question re: duplicate symbol warning
Kevin Murphy wrote: It seems that the right place to put this is BE_DLLLIBS, which is a macro that probably didn't exist when the AIX support was last looked at. But both Windows and Darwin ports use it now, so we may as well bring AIX up to speed. Would you try the attached patch and see if it gets rid of the warnings? Also check that regression tests and contrib build/regression tests still work. Tom, That patch worked, as far as eliminating those duplicate symbol warnings. I have some dependency issues (libintl) that I have to resolve in order to test the build. Or you could build without nls in the first instance. cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] ERROR: invalid memory alloc request size a_big_number_here
Matteo Beccati wrote: Hi, Should I try Alvaro's second patch that you said not going to work? I'll add that this works for me, that's it prevents invalid alloc requests to show. Yeah, the problem with that patch is that there's another, different race condition, of much lower probability. So your original problem is fixed, but there's still a bug. -- Alvaro Herrera Developer, http://www.PostgreSQL.org Just treat us the way you want to be treated + some extra allowance for ignorance.(Michael Brusser) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] ERROR: invalid memory alloc request size a_big_number_here
Alvaro Herrera [EMAIL PROTECTED] writes: I think I understand your approach, but I wonder why Matteo didn't find an improvement with your patch. Maybe there's a bug on it? Yeah, looking at it this morning, I got the retry condition wrong. It might be fixable but I'm less enthused about it than I was last night. Your idea of handling the wraparound ambiguity by ignoring InvalidTransactionId isn't bad --- I'll take a look at that. Were you able to create a test case? I tried several things, including stopping a backend in the middle of creating a MultiXactId, but no luck yet. I've had some success using Tatsuo's new scriptable pgbench: create table t1(f1 int); insert into t1 select * from generate_series(1,1000); create file tscript containing \setrandom n 1 1000 select * from t1 limit :n for share; and do, say, pgbench -c 10 -t 1 -n -f tscript regression Using CVS tip, this generates failures within a few seconds for me. If it doesn't for you, try altering the number of processes (-c) and the setrandom bounds. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] ERROR: invalid memory alloc request size a_big_number_here
I wrote: Your idea of handling the wraparound ambiguity by ignoring InvalidTransactionId isn't bad --- I'll take a look at that. OK, I think this version may actually work, and get the wraparound case right too. It hasn't failed yet on the pgbench test case anyway. Matteo, could you try it on your test case? regards, tom lane bintRKBhTBzqW.bin Description: multixact-3.patch ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] TODO Item - Add system view to show free space map
On Fri, 2005-10-28 at 08:31 -0300, Alvaro Herrera wrote: Simon Riggs wrote: On Fri, 2005-10-28 at 13:21 +1300, Mark Kirkwood wrote: regression=# SELECT c.relname, m.relblocknumber, m.blockfreebytes FROM pg_freespacemap m INNER JOIN pg_class c ON c.relfilenode = m.relfilenode LIMIT 10; I like this, but not because I want to read it myself, but because I want to make autovacuum responsible for re-allocating free space when it runs out. This way we can have an autoFSM feature in 8.2 What do you mean, re-allocating free space? I don't understand what you are proposing. Moving to -hackers. FSM currently focuses on reusing holes in a table. It does nothing to help with the allocation of space for extending tables. There are a few issues with current FSM implementation, IMHO, discussing as usual the very highest end of performance: 1. Data Block Contention: If you have many free blocks in the FSM and many concurrent UPDATE/INSERTers then each gets its own data block and experiences little contention. Once the FSM is used up, each new block is allocated by relation extension. At this point, all UPDATE/INSERTers attempt to use the same block and contention increases as a result. ISTM that if we were to re-fill the FSM with freshly allocated blocks then we would be able to continue without data block contention. (We would still have some index block contention, but that is a separate issue). 2. FSM Contention: As the FSM empties, it takes longer and longer to find a free data block to insert into. When the FSM is empty, the search time to discover that no free blocks are available is O(N), so the freespace lock is held for longer the bigger you make the FSM. So refilling the FSM automatically when it happens seems again like a reasonable way to reduce contention. (Perhaps another way would be simply to alter the search algorithm to make it O(1) when FSM empty, which is simpler than it sounds.) 3. Helping Readahead efficiency: Currently blocks are allocated one at a time. If many tables are extending at the same time, the blocks from multiple tables will be intermixed together on the disk. Reading the data back takes more head movement and reduces the I/O rate. Allocating the blocks on disk in larger chunks would help to reduce that. Doing so would require us to keep track of that, which is exactly what the FSM already does for us. So automatically refilling the FSM seems like a possible way of doing that since the FSM effectively tracks which relations extend frequently and for whom larger allocations would be a win. (Larger allocations in all cases would give very poor disk usage that we might call fragmentation, if we can avoid debating that word) There are other solutions to the above issues, so I really should have started with the above as a problem statement rather than driving straight to a partially thought through solution. Do we agree those problems exist? (I'm not intending to work on these issues myself anytime soon, so happy for others to go for it.) Best Regards, Simon Riggs ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [GENERAL] aix build question re: duplicate symbol warning
Hmm. pqStrerror is defined in libpgport (which is linked into the backend) as well as libpq. ISTM that libpq should not be linked with -Wl,-bI:../../../src/backend/postgres.imp, since it's not intended to be loaded into the backend. Without having looked at the code, I'm wondering if the AIX makefiles stick that option into LDFLAGS_SL rather than someplace more restricted. It seems that the right place to put this is BE_DLLLIBS, which is a macro that probably didn't exist when the AIX support was last looked at. But both Windows and Darwin yes ports use it now, so we may as well bring AIX up to speed. Would you try the attached patch and see if it gets rid of the warnings? Also check that regression tests and contrib build/regression tests still work. works perfectly on AIX 4.3.3 with xlc. All regression tests pass, except horology that shows 7 extra -infinity rows. In contrib I tested btree_gist and dblink which also pass. There are a few places left in contrib with duplicate warnings, some because of -l pgport and -I:postgres.imp but the main build is now free of those warnings. Thank you for the work, please apply. Andreas ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] ERROR: invalid memory alloc request size a_big_number_here
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Were you able to create a test case? I tried several things, including stopping a backend in the middle of creating a MultiXactId, but no luck yet. I've had some success using Tatsuo's new scriptable pgbench: Hmm. I wasn't able to reproduce it with this on my desktop machine, but maybe it's because it's slow as hell. I plugged my notebook however and I was able to. Additionally, I can confirm that the problem doesn't manifest with your latest patch. I'm running several instances just to be sure. Thanks, -- Alvaro Herrerahttp://www.advogato.org/person/alvherre Acepta los honores y aplausos y perderás tu libertad ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] ERROR: invalid memory alloc request size a_big_number_here
Tom Lane wrote: OK, I think this version may actually work, and get the wraparound case right too. It hasn't failed yet on the pgbench test case anyway. Matteo, could you try it on your test case? Yes, it's working. The test case ran for a several minutes without errors. Thank you all :) Best regards -- Matteo Beccati http://phpadsnew.com http://phppgads.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] ERROR: invalid memory alloc request size a_big_number_here
Tom Lane [EMAIL PROTECTED] writes: creatingOffsetZero will be a bool that gets set before releasing MultiXactGenLock if offset 0 is being returned, and then we clear it after updating the slru data structures if we had starting offset 0. If you're going to have a special flag indicating this couldn't you just have a special flag indicating that the offset isn't ready yet? Loop until that flag is cleared instead of looking for offset != 0 at all. -- greg ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] ERROR: invalid memory alloc request size a_big_number_here
Greg Stark [EMAIL PROTECTED] writes: If you're going to have a special flag indicating this couldn't you just have a special flag indicating that the offset isn't ready yet? Loop until that flag is cleared instead of looking for offset != 0 at all. Well, the whole idea didn't work anyway :-(. But I think your proposal is equivalent to holding the lock throughout CreateMultiXactId, which is exactly what we're trying to avoid doing ... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] TODO Item - Add system view to show free space map
On Fri, Oct 28, 2005 at 05:05:25PM +0100, Simon Riggs wrote: 3. Helping Readahead efficiency: Currently blocks are allocated one at a time. If many tables are extending at the same time, the blocks from multiple tables will be intermixed together on the disk. Reading the data back takes more head movement and reduces the I/O rate. Allocating Ok, I agree with the rest but this isn't true. Any filesystem designed in the last ten years leaves gaps around the place so when you extend a file it remains consecutive. Some filesystems (like XFS) take it to extremes). Interleaving blocks with this pattern hasn't been done since FAT. That isn't to say that preextending isn't a good idea. With my pread() patch it was the one use of lseek() I couldn't remove. Other than that, good thought... Have a nice dat, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgp7phXzSrHQS.pgp Description: PGP signature
Re: [HACKERS] ERROR: invalid memory alloc request size a_big_number_here
Alvaro Herrera wrote: Additionally, I can confirm that the problem doesn't manifest with your latest patch. I'm running several instances just to be sure. Ok, I tested several runs and the problem didn't manifest. Additionally I tested that wraparound also worked on at least some cases, by doing pg_resetxlog -O 4294967200 $PGDATA dd if=/dev/zero of=$PGDATA/pg_multixact/members/ bs=8192 count=32 and retrying the test. I did this several times, with no problems detected. -- Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J Hay quien adquiere la mala costumbre de ser infeliz (M. A. Evans) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] TODO Item - Add system view to show free space map
Simon Riggs [EMAIL PROTECTED] writes: There are a few issues with current FSM implementation, IMHO, discussing as usual the very highest end of performance: Do you have any evidence that the FSM is actually a source of performance issues, or is this all hypothetical? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] TRAP: FailedAssertion(!((itemid)-lp_flags 0x01),
On Fri, Oct 28, 2005 at 02:26:31PM +1000, Gavin Sherry wrote: Have spoken with Jim on IRC, he says that there have been several crashes recently due to a faulty disk array. I guess the zeroing could be an outcome of the faulty disk. I wonder if the crash the faulty disk resulted in could have been caused some where around mdextend() where we create a zero'd page but before we could have written out the initialised page. Just to clarify, there's no evidence that the array is faulty. I do know that they were using write-back with a non-battery-backed cache though. What has been happening is periodic random crashes, around 1 a week. I now have a good core for one, as well as an assert: TRAP: FailedAssertion(!(shared-page_number[slotno] == pageno shared-page_status[slotno] == SLRU_PAGE_READ_IN_PROGRESS), File: slru.c, Line: 308) I haven't looked at that code yet, so I have no idea what that actually means. Let me know what info y'all would like to see out of the core. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [GENERAL] aix build question re: duplicate symbol warning
Zeugswetter Andreas DAZ SD [EMAIL PROTECTED] writes: Thank you for the work, please apply. Done. Thanks for testing it. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] TRAP: FailedAssertion(!((itemid)-lp_flags 0x01),
Jim C. Nasby [EMAIL PROTECTED] writes: What has been happening is periodic random crashes, around 1 a week. I now have a good core for one, as well as an assert: TRAP: FailedAssertion(!(shared-page_number[slotno] == pageno shared-page_status[slotno] == SLRU_PAGE_READ_IN_PROGRESS), File: slru.c, Line: 308) I haven't looked at that code yet, so I have no idea what that actually means. Let me know what info y'all would like to see out of the core. The whole contents of *shared and the local variables of SimpleLruReadPage would be good for starters. Also, what PG version is this exactly, again? regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] TRAP: FailedAssertion(!((itemid)-lp_flags 0x01),
From: Tom Lane [mailto:[EMAIL PROTECTED] Jim C. Nasby [EMAIL PROTECTED] writes: What has been happening is periodic random crashes, around 1 a week. I now have a good core for one, as well as an assert: TRAP: FailedAssertion(!(shared-page_number[slotno] == pageno shared-page_status[slotno] == SLRU_PAGE_READ_IN_PROGRESS), File: slru.c, Line: 308) I haven't looked at that code yet, so I have no idea what that actually means. Let me know what info y'all would like to see out of the core. The whole contents of *shared and the local variables of SimpleLruReadPage would be good for starters. I know how to get to the SimpleLruReadPage frame, but what commands do I need to use after that? Also, what PG version is this exactly, again? 8.0.3. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] TRAP: FailedAssertion(!((itemid)-lp_flags 0x01),
Jim Nasby [EMAIL PROTECTED] writes: From: Tom Lane [mailto:[EMAIL PROTECTED] The whole contents of *shared and the local variables of SimpleLruReadPage would be good for starters. I know how to get to the SimpleLruReadPage frame, but what commands do I need to use after that? p *shared info locals regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] TRAP: FailedAssertion(!((itemid)-lp_flags 0x01),
Here's the full info from 2 different cores:
[EMAIL PROTECTED] coredumps]# cat slru.gdb
f 3
p *shared
p pageno
p slotno
p ok
p xid
quit
[EMAIL PROTECTED] coredumps]# gdb -x slru.gdb /usr/bin/postmaster core.25146
|tail -n 13
warning: svr4_current_sos: Can't read pathname for load map: Input/output error
#3 0x0047365f in SimpleLruReadPage (ctl=0x7d9f40, pageno=162932,
xid=0) at slru.c:307
307 Assert(shared-page_number[slotno] == pageno
$1 = {ControlLock = SubtransControlLock, page_buffer = {0x2a98298380 ,
0x2a9829a380 ,
0x2a9829c380 , 0x2a9829e380 , 0x2a982a0380 , 0x2a982a2380 ,
0x2a982a4380 ,
0x2a982a6380 }, page_status = {SLRU_PAGE_CLEAN,
SLRU_PAGE_READ_IN_PROGRESS,
SLRU_PAGE_CLEAN, SLRU_PAGE_CLEAN, SLRU_PAGE_DIRTY,
SLRU_PAGE_READ_IN_PROGRESS,
SLRU_PAGE_READ_IN_PROGRESS, SLRU_PAGE_CLEAN}, page_number = {162878,
162877, 163050,
162883, 163270, 162761, 162980, 162797}, page_lru_count = {8, 2, 5, 1, 139,
4, 0, 3},
buffer_locks = {24, 25, 26, 27, 28, 29, 30, 31}, latest_page_number = 163270}
$2 = 162932
$3 = 1
$4 = 1 '\001'
$5 = 0
[EMAIL PROTECTED] coredumps]# gdb -x slru.gdb /usr/bin/postmaster core.32555
|tail -n 13
warning: svr4_current_sos: Can't read pathname for load map: Input/output error
#3 0x0047365f in SimpleLruReadPage (ctl=0x7d9f40, pageno=164152,
xid=0) at slru.c:307
307 Assert(shared-page_number[slotno] == pageno
$1 = {ControlLock = SubtransControlLock, page_buffer = {0x2a98298380 ,
0x2a9829a380 ,
0x2a9829c380 , 0x2a9829e380 , 0x2a982a0380 , 0x2a982a2380 ,
0x2a982a4380 ,
0x2a982a6380 }, page_status = {SLRU_PAGE_READ_IN_PROGRESS,
SLRU_PAGE_CLEAN,
SLRU_PAGE_CLEAN, SLRU_PAGE_DIRTY, SLRU_PAGE_CLEAN, SLRU_PAGE_CLEAN,
SLRU_PAGE_CLEAN,
SLRU_PAGE_CLEAN}, page_number = {164145, 164146, 164147, 164153, 164148,
164150, 164151,
164149}, page_lru_count = {0, 1, 2, 106, 5, 7, 8, 6}, buffer_locks = {24,
25, 26, 27, 28,
29, 30, 31}, latest_page_number = 164153}
$2 = 164152
$3 = 0
$4 = 1 '\001'
$5 = 0
[EMAIL PROTECTED] coredumps]#
Also, here's the trace from a 3rd core:
[EMAIL PROTECTED] coredumps]# gdb /usr/bin/postgres core.13897
GNU gdb Red Hat Linux (6.3.0.0-1.63rh)
Copyright 2004 Free Software Foundation, Inc.
GDB is free software, covered by the GNU General Public License, and you are
welcome to change it and/or distribute copies of it under certain conditions.
Type show copying to see the conditions.
There is absolutely no warranty for GDB. Type show warranty for details.
This GDB was configured as x86_64-redhat-linux-gnu...Using host libthread_db
library /lib64/tls/libthread_db.so.1.
warning: core file may not match specified executable file.
Core was generated by `gdb -q -fullname /usr/bin/postmaster core.25146'.
Program terminated with signal 11, Segmentation fault.
#0 0x003b894688e3 in ?? ()
(gdb) bt
#0 0x003b894688e3 in ?? ()
#1 0x004f4f20 in ExecReScanHashJoin ()
#2 0x004b593c in DoCopy (stmt=Variable stmt is not available.
) at copy.c:767
#3 0x00447190 in _hash_log2 () at hashutil.c:107
#4 0x in ?? ()
(gdb)
-rw--- 1 root root 29179904 Oct 28 10:08 core.13897
-rw--- 1 root root 1166159872 Oct 28 07:13 core.25146
-rw--- 1 root root 1167413248 Oct 28 09:05 core.32555
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
---(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] enums
On Thu, Oct 27, 2005 at 11:07:19PM -0400, Rod Taylor wrote: The other issue is ease of use. We used lookup tables in bugzilla when it was converted to work with Postgres. But many users will find having to do that annoying, to say the least. I think there's a very good case for providing true enums. Then why did you use lookup tables instead of a varchar and a constraint? Probably performance. A much more general purpose but just as good solution would be the ability to create a hidden surrogate key for a structure. CREATE TABLE status (code varchar(20) PRIMARY KEY) WITH SURROGATE; CREATE TABLE account (name varchar(60), status varchar(20) references status); Behind the scenes (transparent to the user) this gets converted to: CREATE TABLE status (id SERIAL UNIQUE, code varchar(20) PRIMARY KEY) WITH SURROGATE; CREATE TABLE account (name varchar(60), status integer references status(id)); SELECT * FROM account; would be rewritten as SELECT * FROM (SELECT name, code FROM account JOIN status USING (id)) AS account; Enum might be good for a short list of items but something like the above should be good for any common value that we manually create surrogate keys for today but without the clutter or the application needing to know. If PostgreSQL had an updatable view implementation it would be pretty simple to implement. I'm not quiet following the WITH SURROGATE bit, but what you've described certainly looks valuable. Note that I would still want to be able to get at the raw numeric values in some fasion. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(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] enums
On Thu, Oct 27, 2005 at 10:34:57PM -0400, Andrew Dunstan wrote: Jim C. Nasby wrote: On Thu, Oct 27, 2005 at 09:45:05PM -0400, Andrew Dunstan wrote: Jim C. Nasby wrote: Andrew, you mentioned that if you want to change the ordering you should just create a new type. What about if you need to change the values that are in the enum? MySQL does (or at least did, it's been some time since I've messed with this) a horrible job at that. There's no way to rename anything; you have to add the new names you want, then do a bulk update, then delete the (now old) names. IMO this is broken. It would just be a standard ALTER TABLE foo ALTER COLUMN bar TYPE newtype USING expression operation. You would write a function that took a value of the old type and returned a value of the new type and use a cll to that function in the expression. Since these would be named types, unlike the case in mysql where they are anonymously defined inline, this would present no difficulties at all. But why force a re-write of the entire table just to change the name of something? Because you are not just changing the name of something. No, I was refering specifically to the case of wanting to rename something. IE: you setup an enum for sky colors (blue, black), and then the PHB issues an edict that the daytime sky is now green. In this case you (or at least I) don't want to define a new enum, I just want to change 'blue' to 'green' in that enum. There's no reason it needs to hit the table at all. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] pl/pgsql breakage in 8.1b4?
On Fri, Oct 28, 2005 at 12:10:00AM -0400, Tom Lane wrote: Philip Yarra [EMAIL PROTECTED] writes: Without really wishing to volunteer myself: should plpgsql allow using parameters with the same name as the columns being referred to within the function, provided they're qualified as function_name.parameter? No, because that just changes where the ambiguity is. The function name could easily conflict with a table name. It's a mighty weird-looking convention anyway --- on what grounds would you argue that the function is a structure having parameter names as fields? Is there some other means we could come up with to distinguish between field names and variables? Maybe local.variablename? Oracle has similar issues where you have to use functionname.variablename if there's a conflict, which is a pita. Hence the standard advice of always prefixing your variables with something, but that seems like an ugly hack to me. Of course the real issue is the namespace conflict to begin with, but I have no idea how to solve that.. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] enums
On Fri, 2005-10-28 at 13:20 -0500, Jim C. Nasby wrote: On Thu, Oct 27, 2005 at 11:07:19PM -0400, Rod Taylor wrote: The other issue is ease of use. We used lookup tables in bugzilla when it was converted to work with Postgres. But many users will find having to do that annoying, to say the least. I think there's a very good case for providing true enums. Then why did you use lookup tables instead of a varchar and a constraint? Probably performance. A much more general purpose but just as good solution would be the ability to create a hidden surrogate key for a structure. CREATE TABLE status (code varchar(20) PRIMARY KEY) WITH SURROGATE; CREATE TABLE account (name varchar(60), status varchar(20) references status); Behind the scenes (transparent to the user) this gets converted to: CREATE TABLE status (id SERIAL UNIQUE, code varchar(20) PRIMARY KEY) WITH SURROGATE; CREATE TABLE account (name varchar(60), status integer references status(id)); SELECT * FROM account; would be rewritten as SELECT * FROM (SELECT name, code FROM account JOIN status USING (id)) AS account; Enum might be good for a short list of items but something like the above should be good for any common value that we manually create surrogate keys for today but without the clutter or the application needing to know. If PostgreSQL had an updatable view implementation it would be pretty simple to implement. I'm not quiet following the WITH SURROGATE bit, but what you've described certainly looks valuable. Note that I would still want to be able to get at the raw numeric values in some fasion. The basic idea is that most of us break out schemas by creating fake primary keys for the purpose of obtaining performance because using the proper primary key (single or multiple columns) is often very slow. The automatic and transparent creation of a surrogate key by PostgreSQL would allow us to dramatically clean up the presentation of our schema to the users using the database without the performance hit we currently get. It puts surrogate keys (fake primary keys) back to the level of table spaces, indexes and other performance enhancements where they belong. -- ---(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] TRAP: FailedAssertion(!((itemid)-lp_flags 0x01),
BTW, what's the stack trace in those two core files? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] enums
Jim C. Nasby wrote: But why force a re-write of the entire table just to change the name of something? Because you are not just changing the name of something. No, I was refering specifically to the case of wanting to rename something. IE: you setup an enum for sky colors (blue, black), and then the PHB issues an edict that the daytime sky is now green. In this case you (or at least I) don't want to define a new enum, I just want to change 'blue' to 'green' in that enum. There's no reason it needs to hit the table at all. Well, with enumkit you can't, because the values are hardwired in the .so file. With a builtin facility you would be able to, because the values would live in the catalog. However, hacking the catalog is not something I would encourage - what you are suggesting basically breaks the abstraction. But sure, it would be possible. I would not provide an SQL level facility to do it, though. My approved way to do it would be like the example I gave earlier. cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] TRAP: FailedAssertion(!((itemid)-lp_flags 0x01),
On Fri, Oct 28, 2005 at 03:04:02PM -0400, Tom Lane wrote: BTW, what's the stack trace in those two core files? From 25146: #0 0x003b8942e37d in raise () from /lib64/tls/libc.so.6 #1 0x003b8942faae in abort () from /lib64/tls/libc.so.6 #2 0x005d36f8 in ExceptionalCondition ( conditionName=0x623a Address 0x623a out of bounds, errorType=0x623a Address 0x623a out of bounds, fileName=0x623a Address 0x623a out of bounds, lineNumber=-1) at assert.c:51 #3 0x0047365f in SimpleLruReadPage (ctl=0x7d9f40, pageno=162932, xid=0) at slru.c:307 #4 0x00473863 in SlruSelectLRUPage (ctl=0x7d9f40, pageno=163131) at slru.c:753 #5 0x00473439 in SimpleLruReadPage (ctl=0x7d9f40, pageno=163131, xid=334094300) at slru.c:254 #6 0x00473eeb in SubTransGetParent (xid=334094300) at subtrans.c:116 #7 0x00473f61 in SubTransGetTopmostTransaction (xid=Variable xid is not available. ) at subtrans.c:153 #8 0x005efa38 in HeapTupleSatisfiesSnapshot (tuple=0x2ac2fb04b0, snapshot=0x88ab98, buffer=86685) at tqual.c:967 #9 0x00447d7a in heapgettup (relation=0x2add22b960, dir=1, tuple=0x8c4a90, buffer=0x8c4ab0, snapshot=0x88ab98, nkeys=0, key=0x0, pages=597) at heapam.c:305 #10 0x00448b53 in heap_getnext (scan=0x8c4a68, direction=Variable direction is not available. ) at heapam.c:832 #11 0x004f7f86 in SeqNext (node=Variable node is not available. ) at nodeSeqscan.c:102 #12 0x004eec2e in ExecScan (node=0x8c3b68, accessMtd=0x4f7f20 SeqNext) at execScan.c:98 #13 0x004e9c9d in ExecProcNode (node=0x8c3b68) at execProcnode.c:303 #14 0x004f2a75 in ExecAgg (node=0x8c3610) at nodeAgg.c:783 #15 0x004e9bea in ExecProcNode (node=0x8c3610) at execProcnode.c:353 #16 0x004e8ccd in ExecutorRun (queryDesc=Variable queryDesc is not available. ) at execMain.c:1060 #17 0x0056968e in PortalRunSelect (portal=0x8acb38, forward=Variable forward is not available. ) at pquery.c:746 #18 0x00569caf in PortalRun (portal=0x8acb38, count=9223372036854775807, dest=0x8bbae0, altdest=0x8bbae0, completionTag=0x7fbfffdfd0 ) at pquery.c:561 #19 0x00565f12 in exec_simple_query ( query_string=0x89e0b8 SELECT count(*) as cnt FROM queue where machineindex = '32') at postgres.c:933 #20 0x00567b33 in PostgresMain (argc=4, argv=0x846368, username=0x846328 iacm) at postgres.c:3007 #21 0x0053ac70 in ServerLoop () at postmaster.c:2836 #22 0x0053c374 in PostmasterMain (argc=5, argv=0x843500) at postmaster.c:918 #23 0x00507fef in main (argc=5, argv=0x843500) at main.c:268 And 32555: #0 0x003b8942e37d in raise () from /lib64/tls/libc.so.6 (gdb) bt #0 0x003b8942e37d in raise () from /lib64/tls/libc.so.6 #1 0x003b8942faae in abort () from /lib64/tls/libc.so.6 #2 0x005d36f8 in ExceptionalCondition ( conditionName=0x7f2b Address 0x7f2b out of bounds, errorType=0x7f2b Address 0x7f2b out of bounds, fileName=0x7f2b Address 0x7f2b out of bounds, lineNumber=-1) at assert.c:51 #3 0x0047365f in SimpleLruReadPage (ctl=0x7d9f40, pageno=164152, xid=0) at slru.c:307 #4 0x00473863 in SlruSelectLRUPage (ctl=0x7d9f40, pageno=164037) at slru.c:753 #5 0x00473439 in SimpleLruReadPage (ctl=0x7d9f40, pageno=164037, xid=335949336) at slru.c:254 #6 0x00473eeb in SubTransGetParent (xid=335949336) at subtrans.c:116 #7 0x00473f61 in SubTransGetTopmostTransaction (xid=Variable xid is not available. ) at subtrans.c:153 #8 0x005ef963 in HeapTupleSatisfiesSnapshot (tuple=0x2abc81e0b8, snapshot=0x8788d8, buffer=73427) at tqual.c:905 #9 0x00448dc6 in heap_release_fetch (relation=0x2add227130, snapshot=0x8788d8, tuple=0x8d2460, userbuf=0x8d2480, keep_buf=1 '\001', pgstat_info=0x8d24b8) at heapam.c:979 #10 0x00450c8f in index_getnext (scan=0x8d2418, direction=ForwardScanDirection) at indexam.c:528 #11 0x004f5012 in IndexNext (node=0x8d18c0) at nodeIndexscan.c:316 #12 0x004eec2e in ExecScan (node=0x8d18c0, accessMtd=0x4f4f20 IndexNext) at execScan.c:98 #13 0x004e9c8d in ExecProcNode (node=0x8d18c0) at execProcnode.c:307 #14 0x004e8ccd in ExecutorRun (queryDesc=Variable queryDesc is not available. ) at execMain.c:1060 #15 0x0056968e in PortalRunSelect (portal=0x8add58, forward=Variable forward is not available. ) at pquery.c:746 #16 0x00569caf in PortalRun (portal=0x8add58, count=9223372036854775807, dest=0x8e5c48, altdest=0x8e5c48, completionTag=0x7fbfffdfd0 ) at pquery.c:561 #17 0x00565f12 in exec_simple_query ( query_string=0x89f2d8 select index from daily_reports where accountindex = '3034' and date = '113004') at postgres.c:933 #18 0x00567b33 in PostgresMain (argc=4, argv=0x846368, username=0x846328 iacm) at postgres.c:3007 #19 0x0053ac70 in ServerLoop
Re: [HACKERS] enums
On Fri, Oct 28, 2005 at 02:57:03PM -0400, Rod Taylor wrote: The basic idea is that most of us break out schemas by creating fake primary keys for the purpose of obtaining performance because using the proper primary key (single or multiple columns) is often very slow. The automatic and transparent creation of a surrogate key by PostgreSQL would allow us to dramatically clean up the presentation of our schema to the users using the database without the performance hit we currently get. It puts surrogate keys (fake primary keys) back to the level of table spaces, indexes and other performance enhancements where they belong. Ahh. Yes, that would definately be great to have. Although it would probably take me months if not years to get used to not seeing a bunch of _id fields laying all over the place... Is SURROGATE part of any of the ANSI specs? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] enums
On Fri, Oct 28, 2005 at 04:12:01PM -0400, Andrew Dunstan wrote: Well, with enumkit you can't, because the values are hardwired in the .so file. With a builtin facility you would be able to, because the values would live in the catalog. However, hacking the catalog is not something I would encourage - what you are suggesting basically breaks the abstraction. But sure, it would be possible. I would not provide an SQL level facility to do it, though. My approved way to do it would be like the example I gave earlier. Why not allow renaming though? It seems like a logical feature to have, and an easy one to add. What am I missing? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] TRAP: FailedAssertion(!((itemid)-lp_flags 0x01),
Here's another core... (pid 805 for reference)
#0 0x003b8942e37d in raise () from /lib64/tls/libc.so.6
#0 0x003b8942e37d in raise () from /lib64/tls/libc.so.6
#1 0x003b8942faae in abort () from /lib64/tls/libc.so.6
#2 0x005d36f8 in ExceptionalCondition (
conditionName=0x325 Address 0x325 out of bounds,
errorType=0x325 Address 0x325 out of bounds,
fileName=0x325 Address 0x325 out of bounds, lineNumber=-1) at assert.c:51
#3 0x0047365f in SimpleLruReadPage (ctl=0x7d9f40, pageno=169039,
xid=0) at slru.c:307
#4 0x00473863 in SlruSelectLRUPage (ctl=0x7d9f40, pageno=169162) at
slru.c:753
#5 0x00473439 in SimpleLruReadPage (ctl=0x7d9f40, pageno=169162,
xid=346445732)
at slru.c:254
#6 0x00473eeb in SubTransGetParent (xid=346445732) at subtrans.c:116
#7 0x00473f61 in SubTransGetTopmostTransaction (xid=Variable xid is
not available.
) at subtrans.c:153
#8 0x005efa38 in HeapTupleSatisfiesSnapshot (tuple=0x2ac4b87dd0,
snapshot=0x877908,
buffer=90248) at tqual.c:967
#9 0x00447d7a in heapgettup (relation=0x2add20fd98, dir=1,
tuple=0x8e7210,
buffer=0x8e7230, snapshot=0x877908, nkeys=0, key=0x0, pages=435) at
heapam.c:305
#10 0x00448b53 in heap_getnext (scan=0x8e71e8, direction=Variable
direction is not available.
) at heapam.c:832
#11 0x004f7f86 in SeqNext (node=Variable node is not available.
) at nodeSeqscan.c:102
#12 0x004eec2e in ExecScan (node=0x8b7c38, accessMtd=0x4f7f20 SeqNext)
at execScan.c:98
#13 0x004e9c9d in ExecProcNode (node=0x8b7c38) at execProcnode.c:303
#14 0x004f7431 in ExecNestLoop (node=0x8b64b0) at nodeNestloop.c:135
#15 0x004e9c4d in ExecProcNode (node=0x8b64b0) at execProcnode.c:326
#16 0x004f89f9 in ExecSort (node=0x8b6398) at nodeSort.c:102
#17 0x004e9c0a in ExecProcNode (node=0x8b6398) at execProcnode.c:345
#18 0x004f9048 in ExecLimit (node=0x8b6150) at nodeLimit.c:87
#19 0x004e9bb4 in ExecProcNode (node=0x8b6150) at execProcnode.c:369
#20 0x004e8ccd in ExecutorRun (queryDesc=Variable queryDesc is not
available.
) at execMain.c:1060
#21 0x0056968e in PortalRunSelect (portal=0x8ad5a8, forward=Variable
forward is not available.
) at pquery.c:746
#22 0x00569caf in PortalRun (portal=0x8ad5a8, count=9223372036854775807,
dest=0x8e1870, altdest=0x8e1870, completionTag=0x7fbfffdfd0 ) at
pquery.c:561
#23 0x00565f12 in exec_simple_query (
query_string=0x89f168 ' ' repeats 71 times, SELECT
a.index,a.jobtype,a.machineindex,a.pid,a.data,a.status,a.starttime,a.ranby,a.clientindex,a.parentindex,a.output_data,a.per...)
at postgres.c:933
#24 0x00567b33 in PostgresMain (argc=4, argv=0x846368,
username=0x846328 iacm)
at postgres.c:3007
#25 0x0053ac70 in ServerLoop () at postmaster.c:2836
#26 0x0053c374 in PostmasterMain (argc=5, argv=0x843500) at
postmaster.c:918
#27 0x00507fef in main (argc=5, argv=0x843500) at main.c:268
#3 0x0047365f in SimpleLruReadPage (ctl=0x7d9f40, pageno=169039,
xid=0) at slru.c:307
307 Assert(shared-page_number[slotno] == pageno
$1 = {ControlLock = SubtransControlLock, page_buffer = {0x2a98298380 ,
0x2a9829a380 ,
0x2a9829c380 , 0x2a9829e380 , 0x2a982a0380 , 0x2a982a2380 ,
0x2a982a4380 ,
0x2a982a6380 }, page_status = {SLRU_PAGE_DIRTY, SLRU_PAGE_CLEAN,
SLRU_PAGE_READ_IN_PROGRESS, SLRU_PAGE_CLEAN, SLRU_PAGE_CLEAN,
SLRU_PAGE_READ_IN_PROGRESS,
SLRU_PAGE_CLEAN, SLRU_PAGE_CLEAN}, page_number = {169452, 169351, 169163,
169238, 169236,
169328, 169233, 169239}, page_lru_count = {17108, 4, 1, 3, 5, 0, 6, 2},
buffer_locks = {
24, 25, 26, 27, 28, 29, 30, 31}, latest_page_number = 169452}
$2 = 169039
$3 = 2
$4 = 1 '\001'
$5 = 0
-
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-946
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] enums
Jim C. Nasby wrote: On Fri, Oct 28, 2005 at 04:12:01PM -0400, Andrew Dunstan wrote: Well, with enumkit you can't, because the values are hardwired in the .so file. With a builtin facility you would be able to, because the values would live in the catalog. However, hacking the catalog is not something I would encourage - what you are suggesting basically breaks the abstraction. But sure, it would be possible. I would not provide an SQL level facility to do it, though. My approved way to do it would be like the example I gave earlier. Why not allow renaming though? It seems like a logical feature to have, and an easy one to add. What am I missing? That it is not changing a name, but a value. It's roughly the equivalent of inserting a new digit between 3 and 4. Your feature breaks the abstraction I am trying to implement. cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] enums
On Fri, 2005-10-28 at 15:21 -0500, Jim C. Nasby wrote: On Fri, Oct 28, 2005 at 02:57:03PM -0400, Rod Taylor wrote: The basic idea is that most of us break out schemas by creating fake primary keys for the purpose of obtaining performance because using the proper primary key (single or multiple columns) is often very slow. The automatic and transparent creation of a surrogate key by PostgreSQL would allow us to dramatically clean up the presentation of our schema to the users using the database without the performance hit we currently get. It puts surrogate keys (fake primary keys) back to the level of table spaces, indexes and other performance enhancements where they belong. Ahh. Yes, that would definately be great to have. Although it would probably take me months if not years to get used to not seeing a bunch of _id fields laying all over the place... Is SURROGATE part of any of the ANSI specs? No, but neither is an index, rollback segment, or table space. The ANSI spec doesn't usually deal with performance tweaks that are the responsibility of the DBA. -- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] TRAP: FailedAssertion(!((itemid)-lp_flags 0x01),
Jim C. Nasby wrote:
Here's another core... (pid 805 for reference)
All of them have in common that the slotno being passed ($3 below) is in
SLRU_PAGE_READ_IN_PROGRESS state ... could it be a problem with lock
reordering? Maybe somebody is trying to read in a page, and somebody
else steals the buffer from under them. Not sure how likely is that.
BTW what's the relationship with the other assertion failure (the one in
the subject)?
#3 0x0047365f in SimpleLruReadPage (ctl=0x7d9f40, pageno=169039,
xid=0) at slru.c:307
307 Assert(shared-page_number[slotno] == pageno
$1 = {ControlLock = SubtransControlLock, page_buffer = {0x2a98298380 ,
0x2a9829a380 ,
0x2a9829c380 , 0x2a9829e380 , 0x2a982a0380 , 0x2a982a2380 ,
0x2a982a4380 ,
0x2a982a6380 }, page_status = {SLRU_PAGE_DIRTY, SLRU_PAGE_CLEAN,
SLRU_PAGE_READ_IN_PROGRESS, SLRU_PAGE_CLEAN, SLRU_PAGE_CLEAN,
SLRU_PAGE_READ_IN_PROGRESS,
SLRU_PAGE_CLEAN, SLRU_PAGE_CLEAN}, page_number = {169452, 169351, 169163,
169238, 169236,
169328, 169233, 169239}, page_lru_count = {17108, 4, 1, 3, 5, 0, 6, 2},
buffer_locks = {
24, 25, 26, 27, 28, 29, 30, 31}, latest_page_number = 169452}
$2 = 169039
$3 = 2
$4 = 1 '\001'
$5 = 0
--
Alvaro Herrera Valdivia, Chile ICBM: S 39º 49' 17.7, W 73º 14' 26.8
Nadie esta tan esclavizado como el que se cree libre no siendolo (Goethe)
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] TRAP: FailedAssertion(!((itemid)-lp_flags 0x01),
Alvaro Herrera [EMAIL PROTECTED] writes: All of them have in common that the slotno being passed ($3 below) is in SLRU_PAGE_READ_IN_PROGRESS state ... could it be a problem with lock reordering? Maybe somebody is trying to read in a page, and somebody else steals the buffer from under them. Not sure how likely is that. It's even more interesting than that: in all three cases, SlruSelectLRUPage has selected a least recently used page that is still in READ_IN_PROGRESS state (ie, we haven't finished faulting it in) and is recursively calling SimpleLruReadPage to wait for that condition to terminate. Apparently, Jim's setup could desperately do with a larger SLRU arena for pg_subtrans, because this is supposed to be a never-happen path --- if you can't finish loading a page before you need its slot for something else, you are thrashing with a capital T. I suppose there's a bug in this path, but I'm darned if I can see what it is. There are a number of obvious inefficiencies, but those shouldn't be important given that this isn't supposed to happen much. But how's it getting to the Assert failure? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] TRAP: FailedAssertion(!((itemid)-lp_flags 0x01),
On Fri, Oct 28, 2005 at 04:58:56PM -0400, Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: All of them have in common that the slotno being passed ($3 below) is in SLRU_PAGE_READ_IN_PROGRESS state ... could it be a problem with lock reordering? Maybe somebody is trying to read in a page, and somebody else steals the buffer from under them. Not sure how likely is that. It's even more interesting than that: in all three cases, SlruSelectLRUPage has selected a least recently used page that is still in READ_IN_PROGRESS state (ie, we haven't finished faulting it in) and is recursively calling SimpleLruReadPage to wait for that condition to terminate. Apparently, Jim's setup could desperately do with a larger SLRU arena for pg_subtrans, because this is supposed to be a never-happen path --- if you can't finish loading a page before you need its slot for something else, you are thrashing with a capital T. I suppose there's a bug in this path, but I'm darned if I can see what it is. There are a number of obvious inefficiencies, but those shouldn't be important given that this isn't supposed to happen much. But how's it getting to the Assert failure? If it helps, this is a ~250G database that's (now) on an 8-way (opteron I think) machine with 32G. shared_buffers is set to 1G. My client also has a 4-way machine with 16G, although it seemed to be having some issues with producing cores that were useful. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(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] enums
On Fri, Oct 28, 2005 at 04:36:26PM -0400, Rod Taylor wrote: On Fri, 2005-10-28 at 15:21 -0500, Jim C. Nasby wrote: On Fri, Oct 28, 2005 at 02:57:03PM -0400, Rod Taylor wrote: The basic idea is that most of us break out schemas by creating fake primary keys for the purpose of obtaining performance because using the proper primary key (single or multiple columns) is often very slow. The automatic and transparent creation of a surrogate key by PostgreSQL would allow us to dramatically clean up the presentation of our schema to the users using the database without the performance hit we currently get. It puts surrogate keys (fake primary keys) back to the level of table spaces, indexes and other performance enhancements where they belong. Ahh. Yes, that would definately be great to have. Although it would probably take me months if not years to get used to not seeing a bunch of _id fields laying all over the place... Is SURROGATE part of any of the ANSI specs? No, but neither is an index, rollback segment, or table space. The ANSI spec doesn't usually deal with performance tweaks that are the responsibility of the DBA. True, but none of those other things you mention affect external representation of data. But I was more wondering if we were inventing syntax on the fly here or not... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] TRAP: FailedAssertion(!((itemid)-lp_flags 0x01),
I wrote: I suppose there's a bug in this path, but I'm darned if I can see what it is. There are a number of obvious inefficiencies, but those shouldn't be important given that this isn't supposed to happen much. But how's it getting to the Assert failure? While I'm disinclined to change anything until we can explain why it's crashing, I suspect that the solution may be to avoid the recursive call of SimpleLruReadPage, as in the attached patch. Jim, are you interested in seeing if this patch makes the problem go away for you? regards, tom lane binjs4mWgIsYY.bin Description: slru.patch ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] TRAP: FailedAssertion(!((itemid)-lp_flags 0x01),
On Fri, Oct 28, 2005 at 05:45:51PM -0400, Tom Lane wrote: I wrote: I suppose there's a bug in this path, but I'm darned if I can see what it is. There are a number of obvious inefficiencies, but those shouldn't be important given that this isn't supposed to happen much. But how's it getting to the Assert failure? While I'm disinclined to change anything until we can explain why it's crashing, I suspect that the solution may be to avoid the recursive call of SimpleLruReadPage, as in the attached patch. Jim, are you interested in seeing if this patch makes the problem go away for you? Well, this is a production system... what's the risk with that patch? BTW, is it typical to see a 10 difference between asserts on and off? My client has a process that was doing 10-20 records/sec with asserts on and 90-110 with asserts off. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] TRAP: FailedAssertion(!((itemid)-lp_flags 0x01),
Jim C. Nasby [EMAIL PROTECTED] writes: On Fri, Oct 28, 2005 at 05:45:51PM -0400, Tom Lane wrote: Jim, are you interested in seeing if this patch makes the problem go away for you? Well, this is a production system... what's the risk with that patch? Well, it's utterly untested, which means it might crash your system, which is where you are now, no? BTW, is it typical to see a 10 difference between asserts on and off? My client has a process that was doing 10-20 records/sec with asserts on and 90-110 with asserts off. Not typical, but I can believe there are some code paths like that. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] enums
On Fri, 2005-10-28 at 16:28 -0500, Jim C. Nasby wrote: On Fri, Oct 28, 2005 at 04:36:26PM -0400, Rod Taylor wrote: On Fri, 2005-10-28 at 15:21 -0500, Jim C. Nasby wrote: On Fri, Oct 28, 2005 at 02:57:03PM -0400, Rod Taylor wrote: The basic idea is that most of us break out schemas by creating fake primary keys for the purpose of obtaining performance because using the proper primary key (single or multiple columns) is often very slow. The automatic and transparent creation of a surrogate key by PostgreSQL would allow us to dramatically clean up the presentation of our schema to the users using the database without the performance hit we currently get. It puts surrogate keys (fake primary keys) back to the level of table spaces, indexes and other performance enhancements where they belong. Ahh. Yes, that would definately be great to have. Although it would probably take me months if not years to get used to not seeing a bunch of _id fields laying all over the place... Is SURROGATE part of any of the ANSI specs? No, but neither is an index, rollback segment, or table space. The ANSI spec doesn't usually deal with performance tweaks that are the responsibility of the DBA. True, but none of those other things you mention affect external representation of data. But I was more wondering if we were inventing syntax on the fly here or not... It isn't supposed to impact the external representation of the data and generally neither is an ENUM outside of the potential sorting ability. I was just getting the impression that the big push for enums was to be able to use a 'real word' but without a performance hit. A regular old table, foreign key to a varchar gives you the 'real word' and the surrogate key allows you to do so without a performance hit. -- ---(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] TRAP: FailedAssertion(!((itemid)-lp_flags 0x01),
From: Tom Lane [mailto:[EMAIL PROTECTED] Jim C. Nasby [EMAIL PROTECTED] writes: On Fri, Oct 28, 2005 at 05:45:51PM -0400, Tom Lane wrote: Jim, are you interested in seeing if this patch makes the problem go away for you? Well, this is a production system... what's the risk with that patch? Well, it's utterly untested, which means it might crash your system, which is where you are now, no? Yes, but the crashes are somewhat sporadic and most importantly they don't appear to involve any data loss/corruption. I just don't want to make matters any worse. In any case, my client's gone home for the weekend, so I doubt anything would happen until Monday. BTW, is it typical to see a 10 difference between asserts on and off? My client has a process that was doing 10-20 records/sec with asserts on and 90-110 with asserts off. Not typical, but I can believe there are some code paths like that. Yeah, they're doing some not-so-good things like row-by-row operations, so that's probably what the issue is. I seem to recall 20% being the penalty that's normally thrown around, so I was just surprised by such a huge difference. ---(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] enums
On Fri, Oct 28, 2005 at 06:10:26PM -0400, Rod Taylor wrote: It isn't supposed to impact the external representation of the data and generally neither is an ENUM outside of the potential sorting ability. I was just getting the impression that the big push for enums was to be able to use a 'real word' but without a performance hit. A regular old table, foreign key to a varchar gives you the 'real word' and the surrogate key allows you to do so without a performance hit. I think there's probably good use cases for each. If you've got something small like a status field, 'enum' might be better. For bigger things, SURROGATE could be nice syntactic sugar. Now that I finally understand what Andrew's been getting at with enums, I'm wondering if we might want to expand on the typical usage a bit. Looking at a plain-old C enum, you're just representing some magic labels with a number to save space. Things like say, SLRU_PAGE_CLEAN, SLRU_PAGE_READ_IN_PROGRESS, SLRU_PAGE_CLEAN. Those names are great from a code standpoint, but they're not something you'd typically want to display to the user. So, imho a useful extension would be to allow for enums to contain both the 'machine name' and a 'human name', where the human name could be renamed freely. To put this in a more concrete example; I hate the default priorities that ship with bugzilla; P1 - P5. Is 1 high or is 5? So I always rename them to Very Low, Low ... Very High. That means making changes both to the database and to the code. But if Bugzilla was using my idea of an enum then the code would refer to priorities with P1...P5 (or whatever else they wanted to call it) and I could easily change the human names to something that can't be confused. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(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
[HACKERS] TODO for plpgsql: RETURN should accept arbitrary composite expressions
Folks, On 8.0.4 and 8.1b4 given: create type return_value as ( id INTEGER, message TEXT ); this function: create function return_test ( vuser INT, vsession INT ) returns return_value as $fnc$ declare vtemp return_value; begin vtemp := row( -1, 'bad' ); return vtemp; end; $fnc$ language plpgsql; works, but this function: create function return_test_2 ( vuser INT, vsession INT ) returns return_value as $fnc$ begin vtemp := row( -1, 'bad' ); end; $fnc$ language plpgsql; gives this error at run time: ERROR: syntax error at or near vtemp at character 1 QUERY: vtemp := row( -1, 'bad' ) CONTEXT: PL/pgSQL function return_test_2 line 2 at SQL statement LINE 1: vtemp := row( -1, 'bad' ) ... the problem seems to be that RETURN will accept variables and constants but not arbitrary composites. We should fix that eventually. Can we put it on the TODO list? -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(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] TODO for plpgsql: RETURN should accept arbitrary composite expressions
Folks, Two corrections: The second example was the wrong code, it should have been this function: create function return_test_2 ( vuser INT, vsession INT ) returns return_value as $fnc$ begin return row( -1, 'bad' ); end; $fnc$ language plpgsql; Also, this issue is documented, but I believe that it still needs fixing, as current behavior is cumbersome and unintuitive: When returning a scalar type, any expression can be used. The expression's result will be automatically cast into the function's return type as described for assignments. To return a composite (row) value, you must write a record or row variable as the expression. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] FKs on temp tables: hard, or just omitted?
Folks, Are foreign keys on temp tables not allowed just because nobody requested them, or because they're hard to do? -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] 8.1 Release Candidate 1 Coming ...
Tomorrow evening, I'm going to wrap up RC1, to announce it on Monday ... if anyone is sitting on *anything*, please say something before about midnight GMT ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] FKs on temp tables: hard, or just omitted?
Josh Berkus josh@agliodbs.com writes: Are foreign keys on temp tables not allowed just because nobody requested them, or because they're hard to do? You can have foreign keys between temp tables, just not between temp and permanent tables. The latter case is either fairly silly, or technically hard, depending on which direction you have in mind. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
