Re: [HACKERS] pg_class catalog question...
On Apr 2, 2006, at 6:13 PM, Tom Lane wrote: Jonah H. Harris [EMAIL PROTECTED] writes: On 4/2/06, Tom Lane [EMAIL PROTECTED] wrote: If you're expecting that you'll be able to write BYTEA(n) and avoid storing a length word, you'll find that it's not a trivial matter. It may not be trivial, but it's certainly not impossible. A word to the wise is sufficient: function result types don't have known typmods, and for the most part expression results don't either. Changing that is not impossible, but the level of pain vastly exceeds what this feature would be worth. And that's not even the only problem. If you're desperate to have something like this, you could create one or more fixed-size datatypes (ie, with various positive typlen values). But I don't see a practical way to use a typmod in determining the physical width. I'm not sure how other databases handle this, but I suspect it would be OK performance-wise to tack on a length byte for these types when dealing with functions and anything else that isn't directly tied to a table where you can easily get length info from the catalog. Actually, how is this handled with varchar(x)? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pg_class catalog question...
On Mon, Apr 03, 2006 at 01:31:50PM -0400, Jim Nasby wrote: I'm not sure how other databases handle this, but I suspect it would be OK performance-wise to tack on a length byte for these types when dealing with functions and anything else that isn't directly tied to a table where you can easily get length info from the catalog. Actually, how is this handled with varchar(x)? By storing the length in the Datum. This discussion as about whether we could support something like HEX(n) without storing the (n) in the data field but only in the catalog. varchar(n) doesn't have this issue because we always store the length, so everywhere that needs to know already does. If your not worried about the length field you could code this up in an afternoon. In fact, it's probably already been done... -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] pg_class catalog question...
On Sat, Apr 01, 2006 at 05:42:34PM +0200, Thomas Hallgren wrote: Jim C. Nasby wrote: On Fri, Mar 31, 2006 at 11:29:15AM -0500, Tom Lane wrote: This argument falls flat when you consider that the width of a CHAR entry is measured in characters, not bytes, and therefore its physical size is not fixed even if its logical width is. True, but in every case I've used char it was to store something that would never be multi-byte, like a GUID, or a SHA1. Though I guess in retrospect, what would really be handy is 'hex' datatype, that stores a hex string (possibly with a custom format, such as a GUID) in it's native binary format. Why not simply a fixed number of bytes, i.e. byte(16) or octet(16)? Hexadecimal is just a convenient human-readable representation. Well, hex is much easier to deal with in many regards than raw bytes, though. But yes, the idea is that you'd just store raw bytes on disk. byte or octet would work fine if they existed. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] pg_class catalog question...
Jim C. Nasby wrote: On Sat, Apr 01, 2006 at 05:42:34PM +0200, Thomas Hallgren wrote: Why not simply a fixed number of bytes, i.e. byte(16) or octet(16)? Hexadecimal is just a convenient human-readable representation. Well, hex is much easier to deal with in many regards than raw bytes, though. But yes, the idea is that you'd just store raw bytes on disk. byte or octet would work fine if they existed. IIRC, Oracle actually uses the term RAW. It makes sense I think. No conversion applied, no nothing. Just simple raw data. - thomas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_class catalog question...
Thomas Hallgren [EMAIL PROTECTED] writes: Jim C. Nasby wrote: Well, hex is much easier to deal with in many regards than raw bytes, though. But yes, the idea is that you'd just store raw bytes on disk. byte or octet would work fine if they existed. IIRC, Oracle actually uses the term RAW. It makes sense I think. No conversion applied, no nothing. Just simple raw data. bytea does that. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] pg_class catalog question...
On 4/2/06, Tom Lane [EMAIL PROTECTED] wrote: bytea does that. Yep. However, I've wanted to add a constrained, fixed-length version of bytea for some time now; it's just not high on my priority list. At EnterpriseDB, we've actually had a lot of customers who would prefer a constrained bytea (like Oracle's RAW), rather than variable-length. However, many people end up liking bytea better just because they don't like the limitation's of Oracle's RAW data type. Just depends on the application. I'll probably get to this in the next couple weeks unless someone wants to beat me to it :) -- Jonah H. Harris, Database Internals Architect EnterpriseDB Corporation 732.331.1324 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_class catalog question...
Jonah H. Harris [EMAIL PROTECTED] writes: Yep. However, I've wanted to add a constrained, fixed-length version of bytea for some time now; it's just not high on my priority list. If you're expecting that you'll be able to write BYTEA(n) and avoid storing a length word, you'll find that it's not a trivial matter. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] pg_class catalog question...
On 4/2/06, Tom Lane [EMAIL PROTECTED] wrote: If you're expecting that you'll be able to write BYTEA(n) and avoid storing a length word, you'll find that it's not a trivial matter. It may not be trivial, but it's certainly not impossible. -- Jonah H. Harris, Database Internals Architect EnterpriseDB Corporation 732.331.1324 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pg_class catalog question...
Jonah H. Harris [EMAIL PROTECTED] writes: On 4/2/06, Tom Lane [EMAIL PROTECTED] wrote: If you're expecting that you'll be able to write BYTEA(n) and avoid storing a length word, you'll find that it's not a trivial matter. It may not be trivial, but it's certainly not impossible. A word to the wise is sufficient: function result types don't have known typmods, and for the most part expression results don't either. Changing that is not impossible, but the level of pain vastly exceeds what this feature would be worth. And that's not even the only problem. If you're desperate to have something like this, you could create one or more fixed-size datatypes (ie, with various positive typlen values). But I don't see a practical way to use a typmod in determining the physical width. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_class catalog question...
On 4/2/06, Tom Lane [EMAIL PROTECTED] wrote: Changing that is not impossible, but the level of pain vastly exceeds what this feature would be worth. I really like the wording, the level of pain... so true :) you could create one or more fixed-size datatypes (ie, with various positive typlen values). But I don't see a practical way to use a typmod in determining the physical width. Thanks for the suggestion and caution. I hope to look into this when I get some time in the next month or so. Likewise, I will inform everyone of my planned implementation after some inspection. -- Jonah H. Harris, Database Internals Architect EnterpriseDB Corporation 732.331.1324 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pg_class catalog question...
On Fri, Mar 31, 2006 at 11:29:15AM -0500, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: About the only reason I use CHAR in other databases systems is when I know that the field will always contain the same amount of data, ie: storing a SHA1. In these cases it's silly to have a 4 byte overhead to store length. I really wish CHAR in PostgreSQL worked this way, so it would be a welcome addition to have a type that did work this way. In fact, I'd argue that CHAR should be made to work that way, and what's currently called CHAR should be renamed for those who wish to use it. This argument falls flat when you consider that the width of a CHAR entry is measured in characters, not bytes, and therefore its physical size is not fixed even if its logical width is. True, but in every case I've used char it was to store something that would never be multi-byte, like a GUID, or a SHA1. Though I guess in retrospect, what would really be handy is 'hex' datatype, that stores a hex string (possibly with a custom format, such as a GUID) in it's native binary format. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] pg_class catalog question...
Jim C. Nasby wrote: On Fri, Mar 31, 2006 at 11:29:15AM -0500, Tom Lane wrote: This argument falls flat when you consider that the width of a CHAR entry is measured in characters, not bytes, and therefore its physical size is not fixed even if its logical width is. True, but in every case I've used char it was to store something that would never be multi-byte, like a GUID, or a SHA1. Though I guess in retrospect, what would really be handy is 'hex' datatype, that stores a hex string (possibly with a custom format, such as a GUID) in it's native binary format. Why not simply a fixed number of bytes, i.e. byte(16) or octet(16)? Hexadecimal is just a convenient human-readable representation. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] pg_class catalog question...
Jonah H. Harris [EMAIL PROTECTED] wrote Yeah, I noticed that one. How would you suggest setting CLASS_TUPLE_SIZE in that case? What if you put your char[64] before relhassubclass, then you don't change CLASS_TUPLE_SIZE. Regards, Qingqing ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] pg_class catalog question...
On 3/31/06, Qingqing Zhou [EMAIL PROTECTED] wrote: What if you put your char[64] before relhassubclass, then you don't change CLASS_TUPLE_SIZE. Thought about that... but it would be an ugly place for this column. I know I could get around it by renumbering the attribute, but that's just a kludge. Now that I've had some sleep, I'm sure I'll get it working :) -- Jonah H. Harris, Database Internals Architect EnterpriseDB Corporation 732.331.1324 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] pg_class catalog question...
On 3/31/06, Alvaro Herrera [EMAIL PROTECTED] wrote: What are you using a char[64] for anyway? You should probably consider using NameData, if you want to store an identifier. It's just a fixed length string that will never change in size and as such, I'd like not to add the overhead of any variable-length handling. -- Jonah H. Harris, Database Internals Architect EnterpriseDB Corporation 732.331.1324 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] pg_class catalog question...
On Fri, Mar 31, 2006 at 10:45:15AM -0500, Jonah H. Harris wrote: On 3/31/06, Alvaro Herrera [EMAIL PROTECTED] wrote: What are you using a char[64] for anyway? You should probably consider using NameData, if you want to store an identifier. It's just a fixed length string that will never change in size and as such, I'd like not to add the overhead of any variable-length handling. What about creating a fixed-size general purpose type? About the only reason I use CHAR in other databases systems is when I know that the field will always contain the same amount of data, ie: storing a SHA1. In these cases it's silly to have a 4 byte overhead to store length. I really wish CHAR in PostgreSQL worked this way, so it would be a welcome addition to have a type that did work this way. In fact, I'd argue that CHAR should be made to work that way, and what's currently called CHAR should be renamed for those who wish to use it. I've yet to run across a use for CHAR where you might actually have a variable amount of data stored and just want to enforce a certain number of space padding. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_class catalog question...
Jim C. Nasby [EMAIL PROTECTED] writes: About the only reason I use CHAR in other databases systems is when I know that the field will always contain the same amount of data, ie: storing a SHA1. In these cases it's silly to have a 4 byte overhead to store length. I really wish CHAR in PostgreSQL worked this way, so it would be a welcome addition to have a type that did work this way. In fact, I'd argue that CHAR should be made to work that way, and what's currently called CHAR should be renamed for those who wish to use it. This argument falls flat when you consider that the width of a CHAR entry is measured in characters, not bytes, and therefore its physical size is not fixed even if its logical width is. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pg_class catalog question...
On 3/31/06, Tom Lane [EMAIL PROTECTED] wrote: This argument falls flat when you consider that the width of a CHAR entry is measured in characters, not bytes, and therefore its physical size is not fixed even if its logical width is. Gotta love multibyte :) -- Jonah H. Harris, Database Internals Architect EnterpriseDB Corporation 732.331.1324 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pg_class catalog question...
Jonah H. Harris [EMAIL PROTECTED] writes: Does anyone know how to add a fixed-length char field to pg_class? Changing any of the bootstrap catalogs is fairly tricky --- there are a lot of places you have to update manually. I'd suggest looking for a previous commit that did something similar and studying the diff. [ digs in CVS log... ] Here are a couple of possibilities; the first one is smaller but it's touching pg_proc not pg_class. 2005-03-29 14:44 tgl * doc/src/sgml/bki.sgml, doc/src/sgml/catalogs.sgml, src/backend/bootstrap/bootstrap.c, src/backend/catalog/pg_proc.c, src/include/catalog/catversion.h, src/include/catalog/pg_attribute.h, src/include/catalog/pg_class.h, src/include/catalog/pg_proc.h: Add proallargtypes and proargmodes columns to pg_proc, as per my earlier proposal for OUT parameter support. The columns don't actually *do* anything yet, they are just left NULLs. But I thought I'd commit this part separately as a fairly pure example of the tasks needed when adding a column to pg_proc or one of the other core system tables. 2002-03-26 14:15 tgl * doc/src/sgml/catalogs.sgml, src/backend/access/heap/heapam.c, src/backend/access/index/indexam.c, src/backend/bootstrap/bootparse.y, src/backend/bootstrap/bootstrap.c, src/backend/catalog/Makefile, src/backend/catalog/aclchk.c, src/backend/catalog/genbki.sh, src/backend/catalog/heap.c, src/backend/catalog/index.c, src/backend/catalog/indexing.c, src/backend/catalog/namespace.c, src/backend/commands/cluster.c, src/backend/commands/command.c, src/backend/commands/comment.c, src/backend/commands/creatinh.c, src/backend/commands/indexcmds.c, src/backend/commands/rename.c, src/backend/commands/trigger.c, src/backend/commands/user.c, src/backend/executor/execMain.c, src/backend/parser/analyze.c, src/backend/parser/parse_clause.c, src/backend/parser/parse_relation.c, src/backend/rewrite/rewriteDefine.c, src/backend/tcop/utility.c, src/backend/utils/adt/acl.c, src/backend/utils/cache/catcache.c, src/backend/utils/cache/lsyscache.c, src/backend/utils/cache/relcache.c, src/backend/utils/cache/syscache.c, src/include/access/genam.h, src/include/access/heapam.h, src/include/bootstrap/bootstrap.h, src/include/catalog/catversion.h, src/include/catalog/heap.h, src/include/catalog/index.h, src/include/catalog/indexing.h, src/include/catalog/namespace.h, src/include/catalog/pg_attribute.h, src/include/catalog/pg_class.h, src/include/commands/cluster.h, src/include/commands/command.h, src/include/commands/comment.h, src/include/commands/defrem.h, src/include/commands/rename.h, src/include/nodes/parsenodes.h, src/include/nodes/primnodes.h, src/include/utils/catcache.h, src/include/utils/lsyscache.h, src/include/utils/rel.h, src/include/utils/relcache.h, src/include/utils/syscache.h, src/pl/plpgsql/src/pl_comp.c: pg_class has a relnamespace column. You can create and access tables in schemas other than the system namespace; however, there's no search path yet, and not all operations work yet on tables outside the system namespace. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] pg_class catalog question...
On 3/30/06, Tom Lane [EMAIL PROTECTED] wrote: Here are a couple of possibilities; the first one is smaller but it's touching pg_proc not pg_class. Yeah, I noticed that one. How would you suggest setting CLASS_TUPLE_SIZE in that case? -- Jonah H. Harris, Database Internals Architect EnterpriseDB Corporation 732.331.1324 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
