[HACKERS] GIN, XLogInsert and MarkBufferDirty
Hi Teodor, I think there's a little bug in ginInsertValue. A page is marked as dirty with MarkBufferDirty after writing the corresponding WAL record with XLogInsert. That's not safe, MarkBufferDirty needs to be called before XLogInsert to avoid a race condition in checkpoint, see comments in SyncOneBuffer in bufmgr.c for an explanation. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] TOAST usage setting
The big question is do we want to drop the target tuple size down to 512, and increase the chunk size to 8k for 8.3? Dropping the tuple size down to 512 is going to give us some smaller TOAST values to fill in free space created by the 8k chuck size, assuming you have both types of values in the table. Do we want to increase the access time of long TOAST by 6% if it means having more wasted space for lots of 4.1k values? If we do that people could see their disk space usage increase by up to 16x: currently 513 bytes fits in heap and takes (roughly) 513 bytes; No, you misunderstood. Bruce was suggesting changing the target to 512. That means if a row is wider than ~2k, toaster will try to toast until the base row is ~512 bytes. I would not do that part for 8.3. if we make that change it would then get toasted and take 8K. I don't think we want to do that. Disk space aside, it's almost certain to seriously hurt performance as soon as you don't fit entirely in memory. No, allowing one toast chunk to fill a page does not mean that every chunk uses a whole page. Andreas ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Performance regression on CVS head
Tom Lane wrote: Heikki Linnakangas [EMAIL PROTECTED] writes: I tried to repeat the DBT-2 runs with the oldestxmin refresh patch, but to my surprise the baseline run with CVS head, without the patch, behaved very differently than it did back in March. I rerun the a shorter 1h test with CVS head from May 20th, and March 6th (which is when I ran the earlier tests), and something has clearly been changed between those dates that affects the test. Test run 248 is with CVS checkout from May 20th, and 249 is from March 6th: May 20th is not quite my idea of HEAD ;-). It might be worth checking current code before investing any think-time on this. :) Yeah, I did run it with real head at first. I suspected the n_live_tuples calculations, and that's why I ran it again with a checkout from May 20th. But having said that, it looks a bit like a planner problem --- if I'm reading the graphs correctly, I/O wait time goes through the roof, suggesting a change to a much less efficient plan. Right. I'll do a binary search with a checkouts from different dates runs to pin it down. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] GIN, XLogInsert and MarkBufferDirty
with XLogInsert. That's not safe, MarkBufferDirty needs to be called before XLogInsert to avoid a race condition in checkpoint, see comments in SyncOneBuffer in bufmgr.c for an explanation. Ugh, thank you fixed. It's a trace of misunderstood of WriteBuffer(). -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] libpq and Binary Data Formats
Wilhansen Li wrote: Basically, better support for binary formats which includes, but not limited to: 1) functions for converting to and from various datatypes 2) reducing the need to convert to and from network byte order 3) better documentation My suggestion on using ASN.1 was merely a naive suggestion on how in can be implemented properly without breaking (future) compatibility because that seems to be the main problem which prevents the use of binary formats. Well, it sounds to me like this is two separate items: (1+2), (3). For (3) there is the pgsql-docs mailing list. If you have additions/changes, that's the place you want. Submissions in text are fine, you don't need to worry about SGML formatting, but do discuss them first. The documentation relies on people saying I don't think this bit is clear, so help is always welcome. For (1+2) it sounds like what you actually want is a native binary for my application protocol rather than internal binary format which is sort of what's available now. Clearly application binary is an addition rather than a replacement (unless everyone using binary transfers thinks it's so much better they're happy to switch immediately). A few obvious questions leap out at me: 1. What languages are you seeking to target: just C? 2. What platforms are you seeking to target: intel 32 bit? 64 bit? powerpc? arm? 3. How much do I gain (and lose) over text transfer, and under what circumstances? 4. What will happen with custom/user-defined types? Will they need their own adaptor written to support this? Crucially, I think you want to demonstrate #3 - that there's a clear gain for all the work that's involved in defining a separate transfer encoding. If you can demonstrate the gains are felt by all the Perl/PHP/Java applications too that'd obviously help. Bear in mind I'm just another user of PostgreSQL, not a developer, so you could do everything I've said and still not interest core in making changes. However, I've seen a lot of changes come and go and I think you'll need to make progress on those 4 points to get anywhere. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] [HACHERS] privilege check: column level only?
Am Dienstag, 5. Juni 2007 06:39 schrieb Golden Liu: According to this, column privilege descriptors are created automatically while table privilege descriptor is created. Then, while checking privilege, can I JUST check column level privilege? While possible, for performance reasons it would probably be unwise. Needs checking. Any and all help and/or comment is appreciated. From sql standard, I found no information on how privilege check should be done. The SQL standard only explains constraints on the behavior of an implementation, not how to implement it. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] How do we create the releases?
Hi Marc, Is there a written procedure about creating tarballs? I'd like to start working on 8.3 RPMs and I want to know what I should to to create a tarball. Regards, -- Devrim GÜNDÜZ PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/ signature.asc Description: This is a digitally signed message part
Re: [HACKERS] [HACHERS] privilege check: column level only?
Peter Eisentraut wrote: Am Dienstag, 5. Juni 2007 06:39 schrieb Golden Liu: According to this, column privilege descriptors are created automatically while table privilege descriptor is created. Then, while checking privilege, can I JUST check column level privilege? While possible, for performance reasons it would probably be unwise. Needs checking. We can possibly infer their existence according to the table level privileges in certain cases. But it's not clear to me how that will work when we change the table level privileges, nor how it works with revoked privileges. Do we have any provision for negative privileges? If not, do we need them? cheers andrew ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[HACKERS] CREATEROLE, CREATEDB
Is it correct that a user with CREATEROLE privilege but without CREATEDB privilege can create a user with *CREATEDB* privilege, thus bypassing his original restrictions? This sequence doesn't look right: pei=# create user foo1 createrole; CREATE ROLE pei=# \c - foo1 You are now connected to database pei as user foo1. pei= create database test; ERROR: permission denied to create database pei= create user foo2 createdb; CREATE ROLE pei= \c - foo2 You are now connected to database pei as user foo2. pei= create database test; CREATE DATABASE -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] CREATEROLE, CREATEDB
--On Dienstag, Juni 05, 2007 16:04:44 +0200 Peter Eisentraut [EMAIL PROTECTED] wrote: Is it correct that a user with CREATEROLE privilege but without CREATEDB privilege can create a user with *CREATEDB* privilege, thus bypassing his original restrictions? This sequence doesn't look right: pei=# create user foo1 createrole; CREATE ROLE pei=# \c - foo1 You are now connected to database pei as user foo1. pei= create database test; ERROR: permission denied to create database pei= create user foo2 createdb; CREATE ROLE pei= \c - foo2 You are now connected to database pei as user foo2. pei= create database test; CREATE DATABASE I had this issue once, too. CREATEROLE doesn't imply any inheritance from a role which gots this privilege, thus you are required to treat such roles much the same as superuser. This behavior is documented (well, at least in 8.2, haven't looked in 8.1): http://www.postgresql.org/docs/8.2/interactive/sql-createrole.html snip Be careful with the CREATEROLE privilege. There is no concept of inheritance for the privileges of a CREATEROLE-role. That means that even if a role does not have a certain privilege but is allowed to create other roles, it can easily create another role with different privileges than its own (except for creating roles with superuser privileges). For example, if the role user has the CREATEROLE privilege but not the CREATEDB privilege, nonetheless it can create a new role with the CREATEDB privilege. Therefore, regard roles that have the CREATEROLE privilege as almost-superuser-roles. /snip -- Thanks Bernd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] libpq and Binary Data Formats
On 6/4/07, Wilhansen Li [EMAIL PROTECTED] wrote: First of all, apologies if this was not meant to be a feedback/wishlist mailing list. Binary formats in libpq has been (probably) a long issue (refer to the listings below) and I want to express my hope that the next revision of PostgreSQL would have better support for binary data types in libpq. I am in no doubt that those binary vs. text debates sprouted because of PostgreSQL's (or rather libpq's) ambiguity when it comes to binary data support. One instance is the documentation itself: it didn't really say (correct me if I'm wrong) that binary data is poorly/not supported and that textual data is preferred. Moreover, those ambiguities are only cleared up in mailing lists/irc/forums which make it seem that the arguments for text data is just an excuse to not have proper support for binary data ( e.x. C:Elephant doesn't support Hammer! P: You don't really need Hammer (we don't support it yet), you can do it with Screwdriver.). This is not meant to be a binary vs. text post so I'll reserve my comments for them. Nevertheless, they each have their own advantages and disadvantages especially when it comes to strongly typed languages that neither shouldn't be ignored. I am well-aware of the problems associated with binary formats and backward/forward compatibility: http://archives.postgresql.org/pgsql-hackers/1999-08/msg00374.php but nevertheless, that shouldn't stop PostgreSQL/libpq's hardworking developers from coming up with a solution. The earling link showed the interest of using CORBA to handle PostgreSQL objects but I belive that it's an overkill and would like to propose using ASN.1 instead. However, what's important is not really the binary/text representation. If we look again the the list below, not everyone need binary formats just for speed and efficiency, rather, they need it to be able to easily manipulate data. In other words, the interfaces to extract data is also important. Personally, I wouldn't mind seeing the libpq API extended to support arrays and record structures. PostgreSQL 8.3 is bringing arrays of composite types and the lack of client side support of these structures is becoming increasingly glaring. If set up with text/binary switch, this would deal with at least part of your objections. I think most people here would agree that certain aspects of the documentation of binary formats are a bit weak and could use improvement (although, it's possible that certain formats were deliberately not documented because they may change). A classy move would be to make specific suggestions in -docs and produce a patch. ISTM to me that many if not most people who are looking at binary interfaces to the database are doing it for the wrong reasons and you should consider that when reviewing historical discussions :-). Also, dealing with large bytea types in the databases which is probably the most common use case, is pretty well covered in libpq documentation IMO. merlin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [COMMITTERS] pgsql: Create a GUC parametertemp_tablespacesthat allows selection of
--On Montag, Juni 04, 2007 15:34:14 -0400 Tom Lane [EMAIL PROTECTED] wrote: The reason I'm thinking per-transaction is that we could tie this to setting up a cached list of tablespace OIDs, which would avoid the overhead of repeat parsing and tablespace validity checking. We had rejected using a long-lived cache because of the problem of tablespaces getting dropped, but I think one that lasts only across a transaction would be OK. Hmm i tried an allocated oid list in TopMemoryContext per backend, but i didn't find any issue with that... What's the reason we cannot work with a long-living cache during backend lifetime? Dropping a tablespace caused get_tablespace_name() to return an InvalidOid and the tablespace selection code to switch to $PGDATA/pgsql_tmp... -- Thanks Bernd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] GIN, XLogInsert and MarkBufferDirty
Heikki Linnakangas [EMAIL PROTECTED] writes: ... MarkBufferDirty needs to be called before XLogInsert to avoid a race condition in checkpoint, see comments in SyncOneBuffer in bufmgr.c for an explanation. Right, see also the Write-Ahead Log coding section in src/backend/access/transam/README (which is maybe not a very good place for it, but it doesn't seem like bufmgr's turf either). 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] [COMMITTERS] pgsql: Create a GUC parametertemp_tablespacesthat allows selection of
--On Montag, Juni 04, 2007 15:34:14 -0400 Tom Lane [EMAIL PROTECTED] wrote: Perhaps a reasonable compromise could work like this: at the first point in a transaction where a temp file is created, choose a random list element, and thereafter advance cyclically for the duration of that transaction. This ensures within-transaction spread-out while still having some randomness across backends. Doing this on transaction-level looks pretty nice; The original code choose the random element on backend startup (or every time you call SET). Btw. i saw you've removed the random selection implemented by MyProcId % num_temp_tablespaces. I liked this idea, because PID should be pretty random on many OS? -- Thanks Bernd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [HACHERS] privilege check: column level only?
Golden Liu [EMAIL PROTECTED] writes: According to this, column privilege descriptors are created automatically while table privilege descriptor is created. Then, while checking privilege, can I JUST check column level privilege? Since we don't have any, no ;-) You could imagine implementing it as the spec suggests, but storing all those per-column privileges would be bulky and usually redundant. I think part of the TODO item here is to think of a more intelligent representation that only stores a column privilege descriptor when it's different from the table-level privileges. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] libpq and Binary Data Formats
Richard Huxton [EMAIL PROTECTED] writes: Wilhansen Li wrote: Basically, better support for binary formats which includes, but not limited to: 1) functions for converting to and from various datatypes 2) reducing the need to convert to and from network byte order 3) better documentation Well, it sounds to me like this is two separate items: (1+2), (3). I could see adding more support in libpq for converting native int and float types to and from the existing on-the-wire binary formats, rather than making applications do it for themselves as is the case now. But I think you've got 0 chance of persuading anyone that we should try to support platform-dependent on-the-wire formats --- the potential performance advantages are minimal and the added complexity large. IOW, 1, 3 yes, 2 no. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] CREATEROLE, CREATEDB
Bernd Helmle [EMAIL PROTECTED] writes: --On Dienstag, Juni 05, 2007 16:04:44 +0200 Peter Eisentraut [EMAIL PROTECTED] wrote: Is it correct that a user with CREATEROLE privilege but without CREATEDB privilege can create a user with *CREATEDB* privilege, thus bypassing his original restrictions? I had this issue once, too. CREATEROLE doesn't imply any inheritance from a role which gots this privilege, thus you are required to treat such roles much the same as superuser. This behavior is documented (well, at least in 8.2, haven't looked in 8.1): This is by design --- the point of CREATEROLE is that you can do anything you want in the line of account management, without having all the dangers inherent in being a real superuser. It's not something you'd give out to people you didn't trust. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Command tags in create/drop scripts
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Is this a TODO? I don't think so; there is no demand from anybody but Zdenek to remove those programs. Has it ever even come up before? Tom, Bruce I started with postgres 6.5 as administrator and from this version names of these utilities have been for me little bit confusing and annoying. I have hoped that it will be changed in the future. It is my personal feeling since 1999. I prefer to add on TODO list and also question is why we don't have: pg_ctl -D path init instead of initdb command? Zdenek ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Command tags in create/drop scripts
Zdenek Kotala wrote: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Is this a TODO? I don't think so; there is no demand from anybody but Zdenek to remove those programs. Has it ever even come up before? Personally I found really strange to have createuser and createdb shipped by Postgres when I started using it. I just didn't complain. pg_ctl -D path init instead of initdb command? Seems a reasonable thing to do too. Harder to type for developers, but how much of a problem this is for real users? And developers can script the thing anyway so I don't think this is a real problem. One idea is to move the offending binaries into libexec, and have pg_ctl or other helpers (pg_cmd) call them; and where compatibility with the old versions is requested, create symlinks in bindir. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Implicit casts with generic arrays
I wrote:
I've been experimenting with another solution, which is to not add any
weird error cases but instead add operators that will capture the
problem cases back away from the anyelement||anyarray operators.
My current prototype is
create function catany(text, anyelement) returns text as
$$ select $1 || $2::text $$ language sql;
create function catany(anyelement, text) returns text as
$$ select $1::text || $2 $$ language sql;
create operator || (procedure = catany, leftarg = text, rightarg =
anyelement);
create operator || (procedure = catany, leftarg = anyelement, rightarg =
text);
which seems to mostly do the right thing. This approach would have
one nice property, namely eliminating the single biggest point of
push-back we are likely to get from removing the implicit casts to text.
I've been testing this approach more, and finding that it really
captures a bit too much: some cases that you'd prefer went to
anyelement||anyarray will be captured by the text||anyelement operator.
For example in 8.2 this is mapped to array_prepend:
regression=# select 'x'::text || array['aa','bb','cc'];
?column?
--
{x,aa,bb,cc}
(1 row)
but with the experimental code you get textcat:
catany=# select 'x'::text || array['aa','bb','cc'];
?column?
-
x{aa,bb,cc}
(1 row)
Basically the textcat operators will capture any case where the scalar
side is implicitly coercible to text, because the type resolution rules
will prefer that. There are some hacks we could make to make this less
probable (eg, declare the capturing operators as taking varchar instead
of text) but I can't find any complete solution short of changing the
resolution rules themselves. Which I'm loath to do since it might have
unexpected side-effects.
What I would like to propose is that we deprecate use of || as the
operator name for array_prepend and array_append, and invent new
recommended names for them. As I said earlier, these operators
aren't exactly concatenation in any normal sense anyway, since they
don't treat their operands symmetrically. My first thought is to
suggest using the shifting symbols:
anyelement anyarray
anyarray anyelement
but perhaps someone will have a better suggestion. If we do that, then
we have a solution for anyone whose array prepend or append operator is
unexpectedly captured by text concatenation: use the new names instead.
Now this is only going to seem like a good idea if you agree that we
should have some capturing operators like these. But if we don't,
I think we are going to get a lot of push-back from people whose
concatenations of random datatypes suddenly stopped working.
Essentially this proposal is putting the compatibility hit of tightening
the implicit cast rules onto people who are using array append/prepend
instead of people who are using concatenation without explicit casts.
I think there are a lot fewer of the former than the latter.
Comments?
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Command tags in create/drop scripts
On Jun 5, 9:19 am, [EMAIL PROTECTED] (Alvaro Herrera) wrote: Zdenek Kotala wrote: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Is this a TODO? I don't think so; there is no demand from anybody but Zdenek to remove those programs. Has it ever even come up before? Personally I found really strange to have createuser and createdb shipped by Postgres when I started using it. I just didn't complain. +1. Given the prevalence of the pg_foo convention, those names are clunky. So is initdb. I'm less creative than Zdenek, so I'd suggest simply renaming to pg_createuser and friends with the same command line options as the originals. Have the binaries check $0 and emit a warning about using the deprecated name to STDERR if called by a name that doesn't have the pg_ prefix. Default to symlinking the old names for backwards compatibility until 9.0. Andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] CREATEROLE, CREATEDB
Peter Eisentraut wrote: Is it correct that a user with CREATEROLE privilege but without CREATEDB privilege can create a user with *CREATEDB* privilege, thus bypassing his original restrictions? This sequence doesn't look right: pei=# create user foo1 createrole; CREATE ROLE pei=# \c - foo1 You are now connected to database pei as user foo1. pei= create database test; ERROR: permission denied to create database pei= create user foo2 createdb; CREATE ROLE pei= \c - foo2 You are now connected to database pei as user foo2. pei= create database test; CREATE DATABASE that's how its documented: http://www.postgresql.org/docs/8.2/interactive/sql-createrole.html Be careful with the CREATEROLE privilege. There is no concept of inheritance for the privileges of a CREATEROLE-role. That means that even if a role does not have a certain privilege but is allowed to create other roles, it can easily create another role with different privileges than its own (except for creating roles with superuser privileges). For example, if the role user has the CREATEROLE privilege but not the CREATEDB privilege, nonetheless it can create a new role with the CREATEDB privilege. Therefore, regard roles that have the CREATEROLE privilege as almost-superuser-roles. -- Chander Ganesan The Open Technology Group One Copley Parkway, Suite 210 Morrisville, NC 27560 Phone: 877-258-8987/919-463-0999 http://www.otg-nc.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] more robust log rotation
I have been trying to come up with a simple plan to make log rotation more robust, particularly in not splitting log lines across files. Greg Smith complained that lines could be split if logs are rotated on size, but AFAICS the danger also exists for time based rotation. My thought is to have the logger mark each true log line line end with a null byte. The syslogger would have a state variable called, say, safe_to_rotate, which would be set true if and only if the last thing written to the log file was a \n from the input stream which was followed by a null byte. The null bytes would of course never actually be sent to the files. Log rotation would be delayed until variable this was set true. There is a small danger that the delay in log rotation might be substantial - with a series of partial lines read. I suspect that in practice this danger would be vanishingly small, and we might reasonably expect that the condition would come true within a few cycles. An alternative scheme would involve keeping some extra buffer(s) of data so that we ensure we never write out a partial line to the file. But that seems to me to involve a lot more processing and so I'm wary of it. I don't think we should contemplate providing for CSV logs until we have this problem solved, so it's rather important. Thoughts? cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] more robust log rotation
Andrew Dunstan [EMAIL PROTECTED] writes: My thought is to have the logger mark each true log line line end with a null byte. This seems not to help much when you consider interleaved output from different backends... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] more robust log rotation
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: My thought is to have the logger mark each true log line line end with a null byte. This seems not to help much when you consider interleaved output from different backends... True. We have two problems with log robustness, and this was an attempt to address one of them. I sent some thoughts about interleaving yesterday. If you have any thoughts about handling interleaving then I'm all ears. However, my impression was that this only happened on a small number of platforms. If it's more general then it's all the more urgent to fix. ISTM that we can't really consider CSV logging until we have a handle on both problems. cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Implicit casts with generic arrays
Tom Lane wrote:
The expressions
'abc' || 34
34 || 'abc'
would no longer work, with the following error message:
ERROR: 22P02: array value must start with { or dimension information
Hm, that's annoying. Not that the expressions fail --- we want them to
--- but that the error message is so unhelpful.
indeed
In the long run maybe we should choose some other name for the
array_append and array_prepend operators to avoid the confusion with
concatenation. It seems to me that concatenation normally implies
stringing together similar objects, which these two operators
definitely don't do, and so you could argue that || was a bad name
for them from the get-go. But compatibility worries would mean we
couldn't eliminate the old names for quite a long time, so maybe
it's too late for that.
Comments?
Originally I saw this situation as as requiring the concatenation
operator per SQL 2003:
array value expression ::=
array concatenation
| array primary
array concatenation ::=
array value expression 1 concatenation operator array primary
concatenation operator ::= ||
array value expression 1 ::= array value expression
array primary ::= value expression primary
value expression primary ::=
parenthesized value expression
| nonparenthesized value expression primary
parenthesized value expression ::=
left paren value expression right paren
value expression ::=
common value expression
| boolean value expression
| row value expression
common value expression ::=
numeric value expression
| string value expression
| datetime value expression
| interval value expression
| user-defined type value expression
| reference value expression
| collection value expression
nonparenthesized value expression primary ::=
unsigned value specification
| column reference
| set function specification
| window function
| scalar subquery
| case expression
| cast specification
| field reference
| subtype treatment
| method invocation
| static method invocation
| new specification
| attribute or method reference
| reference resolution
| collection value constructor
| array element reference
| multiset element reference
| routine invocation
| next value expression
collection value constructor ::=
array value constructor
| multiset value constructor
unsigned value specification ::=
unsigned literal
| general value specification
unsigned literal ::=
unsigned numeric literal
| general literal
general literal ::=
character string literal
| national character string literal
| Unicode character string literal
| binary string literal
| datetime literal
| interval literal
| boolean literal
What I can't decide now is whether all the above means the anyelement in
this operation ought to be in parens or not. It seems to me that the
anyelement can be any literal _except_ a signed numeric literal. In
that case the spec seems to require parenthesis.
Joe
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] TOAST usage setting
Zeugswetter Andreas ADI SD wrote: The big question is do we want to drop the target tuple size down to 512, and increase the chunk size to 8k for 8.3? Dropping the tuple size down to 512 is going to give us some smaller TOAST values to fill in free space created by the 8k chuck size, assuming you have both types of values in the table. Do we want to increase the access time of long TOAST by 6% if it means having more wasted space for lots of 4.1k values? If we do that people could see their disk space usage increase by up to 16x: currently 513 bytes fits in heap and takes (roughly) 513 bytes; No, you misunderstood. Bruce was suggesting changing the target to 512. That means if a row is wider than ~2k, toaster will try to toast until the base row is ~512 bytes. I would not do that part for 8.3. OK, what do you suggest for 8.3? Attached are my suggestion to use 512 and a 4k chunk size, which I think means that 2.7k is the worst values that has a loss of around 25%. -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + Index: src/include/access/tuptoaster.h === RCS file: /cvsroot/pgsql/src/include/access/tuptoaster.h,v retrieving revision 1.35 diff -c -c -r1.35 tuptoaster.h *** src/include/access/tuptoaster.h 6 Apr 2007 04:21:43 - 1.35 --- src/include/access/tuptoaster.h 2 Jun 2007 02:52:22 - *** *** 42,48 * given to needs_toast_table() in toasting.c before unleashing random * changes. */ ! #define TOAST_TUPLES_PER_PAGE 4 /* Note: sizeof(PageHeaderData) includes the first ItemId on the page */ #define TOAST_TUPLE_THRESHOLD \ --- 42,48 * given to needs_toast_table() in toasting.c before unleashing random * changes. */ ! #define TOAST_TUPLES_PER_PAGE 16 /* Note: sizeof(PageHeaderData) includes the first ItemId on the page */ #define TOAST_TUPLE_THRESHOLD \ *** *** 69,75 * * NB: Changing TOAST_MAX_CHUNK_SIZE requires an initdb. */ ! #define EXTERN_TUPLES_PER_PAGE 4/* tweak only this */ /* Note: sizeof(PageHeaderData) includes the first ItemId on the page */ #define EXTERN_TUPLE_MAX_SIZE \ --- 69,75 * * NB: Changing TOAST_MAX_CHUNK_SIZE requires an initdb. */ ! #define EXTERN_TUPLES_PER_PAGE 2/* tweak only this */ /* Note: sizeof(PageHeaderData) includes the first ItemId on the page */ #define EXTERN_TUPLE_MAX_SIZE \ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
