Re: [HACKERS] [GSoC] (Is it OK to choose items without % mark in theToDoList) (is it an acceptable idea to build index on Flash Disk)
On Tue, 25 Mar 2008, mx wrote: The atom unit of flash is page(512~2048byte typically). Page are organized into blocks, typically of 32 or 64 pages. All read write and write operations happen at page granularity, but erase operations happen at block granularity. You made a subtle switch here I wanted to emphasise. Your original message suggested flash is an increasingly important storage mechanism because flash devices like SSD drives are going to be more popular in the future; that is true. However, what you're describing is something more like how flash is used in raw embedded systems applications. The kinds of SSD drives that are becoming popular for database use abstract away all of this low-level block mess and hide it with approaches like sophisticated write-leveling algorithms. Maybe I gives too many detailed about raw flash devices. In fact, what I want to show is the asymmetric speed of read and write. Any flash devices including SSD have such a characteristic. For a sumsung 64G SSD PATA IDE 2.5,maximum Squential Read is 57MB/s, while maximum sequential Read is 38MB/s according the product datasheet. Anyway, in the eyes performance of outside， write is more expensive than read. Some strategy of trade read for write may be considered. So, the asymmetric speed of read and write make it is still valuable to do some work on SSD. You don't (and possibly can't) even know what the underlying structure is like. And even if you did, the fact that there's a always a regular operating system and filesystem underneath PostgreSQL writes will make it undertain the writes are only touching the tiny portion of flash you want to target anyway. They may write a whole OS block regardless. Yeah, you're right. This is the most confused thing. I wish my thesis work is independent of low level flash device. But it's very hard in fact, just as what you said. -- Have a good day;-) Best Regards, Xiao Meng ━ Data and Knowledge Engineering Research Center,CST Harbin Institute of Technology, Harbin, China Gtalk: [EMAIL PROTECTED] MSN: [EMAIL PROTECTED] Blog: http://xiaomeng.yo2.cn -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GSoC] (Is it OK to choose items without % mark in theToDoList) (is it an acceptable idea to build index on Flash Disk)
Thank you for all of your advices. I think you're right. I should be more realistic. There are so many work to do if I want to do some work on Flash disk. It's too difficult to complete the task only in a summer. Obviously, It's not an appropriate project idea for GSoC anyway. Maybe I'll do it in the future after I've done enough work according to my theis work. So, I finally decide to focus on the project idea of improving hash index now. It's more valuable , and also challenging. Any suggestion about the project idea of improving hash index? -- Have a good day;-) Best Regards, Xiao Meng ━ Data and Knowledge Engineering Research Center,CST Harbin Institute of Technology, Harbin, Heilongjiang, China Gtalk: [EMAIL PROTECTED] MSN: [EMAIL PROTECTED] Blog: http://xiaomeng.yo2.cn -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Integer datetimes
Tom Lane napsal(a): Zdenek Kotala [EMAIL PROTECTED] writes: Tom Lane napsal(a): This is not happening, at least not without 100 times more work than anyone has shown willingness to put into the issue. I understand your arguments, but it is important for in-place upgrade. No, it is not, you merely need to be sure the new version is configured the same as the old ... which is a requirement anyway. It depends. For example, currently postgreSQL allows only collation setting per database, but somebody will develop collation per database or column. After that the situation will be different. Upgrade have to transfer collation from control file to catalog and so on. Configuration will be different but there will be possible transition. Probably all packager will delivery 8.4 and newer with integer datetime, but people who will use in-place upgrade they will not use official package. I think it is not good idea. Zdenek -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GSoC] (Is it OK to choose items without % mark in theToDoList) (is it an acceptable idea to build index on Flash Disk)
So, I finally decide to focus on the project idea of improving hash index now. It's more valuable , and also challenging. Any suggestion about the project idea of improving hash index? Imho one thing to look into is the storage. I do not see any real value in storing the key itself (especially longer keys) in the hash buckets. Instead store the hash value only (or not even that) and mark the index lossy (recheck the key in the heap). Andreas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TODO Item: Consider allowing control of upper/lower case folding of unquoted, identifiers
Tom Lane wrote: Russell Smith [EMAIL PROTECTED] writes: As an attempt at a first PostgreSQL patch, I'd like to see if I can do anything about this issue. Trying that as a first patch is a recipe for failure... the short answer is that no one can think of a solution that will be generally acceptable. regards, tom lane What makes this change particularly challenging? The fact that nobody has agreed on how it should work, or the actual coding? regards Russell -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Minor bug in src/port/rint.c
Tom Lane wrote: The proper wording of this item is * Find a correct rint() substitute on Windows Fixed. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TODO Item: Consider allowing control of upper/lower case folding of unquoted, identifiers
Russell Smith wrote: Tom Lane wrote: Russell Smith [EMAIL PROTECTED] writes: As an attempt at a first PostgreSQL patch, I'd like to see if I can do anything about this issue. Trying that as a first patch is a recipe for failure... the short answer is that no one can think of a solution that will be generally acceptable. regards, tom lane What makes this change particularly challenging? The fact that nobody has agreed on how it should work, or the actual coding? The widespread code impact of any change is a strong indicator against doing this as a first patch. But newbie or not, as Tom says, nobody should be starting to design, let alone cut code, until the actual desired behaviour is agreed. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GSoC] (Is it OK to choose items without % mark in theToDoList) (is it an acceptable idea to build index on Flash Disk)
On Tue, Mar 25, 2008 at 01:46:51PM +0100, Zeugswetter Andreas OSB SD wrote: So, I finally decide to focus on the project idea of improving hash index now. It's more valuable , and also challenging. Any suggestion about the project idea of improving hash index? Imho one thing to look into is the storage. I do not see any real value in storing the key itself (especially longer keys) in the hash buckets. Instead store the hash value only (or not even that) and mark the index lossy (recheck the key in the heap). Andreas Meng, I had started a thread on the hackers mailing list about improving the hash index in PostgreSQL. You can look through it for some of the ideas that were suggested. The first one is to replace the storage of the key values in the index with the hash of the key values instead. This can leverage the lossy index heap re-check code that is already in the database. Neil Conway had posted a patch doing this with an old version of PostgreSQL. My coding skills are a bit rusty and my job has kept me from making much progress towards this. Anyway, please take a look at the hash index thread in hackers. It starts with: http://archives.postgresql.org/pgsql-hackers/2007-09/msg00051.php Let me know what you think? Cheers, Ken Marshall -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] writing a MIN(RECORD) aggregate
On Mar 20, 2008, at 2:23 PM, Sam Mason wrote: I'm trying to write a version of the MIN aggregate for values of RECORD type. I'm somewhat stuck on getting type information about the argument out, I can determine how many attributes it's got but I can't seem to do any better than that. Does anyone have any good pointers into the code for places to help me understand what's happening? The reason for doing this is mainly because I think it'd be nicer to be doing things like: SELECT i, (MIN((j,k))).k FROM tbl GROUP BY i; How is that any better than SELECT i, min(k) FROM tbl GROUP BY i ? instead of: SELECT DISTINCT ON (i) i, k FROM tbl ORDER BY i,j,k; Which as far as I can tell should produce identical results, except the first has cleaner semantics. It also allows you to combine MIN and MAX in the same query, giving the value of k for the smallest and largest j in this example--requiring two queries if it was done using the DISTINCT ON method. I don't see how min(record) even allows for that. I'm not saying that min/avg/max/etc(RECORD) wouldn't be useful; I'm just failing to see the use in these examples. -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 smime.p7s Description: S/MIME cryptographic signature
[HACKERS] FETCH FIRST
SQL200n specifies a new command similar to our LIMIT
FETCH { FIRST | NEXT } n { ROW | ROWS } ONLY
and also that the OFFSET command can have an optional suffix
{ ROW | ROWS }
Seems like a % project for the TODO list
--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com
PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] TRUNCATE TABLE with IDENTITY
SQL200n specifies a new qualifier on a TRUNCATE command TRUNCATE TABLE foo [ CONTINUE IDENTITY | RESTART IDENTITY ] CONTINUE IDENTITY is the default and does nothing, like now. RESTART IDENTITY will reset the SERIAL sequences back to the original start value. Seems like a % project for the TODO list -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] FETCH FIRST
Simon Riggs wrote:
SQL200n specifies a new command similar to our LIMIT
FETCH { FIRST | NEXT } n { ROW | ROWS } ONLY
and also that the OFFSET command can have an optional suffix
{ ROW | ROWS }
Seems like a % project for the TODO list
Where does this FETCH appear in a SELECT?
--
Bruce Momjian [EMAIL PROTECTED]http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] postgresql in FreeBSD jails: proposal
Bruce Momjian wrote: Added to TODO: * Improve detection of shared memory segments being used by other FreeBSD jails http://archives.postgresql.org/pgsql-hackers/2008-01/msg00656.php There's a bit more than that to it -- see http://archives.postgresql.org/pgsql-hackers/2008-01/msg00673.php In short, it's not just a FreeBSD issue, but something a bit more general. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [pgsql-www] New email list for emergency communications
On Mon, Mar 24, 2008 at 06:39:25PM -0400, Andrew Dunstan wrote: perusing a mailbox again. We have unfortunately been badly underprepared for this. Surely that there is an emerging consensus to that effect means that it's not as unfortunate as it might be? I seem to recall the original announcement suggesting this was an experiment. I wouldn't expect the first couple rounds to go without a hitch; as long as there is procedural improvement the next time, that's a good thing, right? A -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] FETCH FIRST
On Tue, 2008-03-25 at 10:45 -0400, Bruce Momjian wrote:
Simon Riggs wrote:
SQL200n specifies a new command similar to our LIMIT
FETCH { FIRST | NEXT } n { ROW | ROWS } ONLY
and also that the OFFSET command can have an optional suffix
{ ROW | ROWS }
Seems like a % project for the TODO list
Where does this FETCH appear in a SELECT?
It's supposed to go *after* the OFFSET clause in a query, but making it
a simple synonym for LIMIT seems most sensible.
It's also allowed as part of the WITH clause (that we don't yet have).
--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com
PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Images in PostgreSQL
Hi all, Does anybody know if is it possible to store images in Postgre? If it is possible which data type should I use? Thanks in advance, -- RD | Alea Management Technology Solutions -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Images in PostgreSQL
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tue, 25 Mar 2008 12:06:09 -0300 (BRT) r[EMAIL PROTECTED] wrote: Hi all, Does anybody know if is it possible to store images in Postgre? If it is possible which data type should I use? There are two ways to do it: http://www.postgresql.org/docs/8.3/static/datatype-binary.html http://www.postgresql.org/docs/8.3/static/lo-funcs.html Sincerely, Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFH6RmiATb/zqfZUUQRAhsOAJ9dDzb47p2n2hnIInZhhgXdT66TagCfYi2t emUwHcomGeBzHMx0miHieD0= =6bdP -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] HP-UX 11.11 PostgreSQL 8.3.1 - psql core dumps - Unresolved symbol: tgetent
I've compiled PostgreSQL 8.3.1 using gcc 4.2.3 on HP-UX 11i v1, and all went well. PostgreSQL works and everything, except for psql client: $ ldd /usr/local/pgsql/bin/psql = /usr/lib/libc.2 = /usr/lib/libc.2 /usr/lib/libdld.2 =/usr/lib/libdld.2 /usr/lib/libc.2 = /usr/lib/libc.2 /usr/lib/libm.2 = /usr/lib/libm.2 /usr/local/lib/libreadline.sl = /usr/local/lib/libreadline.sl /usr/local/lib/libz.sl = /usr/local/lib/libz.sl /usr/lib/libxnet.2 = /usr/lib/libxnet.2 /usr/lib/libxti.2 =/usr/lib/libxti.2 ../../../src/interfaces/libpq/libpq.sl.5 = /usr/local/pgsql/lib/libpq.sl.5 $ /usr/local/pgsql/bin/psql Welcome to psql 8.3.1, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit /usr/lib/dld.sl: Unresolved symbol: tgetent (code) from /usr/local/lib/libreadline.sl LOG: unexpected EOF on client connection ABORT instruction (core dumped) # swlist | grep readline readline 5.2readline $ file /usr/local/lib/libreadline.sl /usr/local/lib/libreadline.sl: PA-RISC2.0 shared library As you can see, the older version of psql (7.4.2) works fine: $ psql Welcome to psql 7.4.2, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit $ psql -l List of databases Name| Owner | Encoding ---+--+--- postgres | postgres | SQL_ASCII template0 | postgres | SQL_ASCII template1 | postgres | SQL_ASCII (3 rows) $ /usr/local/pgsql/bin/createdb test $ psql -l List of databases Name| Owner | Encoding ---+--+--- postgres | postgres | SQL_ASCII template0 | postgres | SQL_ASCII template1 | postgres | SQL_ASCII test | postgres | SQL_ASCII (4 rows) If needed, I can attach a core dump. Regards, cmihai -- Criveti Mihai http://unixsadm.blogspot.com
Re: [HACKERS] Images in PostgreSQL
A Dimarts 25 Març 2008, r[EMAIL PROTECTED] va escriure: Hi all, Does anybody know if is it possible to store images in Postgre? If it is possible which data type should I use? Yes it's possible, you can use BYTEA for this, for example. But please, this kind of questions should go to users mailing list. hackers is for people working on developing PostgreSQL itself. Thanks in advance, -- RD | Alea Management Technology Solutions -- Albert Cervera i Areny http://www.NaN-tic.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TODO Item: Consider allowing control of upper/lower case folding of unquoted, identifiers
Russell Smith [EMAIL PROTECTED] writes: What makes this change particularly challenging? The fact that nobody has agreed on how it should work, or the actual coding? Getting the right design is definitely challenging. The coding might or might not be, depending on your design ... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] FETCH FIRST
Simon Riggs [EMAIL PROTECTED] writes: On Tue, 2008-03-25 at 10:45 -0400, Bruce Momjian wrote: Where does this FETCH appear in a SELECT? It's supposed to go *after* the OFFSET clause in a query, but making it a simple synonym for LIMIT seems most sensible. Huh? OFFSET isn't in standard SQL either. Are you sure you're not confusing FETCH-from-a-cursor with SELECT? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TRUNCATE TABLE with IDENTITY
Simon Riggs [EMAIL PROTECTED] writes: SQL200n specifies a new qualifier on a TRUNCATE command TRUNCATE TABLE foo [ CONTINUE IDENTITY | RESTART IDENTITY ] CONTINUE IDENTITY is the default and does nothing, like now. RESTART IDENTITY will reset the SERIAL sequences back to the original start value. Seems like a % project for the TODO list Seems like copying syntax from a *draft* standard is a bit premature, especially when the amount of functionality added is nil. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [pgsql-www] New email list for emergency communications
Andrew Sullivan [EMAIL PROTECTED] writes: On Mon, Mar 24, 2008 at 06:39:25PM -0400, Andrew Dunstan wrote: perusing a mailbox again. We have unfortunately been badly underprepared for this. Surely that there is an emerging consensus to that effect means that it's not as unfortunate as it might be? I seem to recall the original announcement suggesting this was an experiment. I wouldn't expect the first couple rounds to go without a hitch; as long as there is procedural improvement the next time, that's a good thing, right? Yeah, we expected to have glitches. I think we now have a much better idea what sort of status-tracking support we need for future fests. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] HP-UX 11.11 PostgreSQL 8.3.1 - psql core dumps - Unresolved symbol: tgetent
On Tue, Mar 25, 2008 at 04:54:59PM +0200, Mihai Criveti wrote: I've compiled PostgreSQL 8.3.1 using gcc 4.2.3 on HP-UX 11i v1, and all went well. PostgreSQL works and everything, except for psql client: $ /usr/local/pgsql/bin/psql Welcome to psql 8.3.1, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit /usr/lib/dld.sl: Unresolved symbol: tgetent (code) from /usr/local/lib/libreadline.sl tgetent is from curses/ncurses. When readline is built it either takes a system version or a builtin one. Evidently it thinks there ought to be a system version somewhere. In any case it looks like your readline library uses curses but doesn't actually link to it, which is bad. You can probably work around it by adding -lcurses to the psql link line, but fixing readline would be better. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [HACKERS] HP-UX 11.11 PostgreSQL 8.3.1 - psql core dumps - Unresolved symbol: tgetent
Mihai Criveti [EMAIL PROTECTED] writes: I've compiled PostgreSQL 8.3.1 using gcc 4.2.3 on HP-UX 11i v1, and all went well. PostgreSQL works and everything, except for psql client: /usr/lib/dld.sl: Unresolved symbol: tgetent (code) from /usr/local/lib/libreadline.sl Kinda looks like your libreadline is broken. As you can see, the older version of psql (7.4.2) works fine: Maybe that was built without readline support? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] FETCH FIRST
On Tue, 2008-03-25 at 11:47 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: On Tue, 2008-03-25 at 10:45 -0400, Bruce Momjian wrote: Where does this FETCH appear in a SELECT? It's supposed to go *after* the OFFSET clause in a query, but making it a simple synonym for LIMIT seems most sensible. Huh? OFFSET isn't in standard SQL either. Tis now. Are you sure you're not confusing FETCH-from-a-cursor with SELECT? Quite sure. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TRUNCATE TABLE with IDENTITY
On Tue, 2008-03-25 at 11:48 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: SQL200n specifies a new qualifier on a TRUNCATE command TRUNCATE TABLE foo [ CONTINUE IDENTITY | RESTART IDENTITY ] CONTINUE IDENTITY is the default and does nothing, like now. RESTART IDENTITY will reset the SERIAL sequences back to the original start value. Seems like a % project for the TODO list Seems like copying syntax from a *draft* standard is a bit premature, especially when the amount of functionality added is nil. It's at the final yes-or-no vote stage. Seems unlikely to be no to me, and it would be good to be seen to be proactive on standards support. The added functionality in this case isn't nil. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TRUNCATE TABLE with IDENTITY
Simon Riggs wrote: RESTART IDENTITY will reset the SERIAL sequences back to the original start value. Assuming this feature were to be added In cases where the same sequence has been used across multiple tables, what will be the appropriate response when a user attempts to TRUNCATE one of those tables with RESTART IDENTITY? Cheers, Steve -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] GiST opclass and varlena
Hi, I'm trying to code a GiST opclass to index prefix searches (select ... from t where t.prefix @ query), now using a prefix_range datatype. This datatype is a varlena one, and storing it to disk and indexing it with BTrees work ok, but I'm failing to have my GiST opclass working, here's the problem: postgres=# create index idx_prefix on ranges using gist(prefix gist_prefix_range_ops); NOTICE: gpr_picksplit(): entryvec-n= 234 maxoff= 232 l= 176 r= 56 l+r= 232 unionL='01[0-7]' unionR='01[4-7]' NOTICE: gpr_picksplit(): v-spl_ldatum='01[0-7]' v-spl_rdatum='01[4-7]' ERROR: invalid memory alloc request size 3049878020 The code is available at pgfoundry here: http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/prefix/prefix/ The previous support for prefixes as text is still there (but will get deprecated soon --- or so I hope), and the new datatype and its usage not well commented nor documented currenlty. If this show up as a requirement to get your attention, please state it and I'll work on documenting prefix_range first. I'm looking for some help on how to resolve the shown index creation problem, which I think is related to how I give data to GiST in its spl_ldatum and spl_rdatum from the user defined picksplit() method, lines 1101 and 1102 in prefix.c (version 1.26). Regards, -- dim signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] TRUNCATE TABLE with IDENTITY
On Tue, 2008-03-25 at 09:08 -0700, Steve Crawford wrote: Simon Riggs wrote: RESTART IDENTITY will reset the SERIAL sequences back to the original start value. Assuming this feature were to be added In cases where the same sequence has been used across multiple tables, what will be the appropriate response when a user attempts to TRUNCATE one of those tables with RESTART IDENTITY? Well, I'm suggesting it as a TODO item, based on the standard. It would be for whoever took this up to unravel that. Since that's a weak answer, I'd say it should only reset sequences that have been placed there automatically through the use of SERIAL or BIGSERIAL datatypes. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] partial dump of patch queue to wiki
Hi, I created a page for our current commitfest: http://wiki.postgresql.org/wiki/CommitFest:March Not all patches are there yet. I only added the latest version of each patch. I'll continue after lunch. I also created one for the next commitfest: http://wiki.postgresql.org/wiki/CommitFest:May HTH -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] writing a MIN(RECORD) aggregate
On Mon, Mar 24, 2008 at 05:27:04PM -0500, Decibel! wrote: On Mar 20, 2008, at 2:23 PM, Sam Mason wrote: SELECT i, (MIN((j,k))).k FROM tbl GROUP BY i; How is that any better than SELECT i, min(k) FROM tbl GROUP BY i ? Because I want the value of k associated with the minimum value of j. For example, if I have data looking like: i j k 1 3 7 1 4 8 2 5 10 2 6 9 I want to get this out: i k 1 7 2 10 I would get this if I used the DISTINCT ON or if MIN was valid over records. With your code I'd get this: i k 1 7 2 9 I'm not saying that min/avg/max/etc(RECORD) wouldn't be useful; AVG wouldn't work, because it relies on treating it's parameter as a numeric field over which summation and division are valid operations. MIN/MAX just relies on there being a (total) ordering operator available and with PG there pretty much always is. I'm just failing to see the use in these examples. Did the example above make things any clearer? I've also just realised that PG's current handling of NULLs inside records is also going to cause problems. The main problem seems to be that the IS NULL operator isn't consistent with comparison operators. For example: (1,NULL) IS NULL -- FALSE (1,NULL) = (1,NULL) -- NULL I'm not sure if it's just my intuition is off, or whether there is an invariant (e.g. a comparison returns NULL if-and-only-if either side evaluate TRUE to IS NULL) that's being broken. Thanks, Sam -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Text - C string
I've been working some more on Brendan Jurd's patch to simplify text -
C string conversions. It seems we have consensus on the names for the
base operations:
extern text *cstring_to_text(const char *s);
extern char *text_to_cstring(const text *t);
Brendan's patch also included cstring_text_limit(const char *s, int len)
which was defined as copying Min(len, strlen(s)) bytes. I didn't find
this to be particularly useful. In the first place, all potential
callers are passing the exact desired length, so the strlen() call is
just a waste of cycles. In the second place, at least some callers pass
text that is not embedded in a known-to-be-null-terminated string (it
could be a section of a text datum instead); which means there is a
nonzero chance of the strlen running off the end of memory and dumping
core. So I propose instead
extern text *cstring_to_text_with_len(const char *s, int len);
which just takes the given length as gospel. Brendan had also proposed
text_to_cstring_limit(const text *t, int len) with similar Min()
semantics, but what this was doing was replacing copies into
limited-size local buffers with a palloc. If we did that we might
as well just use text_to_cstring. What I think is more useful is
a strlcpy()-like function that copies into a caller-supplied buffer
of limited size. For lack of a better idea I propose defining it
*exactly* like strlcpy:
extern size_t textlcpy(char *dst, const text *src, size_t siz);
I've also found that there are lots and lots of places where the
text end of the conversion needs to be a Datum not a text *,
so it seems worthwhile to introduce a couple of macros to minimize
notation in that case:
#define CStringGetTextDatum(s) PointerGetDatum(cstring_to_text(s))
#define TextDatumGetCString(d) text_to_cstring((text *) DatumGetPointer(d))
Lastly, the originally submitted text-to-something functions would
work correctly on plain and 1-byte-header datums, but not on
compressed or toasted-out-of-line datums. There are a whole lot of
places where that's not good enough. Rather than expecting the caller
to use the right detoasting macro everywhere, it seems best to make
these functions cope with any variant. That also avoids memory
leakage by allowing the intermediate copy to be pfree'd. (I had
suggested that the pfree might be pointless, but I reconsidered ---
if the text object is large enough to be compressed or toasted,
we're talking about at least several K, so it's worth not leaking.)
In short, the infrastructure I'm currently testing is the above
definitions with the attached implementation. Last call for
objections ...
regards, tom lane
/*
* cstring_to_text
*
* Create a text value from a null-terminated C string.
*
* The new text value is freshly palloc'd with a full-size VARHDR.
*/
text *
cstring_to_text(const char *s)
{
return cstring_to_text_with_len(s, strlen(s));
}
/*
* cstring_to_text_with_len
*
* Same as cstring_to_text except the caller specifies the string length;
* the string need not be null_terminated.
*/
text *
cstring_to_text_with_len(const char *s, int len)
{
text *result = (text *) palloc(len + VARHDRSZ);
SET_VARSIZE(result, len + VARHDRSZ);
memcpy(VARDATA(result), s, len);
return result;
}
/*
* text_to_cstring
*
* Create a palloc'd, null-terminated C string from a text value.
*
* We support being passed a compressed or toasted text value.
* This is a bit bogus since such values shouldn't really be referred to as
* text *, but it seems useful for robustness. If we didn't handle that
* case here, we'd need another routine that did, anyway.
*/
char *
text_to_cstring(const text *t)
{
char *result;
text *tunpacked = pg_detoast_datum_packed((struct varlena *) t);
int len = VARSIZE_ANY_EXHDR(tunpacked);
result = (char *) palloc(len + 1);
memcpy(result, VARDATA_ANY(tunpacked), len);
result[len] = '\0';
if (tunpacked != t)
pfree(tunpacked);
return result;
}
/*
* textlcpy --- exactly like strlcpy(), except source is a text value.
*
* Copy src to string dst of size siz. At most siz-1 characters
* will be copied. Always NUL terminates (unless siz == 0).
* Returns strlen(src); if retval = siz, truncation occurred.
*
* We support being passed a compressed or toasted text value.
* This is a bit bogus since such values shouldn't really be referred to as
* text *, but it seems useful for robustness. If we didn't handle that
* case here, we'd need another routine that did, anyway.
*/
size_t
textlcpy(char *dst, const text *src, size_t siz)
{
text *srcunpacked = pg_detoast_datum_packed((struct varlena *)
src);
size_t srclen = VARSIZE_ANY_EXHDR(srcunpacked);
if (siz 0)
{
siz--;
if (siz = srclen)
siz =
Re: [HACKERS] TRUNCATE TABLE with IDENTITY
Simon Riggs írta: On Tue, 2008-03-25 at 09:08 -0700, Steve Crawford wrote: Simon Riggs wrote: RESTART IDENTITY will reset the SERIAL sequences back to the original start value. Assuming this feature were to be added In cases where the same sequence has been used across multiple tables, what will be the appropriate response when a user attempts to TRUNCATE one of those tables with RESTART IDENTITY? Well, I'm suggesting it as a TODO item, based on the standard. It would be for whoever took this up to unravel that. Since that's a weak answer, I'd say it should only reset sequences that have been placed there automatically through the use of SERIAL or BIGSERIAL datatypes. All of them? PostgreSQL allow multiple SERIALs to be present, the standard allows only one IDENTITY column in a table. And what about this case below? CREATE TABLE t1 (id1 serial, ...); ALTER SEQUENCE seq_t1_id1 RESTART WITH 5432 CYCLE; or the equivalent CREATE SEQUENCE seq_t1_id1 START WITH 5432 CYCLE; CREATE TABLE t1 (id1 serial, ...); ALTER SEQUENCE seq_t1_id1 OWNED BY t1.id1; PostgreSQL doesn't keep the START WITH information. But it should to perform a restart on the sequence, using the minval in this case wouldn't be correct. -- -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH http://www.postgresql.at/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TRUNCATE TABLE with IDENTITY
Zoltan Boszormenyi wrote: All of them? PostgreSQL allow multiple SERIALs to be present, the standard allows only one IDENTITY column in a table. And what about this case below? CREATE TABLE t1 (id1 serial, ...); ALTER SEQUENCE seq_t1_id1 RESTART WITH 5432 CYCLE; or the equivalent CREATE SEQUENCE seq_t1_id1 START WITH 5432 CYCLE; CREATE TABLE t1 (id1 serial, ...); ALTER SEQUENCE seq_t1_id1 OWNED BY t1.id1; PostgreSQL doesn't keep the START WITH information. But it should to perform a restart on the sequence, using the minval in this case wouldn't be correct. I do think we need to wait for the standard to be accepted before adding them to the TODO list as standard-compliant additions, especially because no one is asking for the syntax yet. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TRUNCATE TABLE with IDENTITY
Simon Riggs [EMAIL PROTECTED] writes: SQL200n specifies a new qualifier on a TRUNCATE command TRUNCATE TABLE foo [ CONTINUE IDENTITY | RESTART IDENTITY ] CONTINUE IDENTITY is the default and does nothing, like now. RESTART IDENTITY will reset the SERIAL sequences back to the original start value. Seems like a % project for the TODO list I think we need SQL standard IDENTITY columns before we can consider adding SQL standard CONTINUE IDENTITY or RESTART IDENTITY clauses. The reason the last attempt to add them petered out was precisely because they *don't* exactly line up with the semantics of sequences so I don't imagine attempting to shoehorn sequences into these clauses is likely to pan out. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] writing a MIN(RECORD) aggregate
Sam Mason [EMAIL PROTECTED] writes: On Mon, Mar 24, 2008 at 05:27:04PM -0500, Decibel! wrote: On Mar 20, 2008, at 2:23 PM, Sam Mason wrote: SELECT i, (MIN((j,k))).k FROM tbl GROUP BY i; How is that any better than SELECT i, min(k) FROM tbl GROUP BY i ? Because I want the value of k associated with the minimum value of j. For example, if I have data looking like: I have nothing against having min(record) and it does seem like it would let you do this at least for reasonably simple cases. But I'm more eager to see full OLAP window functions which would let you do this and a whole lot else as well. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TRUNCATE TABLE with IDENTITY
Zoltan Boszormenyi írta: Simon Riggs írta: On Tue, 2008-03-25 at 09:08 -0700, Steve Crawford wrote: Simon Riggs wrote: RESTART IDENTITY will reset the SERIAL sequences back to the original start value. Assuming this feature were to be added In cases where the same sequence has been used across multiple tables, what will be the appropriate response when a user attempts to TRUNCATE one of those tables with RESTART IDENTITY? Well, I'm suggesting it as a TODO item, based on the standard. It would be for whoever took this up to unravel that. Since that's a weak answer, I'd say it should only reset sequences that have been placed there automatically through the use of SERIAL or BIGSERIAL datatypes. All of them? PostgreSQL allow multiple SERIALs to be present, the standard allows only one IDENTITY column in a table. And what about this case below? CREATE TABLE t1 (id1 serial, ...); ALTER SEQUENCE seq_t1_id1 RESTART WITH 5432 CYCLE; or the equivalent CREATE SEQUENCE seq_t1_id1 START WITH 5432 CYCLE; CREATE TABLE t1 (id1 serial, ...); of course CREATE TABLE t1 (id1 integer, ...); ALTER SEQUENCE seq_t1_id1 OWNED BY t1.id1; PostgreSQL doesn't keep the START WITH information. But it should to perform a restart on the sequence, using the minval in this case wouldn't be correct. -- -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH http://www.postgresql.at/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [pgsql-www] New email list for emergency communications
Tom Lane wrote: Andrew Sullivan [EMAIL PROTECTED] writes: On Mon, Mar 24, 2008 at 06:39:25PM -0400, Andrew Dunstan wrote: perusing a mailbox again. We have unfortunately been badly underprepared for this. Surely that there is an emerging consensus to that effect means that it's not as unfortunate as it might be? I seem to recall the original announcement suggesting this was an experiment. I wouldn't expect the first couple rounds to go without a hitch; as long as there is procedural improvement the next time, that's a good thing, right? Yeah, we expected to have glitches. I think we now have a much better idea what sort of status-tracking support we need for future fests. Yes. I'm not meaning to whine, sorry if it comes over like that. It looks to me like we need a sort of prep phase for a commit-fest, so the people switching into commit-fest mode when it starts can do so with little friction. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] partial dump of patch queue to wiki
Alvaro Herrera [EMAIL PROTECTED] writes: Hi, I created a page for our current commitfest: http://wiki.postgresql.org/wiki/CommitFest:March Not all patches are there yet. I only added the latest version of each patch. I'll continue after lunch. I also created one for the next commitfest: http://wiki.postgresql.org/wiki/CommitFest:May Hm, at the same time as you were doing this I wrote a perl script to dump Bruce's mail box into a table. The results are at: http://wiki.postgresql.org/wiki/CommitFest:Bruce I think the next step is to manually go through them and remove the comments, replacing them with a single status cell. (And sending mail to the author and -hackers with the meat of the review). (Note that the author is just the author of the first message Bruce saved -- in some cases that's not the right person. And the reviewer is just the author of the last comment.) -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [pgsql-www] New email list for emergency communications
Andrew Dunstan wrote: Surely that there is an emerging consensus to that effect means that it's not as unfortunate as it might be? I seem to recall the original announcement suggesting this was an experiment. I wouldn't expect the first couple rounds to go without a hitch; as long as there is procedural improvement the next time, that's a good thing, right? Yeah, we expected to have glitches. I think we now have a much better idea what sort of status-tracking support we need for future fests. Yes. I'm not meaning to whine, sorry if it comes over like that. It looks to me like we need a sort of prep phase for a commit-fest, so the people switching into commit-fest mode when it starts can do so with little friction. Yea, I think we are still learning, and polishing our tools. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] PG East and Washington DC tour
Is anyone on hackers interested in touring Washington on Monday after PG East? Magnus and I are planning to go. Please email me privately for details. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GiST opclass and varlena
Hello
Not so familiar with all this math :) ,but here's my solution,which I
must admit keep things going at the moment:
db= \d prefix
Table public.prefix
Column | Type | Modifiers
--+-+-
id | bigint | not null default nextval('prefix_id_seq'::regclass)
prefix | text|
operator | integer |
Indexes:
prefix_pkey PRIMARY KEY, btree (id)
prefix_index UNIQUE, btree (prefix)
and we're using a procedure to match prefices (longest prefix
match),with simething like:
...
-- CHECK PREFIX START
while tmp_length = char_length(d_number) loop
-- take the number and try to find it in prefix table
-- if not found,decrease it by removing last number
-- and try again
tmp_dest_number := substring (d_number from 1 for tmp_length);
select into operator_temp operator from prefix
where prefix=tmp_dest_number;
if not found then
tmp_length := tmp_length + 1;
else
-- if we have a match with some prefix
-- take the operator from that row
operatorfound := true;
operator_out := operator_temp;
exit;
end if;
end loop;
-- CHECK PREFIX STOP
only 'semantic' problem You might have with this approach is that number
is like 16511x,which belongs to some Hawaii island operator :),but
the problem is that all You have in Your prefix table is 16xx,which
You mark to belong to operator USA Something. In that case,Your system
will think of 16511xx number as it belongs to USA Something operator
and not Hawaii island operator :( Only solution to this is to always
have up-to-date prefix table,and populate it even with the prefices Your
system does not support,because Your system then reject that number and
it will have a good/precise reason why he did it :)
Some poorly measurement showed some 60-80 matching/sec with this
algorithm of matching prefices and a couple of concurrent database
connections.
Sincerely
Dragan
Dimitri Fontaine wrote:
Hi,
I'm trying to code a GiST opclass to index prefix searches (select ... from t
where t.prefix @ query), now using a prefix_range datatype. This datatype is
a varlena one, and storing it to disk and indexing it with BTrees work ok,
but I'm failing to have my GiST opclass working, here's the problem:
postgres=# create index idx_prefix on ranges using gist(prefix
gist_prefix_range_ops);
NOTICE: gpr_picksplit(): entryvec-n= 234 maxoff= 232 l= 176 r= 56 l+r= 232
unionL='01[0-7]' unionR='01[4-7]'
NOTICE: gpr_picksplit(): v-spl_ldatum='01[0-7]' v-spl_rdatum='01[4-7]'
ERROR: invalid memory alloc request size 3049878020
The code is available at pgfoundry here:
http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/prefix/prefix/
The previous support for prefixes as text is still there (but will get
deprecated soon --- or so I hope), and the new datatype and its usage not
well commented nor documented currenlty. If this show up as a requirement to
get your attention, please state it and I'll work on documenting prefix_range
first.
I'm looking for some help on how to resolve the shown index creation problem,
which I think is related to how I give data to GiST in its spl_ldatum and
spl_rdatum from the user defined picksplit() method, lines 1101 and 1102 in
prefix.c (version 1.26).
Regards,
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] partial dump of patch queue to wiki
Gregory Stark wrote: Hm, at the same time as you were doing this I wrote a perl script to dump Bruce's mail box into a table. The results are at: Heh. I should have guessed. http://wiki.postgresql.org/wiki/CommitFest:Bruce It is a lot harder to trawl though ... I think it's easier to finish my version than get the weed out of yours -- one reason my list is so much shorter is that there was a huge lot of stuff in the patch queue that has no actual patch; and there are multiple versions of certain patches. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Text - C string
On 26/03/2008, Tom Lane [EMAIL PROTECTED] wrote: Brendan's patch also included cstring_text_limit(const char *s, int len) which was defined as copying Min(len, strlen(s)) bytes. I didn't find this to be particularly useful. In the first place, all potential callers are passing the exact desired length, so the strlen() call is just a waste of cycles. In the second place, at least some callers pass text that is not embedded in a known-to-be-null-terminated string (it could be a section of a text datum instead); which means there is a nonzero chance of the strlen running off the end of memory and dumping core. So I propose instead extern text *cstring_to_text_with_len(const char *s, int len); That all makes sense to me. I think the new name is good. It's pretty long, but I'm not seeing a shorter name that accurately describes the function. which just takes the given length as gospel. Brendan had also proposed text_to_cstring_limit(const text *t, int len) with similar Min() semantics, but what this was doing was replacing copies into limited-size local buffers with a palloc. If we did that we might as well just use text_to_cstring. What I think is more useful is a strlcpy()-like function that copies into a caller-supplied buffer of limited size. For lack of a better idea I propose defining it *exactly* like strlcpy: extern size_t textlcpy(char *dst, const text *src, size_t siz); I'm all for providing a function with this behaviour, but is textlcpy() a bit ambiguous? It's not clear from the name whether the function copies text - text, text - cstring or cstring - text. In fact, if I didn't already know better I'd probably assume that the function copied text - text with length, in the same way strlcpy copies string - string. A text_to_cstring_with_len() or text_to_cstring_limit() might be more to the point, and more consistent with the other functions in the family. On the other hand, maybe some difference in naming would help make it obvious to callers that, unlike its siblings, textlcpy() takes the destination string as an argument rather than returning it. text_to_cstring_lcpy()? I've also found that there are lots and lots of places where the text end of the conversion needs to be a Datum not a text *, so it seems worthwhile to introduce a couple of macros to minimize notation in that case: #define CStringGetTextDatum(s) PointerGetDatum(cstring_to_text(s)) #define TextDatumGetCString(d) text_to_cstring((text *) DatumGetPointer(d)) Yes, I recall coming across a number of sites where these macros would come in handy. Lastly, the originally submitted text-to-something functions would work correctly on plain and 1-byte-header datums, but not on compressed or toasted-out-of-line datums. There are a whole lot of places where that's not good enough. Rather than expecting the caller to use the right detoasting macro everywhere, it seems best to make these functions cope with any variant. That also avoids memory leakage by allowing the intermediate copy to be pfree'd. (I had suggested that the pfree might be pointless, but I reconsidered --- if the text object is large enough to be compressed or toasted, we're talking about at least several K, so it's worth not leaking.) Excellent. My patch didn't contemplate dealing with compressed/toasted datums because, quite frankly, I didn't know *how* to deal with them correctly. Much to learn about varlenas, I still have. Cheers, BJ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Text - C string
Brendan Jurd [EMAIL PROTECTED] writes: On 26/03/2008, Tom Lane [EMAIL PROTECTED] wrote: ... What I think is more useful is a strlcpy()-like function that copies into a caller-supplied buffer of limited size. For lack of a better idea I propose defining it *exactly* like strlcpy: extern size_t textlcpy(char *dst, const text *src, size_t siz); I'm all for providing a function with this behaviour, but is textlcpy() a bit ambiguous? Fair enough, I'm not wedded to that name. Search-and-replace is still easy enough at this point ... A text_to_cstring_with_len() or text_to_cstring_limit() might be more to the point, and more consistent with the other functions in the family. Hmm. The thing that's bothering me is that the length is the size of the *destination*, which is not like cstring_to_text_with_len, so using a closely similar name might be confusing. Of those two I'd go with text_to_cstring_limit. Another thought that comes to mind is void text_to_cstring_buffer(const text *src, char *dst, size_t dst_len) Anyone have other ideas? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] gcc 4.3 breaks ContribCheck in 8.2 and older.
On Mon, Mar 24, 2008 at 05:59:33PM -0400, Tom Lane wrote: Kurt Roeckx [EMAIL PROTECTED] writes: I did try the patch. It fails just the same way. Hmph. So we still don't know why 8.2 and 8.3 behave differently ... [ pokes around ... ] Hah, maybe this is it: http://archives.postgresql.org/pgsql-committers/2007-03/msg00292.php This patch atleast solves the problems with 8.2. Kurt -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] writing a MIN(RECORD) aggregate
On Mar 25, 2008, at 4:43PM, Gregory Stark wrote: On Mar 20, 2008, at 2:23 PM, Sam Mason wrote: SELECT i, (MIN((j,k))).k FROM tbl GROUP BY i; I have nothing against having min(record) and it does seem like it would let you do this at least for reasonably simple cases. The main reason for this was that I've needed min(record) a few times before and thought it would be reasonably easy to code. But I'm more eager to see full OLAP window functions which would let you do this and a whole lot else as well. I've never used window functions before so don't think about them when solving my problems. If they were available I'd probably start using them. From the small bit of reading that I've done around them, they seem very imperative in nature. I'm not sure if this is a good or a bad thing. In a database that did support them, how would I write my query with them? My first naive attempt was this: SELECT i, MIN(k) OVER (PARTITION BY j) FROM tbl GROUP BY i; This is obviously wrong, but I don't see how to get to where I need to be. Sam -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Text - C string
Tom Lane wrote: A text_to_cstring_with_len() or text_to_cstring_limit() might be more to the point, and more consistent with the other functions in the family. Hmm. The thing that's bothering me is that the length is the size of the *destination*, which is not like cstring_to_text_with_len, so using a closely similar name might be confusing. Of those two I'd go with text_to_cstring_limit. Another thought that comes to mind is void text_to_cstring_buffer(const text *src, char *dst, size_t dst_len) I think I like buffer. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Text - C string
Tom Lane escribió: Brendan Jurd [EMAIL PROTECTED] writes: A text_to_cstring_with_len() or text_to_cstring_limit() might be more to the point, and more consistent with the other functions in the family. Hmm. The thing that's bothering me is that the length is the size of the *destination*, which is not like cstring_to_text_with_len, so using a closely similar name might be confusing. Of those two I'd go with text_to_cstring_limit. Another thought that comes to mind is void text_to_cstring_buffer(const text *src, char *dst, size_t dst_len) text_to_cstring_buffer seems okay. I did wonder for a bit whether it should be void text_to_cstring_buffer(const text *src, char *buf, size_t buf_len) but then the src/dst pair seems better than src/buf. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] gcc 4.3 breaks ContribCheck in 8.2 and older.
Kurt Roeckx [EMAIL PROTECTED] writes: On Mon, Mar 24, 2008 at 05:59:33PM -0400, Tom Lane wrote: http://archives.postgresql.org/pgsql-committers/2007-03/msg00292.php This patch atleast solves the problems with 8.2. Excellent, I'll go back-patch that and we can see what else there is. You still have panda set up to build with gcc 4.3, correct? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Wiki patch queue
Ok, AFAICT it is complete: http://wiki.postgresql.org/wiki/CommitFest:March It is a reasonably short page, so it's really easy to search for things you might want to work on for this commit fest. I also added the patches submitted on March 2008 to the May commitfest page. Patch submitters: please have a look at the current commitfest page and check for possible nuisances. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] gcc 4.3 breaks ContribCheck in 8.2 and older.
On Tue, Mar 25, 2008 at 02:11:30PM -0400, Tom Lane wrote: Kurt Roeckx [EMAIL PROTECTED] writes: On Mon, Mar 24, 2008 at 05:59:33PM -0400, Tom Lane wrote: http://archives.postgresql.org/pgsql-committers/2007-03/msg00292.php This patch atleast solves the problems with 8.2. Excellent, I'll go back-patch that and we can see what else there is. You still have panda set up to build with gcc 4.3, correct? Yes, I just disabled some cron jobs for those that failed. kurt -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] writing a MIN(RECORD) aggregate
Sam Mason [EMAIL PROTECTED] writes: SELECT i, MIN(k) OVER (PARTITION BY j) FROM tbl GROUP BY i; This is obviously wrong, but I don't see how to get to where I need to be. I'm not entirely sure myself. I think it might involve RANK OVER j though. I suspect it will look more like the DISTINCT ON solution than the min(record) solution. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Text - C string
extern text *cstring_to_text_with_len(const char *s, int len); buffer_to_text ??? Regards Pavel Stehule -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Wiki patch queue
Hello there is some noises about my patches :( I sent EXECUTE USING - it's important (against to SQL injection and faster dynamic SQL), this patch is linger time in queue. variadic function, named params exist only as WIP and I see it for next commit fest. I'll send new version in next months. Regards Pavel Stehule On 25/03/2008, Alvaro Herrera [EMAIL PROTECTED] wrote: Ok, AFAICT it is complete: http://wiki.postgresql.org/wiki/CommitFest:March It is a reasonably short page, so it's really easy to search for things you might want to work on for this commit fest. I also added the patches submitted on March 2008 to the May commitfest page. Patch submitters: please have a look at the current commitfest page and check for possible nuisances. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] writing a MIN(RECORD) aggregate
On Tue, Mar 25, 2008 at 06:58:06PM +, Gregory Stark wrote: Sam Mason [EMAIL PROTECTED] writes: SELECT i, MIN(k) OVER (PARTITION BY j) FROM tbl GROUP BY i; This is obviously wrong, but I don't see how to get to where I need to be. I'm not entirely sure myself. I think it might involve RANK OVER j though. The main thing I wanted to avoid was an explosion of sub-queries that you get with DISTINCT ON style queries. For example, with record style syntax, I can do: SELECT i, (MIN((j,k))).k AS ka, (MIN((mycode(j),k))).k AS kb FROM tbl GROUP BY i; whereas using DISTINCT ON I'd have to do: SELECT a.i, a.k AS ka, b.k as kb FROM ( SELECT DISTINCT ON (i) i, k FROM tbl ORDER BY i, j) a, ( SELECT DISTINCT ON (i) i, k FROM tbl ORDER BY i, mycode(j)) b WHERE a.i = b.i; Which gets unmanageable quickly. Any idea how window functions would cope with this sort of complexity? Or is this what you meant by: I suspect it will look more like the DISTINCT ON solution than the min(record) solution. Thanks, Sam -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Idea for minor tstore optimization
On Sat, 2008-03-22 at 21:24 -0400, Tom Lane wrote: Oh, wait, that's just a -patches entry; it doesn't look like Neil ever committed it. Neil, how come? Sorry, slipped through the cracks -- I've now committed the patch. -Neil -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal for Null Bitmap Optimization(for Trailing NULLs)
Hi, As said, i am attaching the performance test results and the same patch in this thread works with the latest CVS head. Actually, i am seeing a slight performance improvement with the patch, which i think might be either because of noise/ lesser pages. i ran it with the default settings. i have tested only inserts and selects, because that's where the code change has happened. Regarding Tom's comments As far as the changes are concerned, the patch changes the following functions a) heap_fill_tuple b) nocachegetattr c) heap_form_tuple d) index_form_tuple e) nocache_index_getattr f) changed the macros index_getattr, IndexTupleSize, IndexTupleDSize g) Introduced a new macro IndexTupleActualSize The patch introduces the following changes to the storage of tuples 1) If there are only trailing nulls, it doesn't store the null bitmap 2) If there are non-trailing nulls and trailing nulls, it stores the null-bitmap only till the last non-null value. so it decreases the storage requirement of null bitmap. This is expected to have only very few use-cases 3) It doesn't store the null-bitmap for trailing nulls in indexes also The functions mentioned in d), e), f), g) are required for the functionality of index null-bitmap handling. I suppose, we can't handle it with only heap_form_tuple. Please correct me, if i am wrong.. For having the functionality 2), we have to touch the heap_fill_tuple. i have done the trick, by asking it to use the passed number of attributes, instead of taking it from tupdesc. Again please advice me on how to implement this with only heap_form_tuple. Looking forward for comments/suggestions. Thanks, Gokul. Trailing null - results.ods Description: application/vnd.oasis.opendocument.spreadsheet -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] gcc 4.3 breaks ContribCheck in 8.2 and older.
Kurt Roeckx [EMAIL PROTECTED] writes: On Tue, Mar 25, 2008 at 02:11:30PM -0400, Tom Lane wrote: Excellent, I'll go back-patch that and we can see what else there is. You still have panda set up to build with gcc 4.3, correct? Yes, I just disabled some cron jobs for those that failed. Patches committed, please re-enable the back branches so we can see what happens. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] writing a MIN(RECORD) aggregate
Sam Mason [EMAIL PROTECTED] writes: On Tue, Mar 25, 2008 at 06:58:06PM +, Gregory Stark wrote: The main thing I wanted to avoid was an explosion of sub-queries that you get with DISTINCT ON style queries. For example, with record style syntax, I can do: SELECT i, (MIN((j,k))).k AS ka, (MIN((mycode(j),k))).k AS kb FROM tbl GROUP BY i; whereas using DISTINCT ON I'd have to do: ... Which gets unmanageable quickly. Any idea how window functions would cope with this sort of complexity? Or is this what you meant by: I suspect it will look more like the DISTINCT ON solution than the min(record) solution. The flip side is that if you want to get several fields based on min(j) the min(record) approach requires you to write that expression several times (and the database to calculate it several times). I think the window functions might (assuming an ideal implementation) get the best of both worlds. You would be able to do something with multiple partitions so you could ask of a few columns where rank over j = 1 and a few more columns where rank over k = 1. But, uh, I'm not sure. I'll have to sit down with the spec and see if that's true. Furthermore it may be wishful thinking to hope that the implementation will do anything special with the special case where you're only selecting records where rank = 1. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] writing a MIN(RECORD) aggregate
On Tue, Mar 25, 2008 at 07:54:17PM +, Gregory Stark wrote: Sam Mason [EMAIL PROTECTED] writes: SELECT i, (MIN((j,k))).k AS ka, (MIN((mycode(j),k))).k AS kb FROM tbl GROUP BY i; The flip side is that if you want to get several fields based on min(j) the min(record) approach requires you to write that expression several times (and the database to calculate it several times). No. My demos have only used one column because that's the smallest useful demo. SELECT i, r.k, r.l FROM ( SELECT i, MIN((j,k,l)) AS r FROM tbl GROUP BY i) x; The reason for the sub-select is only because SQL doesn't provide any other way to name expressions. Hum, or at least this should work... There doesn't seem to be any nice way of getting fields out of a record! If I really want to do this, it's going to turn into quite an overhaul of record handling in PG. It would also remove the nice syntactic trick that a.b identifies the field b from table a, and s.a.b means that the above is in schema s. I think the window functions might (assuming an ideal implementation) get the best of both worlds. You would be able to do something with multiple partitions so you could ask of a few columns where rank over j = 1 and a few more columns where rank over k = 1. But, uh, I'm not sure. I'll have to sit down with the spec and see if that's true. Furthermore it may be wishful thinking to hope that the implementation will do anything special with the special case where you're only selecting records where rank = 1. I don't really understand what you're saying above. Optimisation is another can of worms that shouldn't be opened until we know how this sort of thing is going to be used. Sam -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GiST opclass and varlena
Le Tuesday 25 March 2008 17:57:11 Dragan Zubac, vous avez écrit : and we're using a procedure to match prefices (longest prefix match),with simething like: while tmp_length = char_length(d_number) loop -- take the number and try to find it in prefix table -- if not found,decrease it by removing last number -- and try again [...] Some poorly measurement showed some 60-80 matching/sec with this algorithm of matching prefices and a couple of concurrent database connections. With the GiST index support we're hoping to get millisecond response time order (that mean something like 1000 matching per sec, best case), and with a query that simple : SELECT operator FROM prefix WHERE prefix @ '16511xx' ORDER BY len(prefix) DESC LIMIT 1; For this you have to create a dedicated index, first version of the code has this version: http://prefix.projects.postgresql.org/README.html CREATE INDEX idx_prefix ON prefix USING GIST(prefix gist_prefix_ops); New version I'm trying to make work looks like this: CREATE TABLE prefix(id serial, prefix prefix_range, operator integer); CREATE INDEX idx_prefix ON prefix USING GIST(prefix gist_prefix_range_ops); Former usage shows performance of 2 to 5 ms answer time on lower setups (meaning sth like 200 reqs/s already), latter one is aiming at 1000 req/s as said before, but does not work at all at the moment... The goal is to be able to use the search from an AFTER INSERT TRIGGER to materialize some calling stats and prices, etc. So it has to be that fast. Please consider trying the code if you're interrested, it's been tested with PostgreSQL versions 8.2 and 8.3, and former version is working fine with text type prefixes, and should offer you some speedups already. Hope this helps clarifying the goals and context, -- dim signature.asc Description: This is a digitally signed message part.
[HACKERS] advancing snapshot's xmin
Hi, I've finished (hopefully) the code to handle a current list of open snapshots in a transaction. I'm now wondering how to put it to good use ;-) I'm not posting it yet -- first I want to get some feedback on the previous patch I posted, http://archives.postgresql.org/pgsql-patches/2008-03/msg00245.php I think the important change here is switching the semantics of MyProc-xmin. Currently, it is the minimum of Xmin and Xid, across all backends, at the moment the current transaction fetches its serializable snapshot. The first important bit is that it is computed only once: when the serializable snapshot is taken. So ISTM the important change is that we will have to update MyProc-xmin more frequently than that. I'm thinking in keeping enough local state so that we can detect at what time the earliest open snapshot is unregistered; when that happens, we can recalculate MyProc-xmin based on the snapshots we have and the Xid/Xmin of remote backends (which could have also been updating their own xmins). There is one hole here: contention on ProcArrayLock. Basically, for simple transactions we will need to update MyProc after every command. It has been reported that ProcArrayLock is the most contended lock for some loads; this would only add to that, and heavily I think. Perhaps we could restructure the locking here somehow to avoid this problem, but it is complex enough already that it may not even be possible. Another idea is to throttle the updating of Xmin so it only happens once in a while, but it's difficult to find a useful criterion and avoid falling into the trap that we just neglected to update it before a large command. Thoughts? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Hash Join Optimization
Hi, I had a chance to go through the Hash join code of Postgresql and had the following thoughts. - Currently postgres takes the heaptuple from the slot and creates and minimal_tuple and copies it into the temp file. I think the creation of minimal_tuple in the middle is a overhead which can be avoided by creating a mem-map and directly creating the minimal_tuple in the mem-map. Since Hash join is used mainly to join huge tables, this might benefit those warehouse customers of postgres. Am i missing something??? Thanks, Gokul.
Re: [HACKERS] gcc 4.3 breaks ContribCheck in 8.2 and older.
Tom Lane wrote: Kurt Roeckx [EMAIL PROTECTED] writes: On Tue, Mar 25, 2008 at 02:11:30PM -0400, Tom Lane wrote: Excellent, I'll go back-patch that and we can see what else there is. You still have panda set up to build with gcc 4.3, correct? Yes, I just disabled some cron jobs for those that failed. Patches committed, please re-enable the back branches so we can see what happens. I have tested this back as far as 8.0, and all seems OK. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] advancing snapshot's xmin
On Tue, 2008-03-25 at 17:26 -0300, Alvaro Herrera wrote: I've finished (hopefully) the code to handle a current list of open snapshots in a transaction. I'm now wondering how to put it to good use ;-) I'm not posting it yet -- first I want to get some feedback on the previous patch I posted, http://archives.postgresql.org/pgsql-patches/2008-03/msg00245.php As I said before, it looks fine. In your words, it just moves code around, so there's not much to complain about. I think the important change here is switching the semantics of MyProc-xmin. Currently, it is the minimum of Xmin and Xid, across all backends, at the moment the current transaction fetches its serializable snapshot. The first important bit is that it is computed only once: when the serializable snapshot is taken. Yes, I see that as necessary. So the refactoring makes sense, since we'll be adding lots of stuff in that area and its good to keep it isolated. So ISTM the important change is that we will have to update MyProc-xmin more frequently than that. I'm thinking in keeping enough local state so that we can detect at what time the earliest open snapshot is unregistered; when that happens, we can recalculate MyProc-xmin based on the snapshots we have and the Xid/Xmin of remote backends (which could have also been updating their own xmins). There is one hole here: contention on ProcArrayLock. Basically, for simple transactions we will need to update MyProc after every command. It has been reported that ProcArrayLock is the most contended lock for some loads; this would only add to that, and heavily I think. Perhaps we could restructure the locking here somehow to avoid this problem, but it is complex enough already that it may not even be possible. I don't see that this would be a contention problem. We are already careful to read the xmin just once during GetSnapshotData(). We advance it while holding only a LW_SHARED lock during a serializable snapshot, so not sure why we wouldn't advance it at other times also without contention issues. Why does anyone else know or care whether we're taking a serializable snapshot or not? The issue is whether we agree that is correct to do so. If we're advancing it in the circumstances you say, then yes I agree it is. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] advancing snapshot's xmin
On Tue, 2008-03-25 at 17:26 -0300, Alvaro Herrera wrote: There is one hole here: contention on ProcArrayLock. Basically, for simple transactions we will need to update MyProc after every command. If we're just updating MyProc-xmin, we only need to acquire ProcArrayLock in shared mode, right? Another idea is to throttle the updating of Xmin so it only happens once in a while, but it's difficult to find a useful criterion and avoid falling into the trap that we just neglected to update it before a large command. Using LWLockConditionalAcquire() might help also. -Neil -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [PATCHES] [HACKERS] Text - C string
Brendan Jurd [EMAIL PROTECTED] writes: As discussed on -hackers, I'm trying to get rid of some redundant code by creating a widely useful set of functions to convert between text and C string in the backend. Applied with revisions --- the functions were modified as per recent discussion, and I fixed a lot more potential call sites. There are no textout/textin calls left, but I may have missed some places that were doing it the hard way with direct palloc/memcpy manipulations. It might be worth trolling all the VARDATA() references to see if any more are easily replaceable. I notice in particular that xfunc.sgml contains sample C functions to copy and concatenate text. While these aren't directly replaceable with the new functions, I wonder whether we ought to change the examples to make them less certain to break if we ever change text's representation. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] advancing snapshot's xmin
Neil Conway wrote: On Tue, 2008-03-25 at 17:26 -0300, Alvaro Herrera wrote: There is one hole here: contention on ProcArrayLock. Basically, for simple transactions we will need to update MyProc after every command. If we're just updating MyProc-xmin, we only need to acquire ProcArrayLock in shared mode, right? In fact, do you need a lock at all? We already assume that reading/writing a TransactionId is atomic in many places. We acquire ProcArrayLock at the end of transaction when we clear MyProc-xid, to ensure that we don't exit the set of running transactions while someone else is taking a snapshot, but AFAICS that's not necessary when we just advance MyProc-xmin. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] writing a MIN(RECORD) aggregate
Sam Mason [EMAIL PROTECTED] writes: The reason for the sub-select is only because SQL doesn't provide any other way to name expressions. Hum, or at least this should work... There doesn't seem to be any nice way of getting fields out of a record! If I really want to do this, it's going to turn into quite an overhaul of record handling in PG. It would also remove the nice syntactic trick that a.b identifies the field b from table a, and s.a.b means that the above is in schema s. Yeah, to disambiguate it you have to use (r).i -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Script binaries renaming
Where are we on this? Tom thinks we don't want this. TODO has: * Prefix command-line utilities like createuser with 'pg_' http://archives.postgresql.org/pgsql-hackers/2007-06/msg00025.php See for reference: http://momjian.us/mhonarc/patches/[EMAIL PROTECTED] One idea is to keep the existing commands and just add pg_* (or pg*) to additional versions, with the idea that the original versions will be removed some day. --- Zdenek Kotala wrote: I attach complete patch which renames following binaries createdb createlang createuser dropdb droplang dropuser clusterdb vacuumdb reindexdb to pg_createdb pg_createlang pg_createuser pg_dropdb pg_droplang pg_dropuser pg_clusterdb pg_vacuumdb pg_reindexdb Symlinks (or copy on win32) are created for backward compatibility. This renaming was discussed there: http://archives.postgresql.org/pgsql-hackers/2007-06/msg00145.php I create also separate unified patch for documentation. Zdenek [ application/x-gzip is not supported, skipping... ] ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] writing a MIN(RECORD) aggregate
On Wed, Mar 26, 2008 at 01:03:18AM +, Gregory Stark wrote: Sam Mason [EMAIL PROTECTED] writes: The reason for the sub-select is only because SQL doesn't provide any other way to name expressions. Hum, or at least this should work... There doesn't seem to be any nice way of getting fields out of a record! Yeah, to disambiguate it you have to use (r).i OK, that sort of makes sense. The next problem is that PG doesn't remember the column names: SELECT (ROW(i)).i FROM (SELECT 1) x(i); Results in PG saying it doesn't know where i is inside the row, which seems a little strange. I think it's this detail that accounts for my problems in trying to get this all working before. This seems to suggest that there are two record-like data structures in PG, one for the records returned as part of the SELECT list and another that I'm using here. As a side case, would it be nice if: SELECT (SELECT 1 AS a, 2 AS b); resulted in a record with two members? Sam -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Bogosity in contrib/xml2/Makefile
Whilst fooling with bug #4058 I noticed that xml2's .c files were being compiled without -g or any of the various warning flags we normally use. I saw this: gcc -I/usr/include/libxml2 -fpic -I. -I../../src/include -D_GNU_SOURCE -I/usr/include/libxml2 -c -o xpath.o xpath.c when I expected something like this: gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -g -fpic -I. -I../../src/include -D_GNU_SOURCE -I/usr/include/libxml2 -c -o xpath.o xpath.c The reason is apparently this line in its Makefile: override CFLAGS += $(shell xml2-config --cflags) It seems the override locks down the value so that the subsequent assignment in Makefile.global does nothing. I didn't try the PGXS case but I imagine it doesn't do the right thing either. Now, in HEAD and 8.3 I think we could just remove this line, because configure knows how to pull the needed -I and -L flags out of xml2-config's output and stick them into appropriate flag variables (neither of which is CFLAGS btw...). I am not sure what to do in older branches though --- there doesn't seem to be any real nice solution. Even though xml2 is deprecated and may go away for 8.4, I think this is important to fix in the back branches. Failing to use the -f flags for instance could be resulting in outright wrong code, and we'd be unlikely to notice since there's no regression test at all for this module. Thoughts? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] stored procedure stats in collector
This has been saved for the next commit-fest: http://momjian.postgresql.org/cgi-bin/pgpatches_hold --- Martin Pihlak wrote: Howdy, Here's an updated version of the function stats patch. The biggest change is that by default only procedural language functions are counted. The GUC variable stats_function_level now takes 3 values: on, off and all. The latter also counts SQL and C language functions, on means only to count procedural language functions. The decision is now made in fmgr_info_cxt_security() Trigger functions are now also counted. Sample output: select procname, calls, total_time, total_cpu, self_time, self_cpu from pg_stat_user_functions order by self_cpu desc limit 5; procname | calls | total_time | total_cpu | self_time | self_cpu +---++---+---+-- next_batch | 32765 | 27139 | 8574 | 27139 | 8574 fetch_batch_events | 3636 | 9252 | 5622 | 3771 | 2717 batch_event_sql| 3636 | 5454 | 2888 | 3910 | 1962 finish_batch | 3420 | 3215 | 1475 | 3215 | 1475 batch_event_tables | 3636 | 1448 | 865 | 1434 | 858 (5 rows) This is still 8.2 only (tested on 8.2.4 and 8.2.5), has seen some production usage here at Skype (about a month on reasonably busy boxes). So far so good. Couple of issues: - sometimes self_cpu self_time - currently blaming it on Linux version of gettimeofday(). - dropped functions are not purged from stats, might bloat the stats file for some usage patterns. PS. Would something like this be a canditate for 8.4 inclusion (if polished up)? Regards, Martin [ application/x-gzip is not supported, skipping... ] ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] advancing snapshot's xmin
Heikki Linnakangas [EMAIL PROTECTED] writes: Neil Conway wrote: If we're just updating MyProc-xmin, we only need to acquire ProcArrayLock in shared mode, right? In fact, do you need a lock at all? I think you probably do. GetSnapshotData needs to be confident that the global xmin it computes is = the xmin that any other backend might be about to store into its MyProc-xmin; how can you ensure that if there's no locking happening? Now the way I'd been envisioning this would work is that whenever the number of active snapshots goes to zero, we clear MyProc-xmin, and that probably could be done without a lock. Then the next time we do GetSnapshotData, it would compute and store a new MyProc-xmin (this would be the same activity that we currently think of as setting the serializable snapshot). So you don't need any more locking than already exists. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Reducing Transaction Start/End Contention
Added to TODO: * Consider transaction start/end performance improvements http://archives.postgresql.org/pgsql-hackers/2007-07/msg00948.php http://archives.postgresql.org/pgsql-hackers/2008-03/msg00361.php --- Simon Riggs wrote: Jignesh Shah's scalability testing on Solaris has revealed further tuning opportunities surrounding the start and end of a transaction. Tuning that should be especially important since async commit is likely to allow much higher transaction rates than were previously possible. There is strong contention on the ProcArrayLock in Exclusive mode, with the top path being CommitTransaction(). This becomes clear as the number of connections increases, but it seems likely that the contention can be caused in a range of other circumstances. My thoughts on the causes of this contention are that the following 3 tasks contend with each other in the following way: CommitTransaction(): takes ProcArrayLock Exclusive but only needs access to one ProcArray element waits for GetSnapshotData():ProcArrayLock Shared ReadNewTransactionId():XidGenLock Shared which waits for GetNextTransactionId() takes XidGenLock Exclusive ExtendCLOG(): takes ClogControlLock Exclusive, WALInsertLock Exclusive two possible place where I/O is required ExtendSubtrans(): takes SubtransControlLock() one possible place where I/O is required Avoids lock on ProcArrayLock: atomically updates one ProcArray element or more simply: CommitTransaction() -- i.e. once per transaction waits for GetSnapshotData() -- i.e. once per SQL statement which waits for GetNextTransactionId() -- i.e. once per transaction This gives some goals for scalability improvements and some proposals. (1) and (2) are proposals for 8.3 tuning, the others are directions for further research. Goal: Reduce total time that GetSnapshotData() waits for GetNextTransactionId() 1. Increase size of Clog-specific BLCKSZ Clog currently uses BLCKSZ to define the size of clog buffers. This can be changed to use CLOG_BLCKSZ, which would then be set to 32768. This will naturally increase the amount of memory allocated to the clog, so we need not alter CLOG_BUFFERS above 8 if we do this (as previously suggested, with successful results). This will also reduce the number of ExtendClog() calls, which will probably reduce the overall contention also. 2. Perform ExtendClog() as a background activity Background process can look at the next transactionid once each cycle without holding any lock. If the xid is almost at the point where a new clog page would be allocated, then it will allocate one prior to the new page being absolutely required. Doing this as a background task would mean that we do not need to hold the XidGenLock in exclusive mode while we do this, which means that GetSnapshotData() and CommitTransaction() would also be less likely to block. Also, if any clog writes need to be performed when the page is moved forwards this would also be performed in the background. 3. Consider whether ProcArrayLock should use a new queued-shared lock mode that puts a maximum wait time on ExclusiveLock requests. It would be fairly hard to implement this well as a timer, but it might be possible to place a limit on queue length. i.e. allow Share locks to be granted immediately if a Shared holder already exists, but only if there is a queue of no more than N exclusive mode requests queued. This might prevent the worst cases of exclusive lock starvation. 4. Since shared locks are currently queued behind exclusive requests when they cannot be immediately satisfied, it might be worth reconsidering the way LWLockRelease works also. When we wake up the queue we only wake the Shared requests that are adjacent to the head of the queue. Instead we could wake *all* waiting Shared requestors. e.g. with a lock queue like this: (HEAD)S-S-X-S-X-S-X-S Currently we would wake the 1st and 2nd waiters only. If we were to wake the 3rd, 5th and 7th waiters also, then the queue would reduce in length very quickly, if we assume generally uniform service times. (If the head of the queue is X, then we wake only that one process and I'm not proposing we change that). That would mean queue jumping right? Well thats what already happens in other circumstances, so there cannot be anything intrinsically wrong with allowing it, the only question is: would it help? We need not wake the whole queue, there may be some generally more beneficial heuristic. The reason for considering this is not to speed up Shared requests but to reduce the queue length and thus the waiting time for the Xclusive requestors. Each time a Shared request is dequeued, we effectively re-enable queue jumping, so a Shared request arriving during that point will actually jump ahead of Shared requests that were unlucky enough to arrive
Re: [HACKERS] Script binaries renaming
Bruce Momjian [EMAIL PROTECTED] writes: Where are we on this? Tom thinks we don't want this. TODO has: * Prefix command-line utilities like createuser with 'pg_' http://archives.postgresql.org/pgsql-hackers/2007-06/msg00025.php It wasn't just me; quite a few people were dubious about it when the patch was submitted. See the thread here: http://archives.postgresql.org/pgsql-patches/2007-07/msg00055.php One idea is to keep the existing commands and just add pg_* (or pg*) to additional versions, with the idea that the original versions will be removed some day. AFAICS the only argument for doing this is to eliminate confusion and potential conflicts, which means that we get no benefit at all until we actually do remove the old names. So if we're going to do this, we have to make a commitment that we're going to remove the old names within the reasonably foreseeable future (say, about two releases out). Are we really prepared to break everyone's scripts for this? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pl/pgsql Plan Invalidation and search_path
On Mon, Mar 24, 2008 at 9:48 PM, Bruce Momjian [EMAIL PROTECTED] wrote: Add to pl/pgsql TODO: o Consider invalidating the cache or keeping seperate cached copies when search_path changes http://archives.postgresql.org/pgsql-hackers/2008-01/msg01009.php IMO, Tom's idea, namely to keep separate cache plans for various search_path settings, is a much stronger proposal and should probably get the 'todo'. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pl/pgsql Plan Invalidation and search_path
Merlin Moncure wrote: On Mon, Mar 24, 2008 at 9:48 PM, Bruce Momjian [EMAIL PROTECTED] wrote: Add to pl/pgsql TODO: o Consider invalidating the cache or keeping seperate cached copies when search_path changes http://archives.postgresql.org/pgsql-hackers/2008-01/msg01009.php IMO, Tom's idea, namely to keep separate cache plans for various search_path settings, is a much stronger proposal and should probably get the 'todo'. Done: o Consider keeping seperate cached copies when search_path changes -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCHES] Add function for quote_qualified_identifier?
Brendan Jurd wrote: On 9/29/07, Bruce Momjian [EMAIL PROTECTED] wrote: I think we need more than one person's request to add this function. Well, I don't expect it would get requested. Most DBAs would likely look for the function in the docs, see it's not there and then just implement it themselves. Obviously it's not critical. But anticipating those little requirements and providing for them is one of the things that makes a piece of software a pleasure to use. Batteries included and all that. Anyway, I seem to be flogging a horse which, if not dead, is surely mortally wounded. If quote_qualified_ident isn't desired, perhaps you can still use the regression test I included for quote_ident in the patch. The test is functional as a standalone item, and seems to fill a gap. I think we did our best to find requests for this feature. If they ever pop up in the future we can always recover this patch from the archives. Sorry. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Script binaries renaming
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Where are we on this? Tom thinks we don't want this. TODO has: * Prefix command-line utilities like createuser with 'pg_' http://archives.postgresql.org/pgsql-hackers/2007-06/msg00025.php It wasn't just me; quite a few people were dubious about it when the patch was submitted. See the thread here: http://archives.postgresql.org/pgsql-patches/2007-07/msg00055.php True. One idea is to keep the existing commands and just add pg_* (or pg*) to additional versions, with the idea that the original versions will be removed some day. AFAICS the only argument for doing this is to eliminate confusion and potential conflicts, which means that we get no benefit at all until we actually do remove the old names. So if we're going to do this, we have to make a commitment that we're going to remove the old names within the reasonably foreseeable future (say, about two releases out). Are we really prepared to break everyone's scripts for this? Uh, I think it is hard to make a case that 'createuser' is an appropriate name for a Postgres utility. On the other hand, we haven't had many complaints about it, which is kind of odd. I feel people can always symlink in the old names if they still want them after we remove the old names. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Script binaries renaming
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 - --On Tuesday, March 25, 2008 22:51:53 -0400 Bruce Momjian [EMAIL PROTECTED] wrote: Uh, I think it is hard to make a case that 'createuser' is an appropriate name for a Postgres utility. On the other hand, we haven't had many complaints about it, which is kind of odd. If nobody has ever complained, what is the reason for the change? How many ppl are going to complain because the commands they are used to suddenly stop existing? - -- Marc G. FournierHub.Org Hosting Solutions S.A. (http://www.hub.org) Email . [EMAIL PROTECTED] MSN . [EMAIL PROTECTED] Yahoo . yscrappy Skype: hub.orgICQ . 7615664 -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.4 (FreeBSD) iD8DBQFH6dPA4QvfyHIvDvMRAj2AAKDQ2r2L8ztHDeUhBBSD10VwbttXugCgksd8 g8Tq27/AorIuM1Yo8nh1vbc= =JnjX -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
