Re: [HACKERS] [COMMITTERS] pgsql: Create a GUC parametertemp_tablespacesthatallows selection of
On 6/5/07, Bruce Momjian <[EMAIL PROTECTED]> wrote: Simon Riggs wrote: > > Assuming, that is, that you think this point is important enough to > > drive the whole design; which I find rather questionable in view of the > > fact that the submitted patch contained no mention whatever of any such > > consideration. Or is this just another way in which its documentation > > was not up to snuff? > > Well, it was listed in the TODO, but I guess that was lost somewhere > along the line. Oh well. The TODO description was removed once the item was complete because sometimes the description doesn't match the implementation. The description was: It could start with a random tablespace from a supplied list and cycle through the list. that is what the patch did but it did it one tablespace per BufFile (not per file) of course, it parses the GUC on every GetTempTablespaces() call :( -- regards, Jaime Casanova "Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning." Richard Cook ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] [HACHERS] privilege check: column level only?
Hello I'm one of the Google SoC's students for PostgreSQL. While reading sql92 standard, I found something like this: 11.36 General Rules 3) For every identified privilege descriptor whose action is SELECT, INSERT, UPDATE, or REFERENCES without a column name, privilege descriptors are also created for each column C in O for which A holds the corresponding privilege with grant op- tion. For each such column, a privilege descriptor is created that specifies the identical , the identical , object C, and grantor A. 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? Here is some examples. (1) CREATE TABLE t1 (c1 int, c2 int); GRANT SELECT ON t1 TO grantee; REVOKE SELECT ON t1 (c1) FROM grantee; Now grantee has privilege on t1(c2) but NOT on t1(c1). Although grantee has privilege on t1, he still has no privilege on t1(c1). So checking column privilege is enough. We don't need to check table privilege. (2) CREATE TABLE t1 (c1 int, c2 int); REVOKE SELECT ON t1 FROM grantee; GRANT SELECT ON t1(c2) TO grantee; Here, still, grantee has privilege on t1(c2) but NOT on t1(c1). (Is this right?) Although grantee has no privilege on t1, he can has privilege on t1(c1). Here, again, checking column privilege is enough. Table privilege is useful when you add columns to a table. Whether grantee has privilege on the new columns depends on whether he has privilege on the table. Any and all help and/or comment is appreciated. From sql standard, I found no information on how privilege check should be done. Thanks. Dong -- Guodong Liu Database Lab, School of EECS, Peking University Room 314, Building 42, Peking University, Beijing, 100871, China
Re: [HACKERS] [COMMITTERS] pgsql: Create a GUC parametertemp_tablespacesthatallows selection of
Simon Riggs wrote: > > Assuming, that is, that you think this point is important enough to > > drive the whole design; which I find rather questionable in view of the > > fact that the submitted patch contained no mention whatever of any such > > consideration. Or is this just another way in which its documentation > > was not up to snuff? > > Well, it was listed in the TODO, but I guess that was lost somewhere > along the line. Oh well. The TODO description was removed once the item was complete because sometimes the description doesn't match the implementation. The description was: It could start with a random tablespace from a supplied list and cycle through the list. -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] TOAST usage setting
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > On Fri, Jun 01, 2007 at 01:50:12PM -0400, Bruce Momjian 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? > 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; if > we make that change it would then get toasted and take 8K. That argument is completely bogus --- having a toast chunk size of 8K does not mean that smaller rows occupy 8K. 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] Implicit casts with generic arrays
Gregory Stark <[EMAIL PROTECTED]> writes:
> "Tom Lane" <[EMAIL PROTECTED]> writes:
>> we should do is make oper() specifically test for the case of operator
>> 349 with UNKNOWN left input, or operator 374 with UNKNOWN right input,
>> and throw a custom error message hinting that the other operand
>> needs to be cast to text.
> Wouldn't that mean that 'foo'||'bar' would *still* fail?
No, because that would preferentially match to text || text, it being
a preferred-type case. The current behavior with the implicit casts
removed is
template1=# select 'abc' || '34';
?column?
--
abc34
(1 row)
ie, this was matched to the text || text operator;
template1=# select 'abc' || 34;
ERROR: array value must start with "{" or dimension information
ie, this was matched to the anyarray || anyelement operator --- because
it clearly can't match text || text.
> It really seems to me that at some point down the line we're going to
> cave and admit that users do expect 'foo' to be a string first and
> cast to other types only if the context requires it.
We already do that to some extent, as shown above; and it's got
approximately nothing to do with this problem anyway. The cases where
we have got a problem are where the other argument is clearly *not*
text.
But having said that, I'm currently leaning to the other solution of
generalizing the || operator (and only that operator) instead of
fooling with the type resolution rules.
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Command tags in create/drop scripts
It's always seemed a little odd to me that Postgres should install a command called "createuser" or "createlang", because it's entirely non-obvious on first examination that these commands (which often live in /usr/bin) have any connections with PostgreSQL. Shouldn't there be at least be a "pg" in the name somewhere? ...Robert -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane Sent: Monday, June 04, 2007 10:31 AM To: Bruce Momjian Cc: Zdenek Kotala; Andrew Dunstan; Peter Eisentraut; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Command tags in create/drop scripts 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? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(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] Performance regression on CVS head
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. 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. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Implicit casts with generic arrays
I wrote: > It looks to me like we have a very narrow problem and > we should tailor a very narrow solution. What I am currently thinking > we should do is make oper() specifically test for the case of operator > 349 with UNKNOWN left input, or operator 374 with UNKNOWN right input, > and throw a custom error message hinting that the other operand > needs to be cast to text. 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 have no doubt that practically the only reasonable use-case for that behavior was to let people concatenate stuff without being too picky about casts, and this mostly preserves that ability. It's not perfect, because it only fixes cases in which at least one operand is either unknown or implicitly coercible to text. But in practice I think that would cover 99% of cases, since typical usages tend to alternate literals and data values. Thoughts? Is this too klugy for words? 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] syslogger line-end processing infelicity
Tom Lane wrote: Andrew Dunstan <[EMAIL PROTECTED]> writes: Tom Lane wrote: +1 on that. The problem of ensuring atomic output remains though (see nearby complaints from George Pavlov and others). Is that the one you suggested trying to fix by calling write() instead of fprintf()? If so, I can't think of any good reason not to do that anyway. Probably not, but it doesn't fix the problem for long log lines (more than PIPE_BUF bytes). The other little problem (which is the reason we like the stderr approach in the first place) is that not all the stderr output we want to capture comes from code under our control. This may not be a huge problem in production situations, since the main issue in my experience is being able to capture dynamic-linker messages when shlib loading fails. But it is a stumbling block in the way of any proposals that involve having a more structured protocol for the stuff going down the wire :-( I have been trying to think of how we can get around the problem of multiplexing our own output inappropriately. I have no great insights, but I did think of these: . use one pipe per backend instead of one per postmaster, and have the syslogger poll them all. . use a mutex to control access to the pipe . same as previous but use a worker thread for each backend to do logging so blocking on the mutex wouldn't block the backend All of these look like a lot of work for a relatively infrequent problem, not to mention plenty of other disadvantages. cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] TOAST usage setting
On Fri, Jun 01, 2007 at 01:50:12PM -0400, Bruce Momjian wrote: > I think the long-term solution is to go to a 2k/8k fragment/block model, > but that isn't going to happen for 8.3. There might well have been lessons learned since UFS (anyone know what ZFS does in this regard?), but I agree that we want to be able to do a mix of full chunks and fragments. > 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; 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. How big is the hit for setting both to 512? Also, is this something that could be set at initdb instead of compile time? That would make it easier for folks to go back to old behavior if the needed to... -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgpzcxY6b7mU2.pgp Description: PGP signature
Re: [HACKERS] Implicit casts with generic arrays
"Tom Lane" <[EMAIL PROTECTED]> writes: > So after reflecting on all that, it doesn't seem like a good idea to > hack the type-coercion code to discriminate against matching unknown > to anyarray. It looks to me like we have a very narrow problem and > we should tailor a very narrow solution. What I am currently thinking > we should do is make oper() specifically test for the case of operator > 349 with UNKNOWN left input, or operator 374 with UNKNOWN right input, > and throw a custom error message hinting that the other operand > needs to be cast to text. Wouldn't that mean that 'foo'||'bar' would *still* fail? It really seems to me that at some point down the line we're going to cave and admit that users do expect 'foo' to be a string first and cast to other types only if the context requires it. That would mean we should be considering matching "unknown" as text first without casting and only if that fails looking for other types. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Performance regression on CVS head
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: http://community.enterprisedb.com/oldestxmin/ Vacuum on the stock table is started right after the rampup, and the drop in performance happens at the very moment that the vacuum finishes. Anyone have an explanation for this? One theory is that after VACUUM has populated the FSM, all updates need to do one extra I/O to read in a page with free space to insert to, instead of just extending the relation. But I don't think anything has changed recently in that area. Another theory is that the VACUUM updates some stats, which changes the access plan used to a much worse one. But the tables have been analyzed before the test, and again I don't remember any changes to that recently. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Implicit casts with generic arrays
Awhile back, I wrote:
> Peter Eisentraut <[EMAIL PROTECTED]> writes:
>> I've looked into cutting back on the implicit casts to text, which
>> exposed the following little gem.
>> 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.
I've looked into this more closely. The problem basically is that the
parser sees these alternatives for binary || operators:
select oid,oid::regoperator,oprcode from pg_operator where oprname = '||';
oid | oid |oprcode
--+-+---
349 | ||(anyarray,anyelement) | array_append
374 | ||(anyelement,anyarray) | array_prepend
375 | ||(anyarray,anyarray) | array_cat
654 | ||(text,text) | textcat
1797 | ||(bit,bit) | bitcat
2018 | ||(bytea,bytea) | byteacat
(6 rows)
If there is no implicit cast from int to text, then operator 349 is the
*only* candidate that is not immediately eliminated by the lack of any
way to cast an integer 34 to its right argument type. So as far as the
parser is concerned there is no ambiguity. If we hack things to prevent
matching unknown to anyarray, as was suggested in the previous
discussion, we'll get "operator does not exist: "unknown" || integer".
Which is better than the 22P02 error, but still not great.
It furthermore seems that the two operators anyarray || anyelement and
anyelement || anyarray are really the only cases where an undesirable
match to anyarray might occur. The other operators that take anyarray
take it on both sides, which means that they'd not be preferred unless
the other operand was discernibly an array. I don't think we want a
solution that causes "knownarraycolumn = '{1,2,3}'" to start failing.
That argument is even more compelling on the function side, because
for instance there isn't a lot of doubt about the user's intent if he
writes "array_append('{1,2,3}', 34)".
So after reflecting on all that, it doesn't seem like a good idea to
hack the type-coercion code to discriminate against matching unknown
to anyarray. It looks to me like we have a very narrow problem and
we should tailor a very narrow solution. What I am currently thinking
we should do is make oper() specifically test for the case of operator
349 with UNKNOWN left input, or operator 374 with UNKNOWN right input,
and throw a custom error message hinting that the other operand
needs to be cast to text.
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?
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] [COMMITTERS] pgsql: Create a GUC parametertemp_tablespacesthatallows selection of
On Mon, 2007-06-04 at 15:34 -0400, Tom Lane wrote: > "Simon Riggs" <[EMAIL PROTECTED]> writes: > > On Mon, 2007-06-04 at 14:41 -0400, Tom Lane wrote: > >> "Simon Riggs" <[EMAIL PROTECTED]> writes: > >>> The original ideal implementation was to use round-robin/cyclic > >>> selection, which allows much better usage in the above case. > >> > >> Really? What if multiple backends are all hitting the same tablespaces > >> in the same order? A random selection seems much less likely to risk > >> having any self-synchronizing behavior. > > > I'd like a single backend to never reuse a temp tablespace that is > > actively being used so that large queries won't randomly conflict with > > themselves. That's pretty certain to draw complaints, IMHO. > > > We can do this two ways > > - cycle thru temp tablespaces, as originally suggested (not by me...) > > - pick a random tablespace **other than ones already in active use** > > Idea 2 fails as soon as you have more temp files than tablespaces, and > also requires tracking which tablespaces are currently in use, a bit of > complexity we do not have in there. > > 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. Works for me. > 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. No problem with that. > And the reason I'm thinking a cache is important is that if you really > want to get any win from this idea, you need to spread the temp files > across tablespaces *per file*, which is not the way it works now. > As committed, the code selects one temp tablespace per sort or hashjoin. > The submitted patch already did it that way for sorts, and I forced the > same for hashjoins, because I wanted to be sure to minimize the number > of executions of aforesaid parsing/checking. So really that patch is > entirely wrong, and selection of the tablespace for a temp file needs > to be pushed much further down. Well, I was looking to achieve poor man's parallelism. If you have a query with two or more temp files active then you will be reading from one while writing to another. That could then allow you to rely on OS file writers to give you asynch I/O like behaviour. I can see what you're thinking though and it sounds even better, but I'm guessing that's a much larger change anyway. > Assuming, that is, that you think this point is important enough to > drive the whole design; which I find rather questionable in view of the > fact that the submitted patch contained no mention whatever of any such > consideration. Or is this just another way in which its documentation > was not up to snuff? Well, it was listed in the TODO, but I guess that was lost somewhere along the line. Oh well. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [COMMITTERS] pgsql: Create a GUC parametertemp_tablespacesthat allows selection of
"Simon Riggs" <[EMAIL PROTECTED]> writes: > On Mon, 2007-06-04 at 14:41 -0400, Tom Lane wrote: >> "Simon Riggs" <[EMAIL PROTECTED]> writes: >>> The original ideal implementation was to use round-robin/cyclic >>> selection, which allows much better usage in the above case. >> >> Really? What if multiple backends are all hitting the same tablespaces >> in the same order? A random selection seems much less likely to risk >> having any self-synchronizing behavior. > I'd like a single backend to never reuse a temp tablespace that is > actively being used so that large queries won't randomly conflict with > themselves. That's pretty certain to draw complaints, IMHO. > We can do this two ways > - cycle thru temp tablespaces, as originally suggested (not by me...) > - pick a random tablespace **other than ones already in active use** Idea 2 fails as soon as you have more temp files than tablespaces, and also requires tracking which tablespaces are currently in use, a bit of complexity we do not have in there. 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. 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. And the reason I'm thinking a cache is important is that if you really want to get any win from this idea, you need to spread the temp files across tablespaces *per file*, which is not the way it works now. As committed, the code selects one temp tablespace per sort or hashjoin. The submitted patch already did it that way for sorts, and I forced the same for hashjoins, because I wanted to be sure to minimize the number of executions of aforesaid parsing/checking. So really that patch is entirely wrong, and selection of the tablespace for a temp file needs to be pushed much further down. Assuming, that is, that you think this point is important enough to drive the whole design; which I find rather questionable in view of the fact that the submitted patch contained no mention whatever of any such consideration. Or is this just another way in which its documentation was not up to snuff? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [COMMITTERS] pgsql: Create a GUC parametertemp_tablespacesthat allows selection of
On Mon, 2007-06-04 at 14:41 -0400, Tom Lane wrote: > "Simon Riggs" <[EMAIL PROTECTED]> writes: > > One of the main reasons for the implementation was to allow larger > > queries to work faster by utilising multiple temp tablespaces for the > > same query. > > > The original ideal implementation was to use round-robin/cyclic > > selection, which allows much better usage in the above case. > > Really? What if multiple backends are all hitting the same tablespaces > in the same order? A random selection seems much less likely to risk > having any self-synchronizing behavior. I'd like a single backend to never reuse a temp tablespace that is actively being used so that large queries won't randomly conflict with themselves. That's pretty certain to draw complaints, IMHO. We can do this two ways - cycle thru temp tablespaces, as originally suggested (not by me...) - pick a random tablespace **other than ones already in active use** -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(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] recovery_target_xid & crashes on the master
On Mon, 2007-06-04 at 18:26 +0200, Florian G. Pflug wrote: > The function recoveryStopsHere in xlog.c checks if we should > stop recovery due to the values of recovery_target_xid and > recovery_target_time. For recovery_target_xid, we stop if > we see a commit or abort record for the given xid. > > Now I wonder what happens if an (admittely rather confused) DBA > uses an xid of a transaction that was aborted because of a > crash of the master as recovery_target_xid. The way I read the > code, postgres will just recover until it reaches the end of > the xlog in that case because neither an COMMIT nor an ABORT > for that xid exists in the WAL. > > I'm not sure if this is worth fixing - it seems like a rather > contrived corner case - but I though I'd bring it up... Currently use of recovery_target_xid overrides recovery_target_time because the first one is exact. It would be possible to have *both*, so you could set one as a backstop for the other. But you wouldn't do that unless you thought the xid might be wrong, in which case why are you using it? There's nothing to stop you specifying a stop time after the crash time either, in which case we just go to end of logs. So I'd say no change required, this time. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] libpq and Binary Data Formats
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. On 6/5/07, Richard Huxton <[EMAIL PROTECTED]> wrote: Wilhansen Li 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. Um - speaking as a user, not a developer, I don't actually see a description of what problem(s) you are suggesting be solved. Are you saying there should be better documentation, or a new format? -- Richard Huxton Archonet Ltd -- (<_<)(>_>)(>_<)(<.<)(>.>)(>.<) Life is too short for dial-up.
Re: [HACKERS] So, why isn't *every* buildfarm member failing ecpg right now?
On Mon, 4 Jun 2007, Andrew Dunstan wrote: turnip_moth is also a Solaris 9 box and doesn't seem have the same issue. Kris, is there anything unusual installed on the box that would make it behave like this? Not sure what's going on here. I did a manual run of the ecpg tests and it completed normally. This machine is quite out of date and it has a large mix of GNU tools with the solaris ones. Since turnip_moth is maintained by Sun I would expect it to be up to date on patches and have few non-Sun tools installed. So it could be using a different interpreter or it could be using a broken tool for which a patch has been released. I'll try doing a run with the buildfarm client later today to try and reproduce this. Kris Jurka ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] recovery_target_xid & crashes on the master
Hi I'm currently working on splitting StartupXLog into smaller parts, because I need to reuse some of the parts for concurrent wal recovery (for my GSoC project) The function recoveryStopsHere in xlog.c checks if we should stop recovery due to the values of recovery_target_xid and recovery_target_time. For recovery_target_xid, we stop if we see a commit or abort record for the given xid. Now I wonder what happens if an (admittely rather confused) DBA uses an xid of a transaction that was aborted because of a crash of the master as recovery_target_xid. The way I read the code, postgres will just recover until it reaches the end of the xlog in that case because neither an COMMIT nor an ABORT for that xid exists in the WAL. I'm not sure if this is worth fixing - it seems like a rather contrived corner case - but I though I'd bring it up... greetings, Florian Pflug ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] libpq and Binary Data Formats
Wilhansen Li 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. Um - speaking as a user, not a developer, I don't actually see a description of what problem(s) you are suggesting be solved. Are you saying there should be better documentation, or a new format? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] libpq and Binary Data Formats
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.
Best wishes,
Wil
NOTES/History of Posts:
1: "Query regarding PostgreSQL date/time binary format for libpq" <
http://archives.postgresql.org/pgsql-interfaces/2007-01/msg00040.php> One of
the many (clueless) individuals who wants to get the binary format of the
date/time struct (I know that there's a way to do this be converting the
time to epoch using extract(epoch from time) to convert it to somthing akin
to time_t)
2. "Bytea network traffic: binary vs text result format" <
http://archives.postgresql.org/pgsql-interfaces/2007-06/msg0.php> One of
the many Binary vs. Text debates.
3. "How do you convert PostgreSQL internal binary field to C datatypes" <
http://archives.postgresql.org/pgsql-interfaces/2007-05/msg00046.php> An
individual disgruntled because of the "half baked C API" of PostgreSQL.
Although he may be wrong in some or many aspects, he has a point with
regards to the binary format support. Moreover, he is probably one of the
many individuals who are disappointed on PostgreSQL because of this.
4. "Array handling in libpq" <
http://archives.postgresql.org/pgsql-interfaces/2007-01/msg00027.php> One of
the common scenarios for the "need" of a binary format (or rather, a better
interface): arrays. Also, the reply of this is one of the many/redundant
assurances that the overhead of text is minimal.
5. "libpq PQexecParams and arrays" <
http://archives.postgresql.org/pgsql-interfaces/2006-06/msg8.php>
Another one of those array issues. This time, the poster/s have expressed
that the documentation for binary formats is "poorly documented :-("
6. "PQgetvalue failed to return column value for non-text data in binary
format" <
http://archives.postgresql.org/pgsql-interfaces/2007-05/msg00045.php>
Another issue about binary formats paired with the assurance (again) that
the overhead of using text is minimal.
--
(<_<)(>_>)(>_<)(<.<)(>.>)(>.<)
Life is too short for dial-up.
Re: [HACKERS] So, why isn't *every* buildfarm member failing ecpg right now?
Michael Meskes wrote: On Mon, Jun 04, 2007 at 03:30:07AM -0400, Tom Lane wrote: AFAICS, Peter's recent incomplete updating of error message wording should have broken every last man jack of 'em. And yet there's still some green to be seen. I think we are looking at problems in the ecpg test scaffolding. For instance, dragonfly claims a green build, but http://www.pgbuildfarm.org/cgi-bin/show_stage_log.pl?nm=dragonfly&dt=2007-06-04%20043001&stg=ecpg-check shows this interesting trace: ... testing connect/test2.pgc ... ./pg_regress: bad substitution Is it possible to find out which line is reporting this error? Looks to me like some incompatibility on the shell side. turnip_moth is also a Solaris 9 box and doesn't seem have the same issue. Kris, is there anything unusual installed on the box that would make it behave like this? cheers andrew ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Running all tests by default
Peter Eisentraut wrote: What happened to the idea to run all tests (including PL, ECPG, contrib(?)) by default, that is, by the top-level check/installcheck targets. Those who want to run individual tests could still do so in the respective subdirectories. What requirements does the buildfarm have? I just realised that the answer to this last question is essentially "none". The reason is that as luck would have it, buildfarm never runs the top level check and installcheck targets - it runs them from the src/test/regress directory. There will be changes needed in the MSVC instructure - we'd need one or two new targets for the vcregress script (checkall/installcheckall or some such). However, to be sane we would also have to have the top-level default make target build contrib (which I badly want to rename anyway), and that will require a buildfarm change. Coordinating it won't be easy. I suggest we plan on making changes exactly at the time that we branch 8.3. I can build in conditional logic based on the version and get an amended script distributed ahead of time. If we don't want to coordinate it with that I could also do things based on catversion or even a preplanned timestamp. I'd prefer not to make these changes before we branch, though, as there's a moderately high risk of buildfarm breakage that we don't need right now. cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Autovacuum launcher doesn't notice death of postmaster immediately
Peter Eisentraut wrote: > I notice that in 8.3, when I kill the postmaster process with SIGKILL or > SIGSEGV, the child processes writer and stats collector go away > immediately, but the autovacuum launcher hangs around for up to a > minute. (I suppose this has to do with the periodic wakeups?). When > you try to restart the postmaster before that it fails with a complaint > that someone is still attached to the shared memory segment. > > These are obviously not normal modes of operation, but I fear that this > could cause some problems with people's control scripts of the > sort, "it crashed, let's try to restart it". The launcher is set up to wake up in autovacuum_naptime seconds at most. So if the user configures a ridiculuos time (for example 86400 seconds, which I've seen) then the launcher would not detect the postmaster death for a very long time, which is probably bad. (You measured a one minute delay because that's the default naptime). Maybe this is not such a hot idea, and we should wake the launcher up every 10 seconds (or less?). I picked 10 seconds because that's the time the bgwriter sleeps if there is no activity configured. Does this sound acceptable? The only problem with waking it up too frequently is that it would be waking the system up (for gettimeofday()) even if nothing is happening. I also just noticed that the launcher will check if postmaster is alive, then sleep, and then possibly do some work. So if the postmaster died in the sleep period, the launcher might try to do some work. Should we add a check for postmaster liveliness after the sleep? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] So, why isn't *every* buildfarm member failing ecpg right now?
On Mon, Jun 04, 2007 at 03:30:07AM -0400, Tom Lane wrote: > AFAICS, Peter's recent incomplete updating of error message wording > should have broken every last man jack of 'em. And yet there's still > some green to be seen. I think we are looking at problems in the ecpg > test scaffolding. For instance, dragonfly claims a green build, but > http://www.pgbuildfarm.org/cgi-bin/show_stage_log.pl?nm=dragonfly&dt=2007-06-04%20043001&stg=ecpg-check > shows this interesting trace: > ... > testing connect/test2.pgc ... ./pg_regress: bad > substitution Is it possible to find out which line is reporting this error? Looks to me like some incompatibility on the shell side. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(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? No. Agreed. -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Command tags in create/drop scripts
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? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] So, why isn't *every* buildfarm member failing ecpg right now?
Tom Lane wrote: AFAICS, Peter's recent incomplete updating of error message wording should have broken every last man jack of 'em. And yet there's still some green to be seen. I think we are looking at problems in the ecpg test scaffolding. Yes. The buildfarm script uses the same logic as other tests: my $ecpg_dir = "$pgsql/src/interfaces/ecpg"; my @makeout = `cd $ecpg_dir && $make NO_LOCALE=1 check 2>&1`; my $status = $? >>8; The sooner we move to a more unified testing infrastructure the better. cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Command tags in create/drop scripts
Is this a TODO? --- Zdenek Kotala wrote: > Tom Lane napsal(a): > > Andrew Dunstan <[EMAIL PROTECTED]> writes: > >> Zdenek Kotala wrote: > > And what about replace all "scripts" by one command e.g pg_cmd with > > following interface: > > > >> Well, I don't think rolling up the miscellaneous commands into a single > >> binary with behaviour dependent on arg[0] is a bad idea. > > > > I don't think that responds to Zdenek's complaint though. He's unhappy > > about polluting /usr/bin with commands like "createuser" --- which is > > not an unreasonable gripe. AFAICS it doesn't help if "createuser" > > is a link to a single executable rather than a file of its own. > > Yes, It was one idea (after midnight :-) how to keep backward > compatibility for next few releases, but better solution is keep old > binaries for couple of release cycles. > > > But in the light of backwards-compatibility requirements, I can't see us > > removing "createuser" from the distribution ... not in 8.4, nor several > > releases thereafter. > > I agree, I only want to start discussion about new command which will > replace old binaries. Old binaries will be marked as obsolete and they > will be removed e.g. in 9.0. Until we do not have the new command we can > not start thinking about remove old one. I think we can have new command > ready for 8.4. > > > Zdenek > > > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] ERROR: index row size
>but I'll bet >a nickel your CREATE TYPE says something else --- probably varlena You win, how can I pay this bet? :) Very very thanks, I was looking for the error in the wrong place! It was so simple and works! But still have another problem, may be related with my output functions!?!? I still get an error while doing a select with order by clause! Any other good bet? The error is: ERROR: cache lookup failed for operator.. Thanks in advance! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Tsearch vs Snowball, or what's a source file?
This makes no difference in terms of the ease of tracking their changes, of course, but it just feels better to me to be distributing "real" source code and not derived files. Hmm. 1 Compiling from .sbl by original Snowball's makefile requires Perl and doesn't work cleanly: % gmake cc -o snowball compiler/space.o compiler/tokeniser.o compiler/analyser.o compiler/generator.o compiler/driver.o compiler/generator_java.o gmake: *** No rule to make target `libstemmer/libstemmer_c.in', needed by `libstemmer/libstemmer.c'. Stop. I used http://snowball.tartarus.org/dist/snowball_code.tgz tarball. 2 Snowball's compiling infrastructure doesn't support Windows target. I agree with simplify support process but, IMHO, it's much simpler to do it with C sources with pgsql's building infrastructure And where should it be placed? src/snowball directory? -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Constraint exclusion oddity with composite index
> >> Assume the following: > >> index on: (id, adate) > >> constraint CHECK(adate > '01-01-2007' AND adate < '04-01-2007'); > > Um, the subject is CE, but the question is about an index ? Those are separate issues. > >> The planner will not use the index listed above. > > For what? > > select adate from parent where adate = '01-25-2007' A possibly cheaper plan would be a self join to produce all possible id's and join the index for each (id, adate) pair. Note, that you need not check visibility of the id's you produce (index only access). Is that what you were expecting ? This is not implemented. Andreas ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Command tags in create/drop scripts
Tom Lane napsal(a): Andrew Dunstan <[EMAIL PROTECTED]> writes: Zdenek Kotala wrote: And what about replace all "scripts" by one command e.g pg_cmd with following interface: Well, I don't think rolling up the miscellaneous commands into a single binary with behaviour dependent on arg[0] is a bad idea. I don't think that responds to Zdenek's complaint though. He's unhappy about polluting /usr/bin with commands like "createuser" --- which is not an unreasonable gripe. AFAICS it doesn't help if "createuser" is a link to a single executable rather than a file of its own. Yes, It was one idea (after midnight :-) how to keep backward compatibility for next few releases, but better solution is keep old binaries for couple of release cycles. But in the light of backwards-compatibility requirements, I can't see us removing "createuser" from the distribution ... not in 8.4, nor several releases thereafter. I agree, I only want to start discussion about new command which will replace old binaries. Old binaries will be marked as obsolete and they will be removed e.g. in 9.0. Until we do not have the new command we can not start thinking about remove old one. I think we can have new command ready for 8.4. Zdenek ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [pgsql-advocacy] Upcoming events
FYI, I am attending events in Italy, California, Pennsylvania, Finland, and Russia in the next few months: http://momjian.us/main/events.html Site of russian conference: http://www.highload.ru (sorry, but only russian info yet) Bruce's announcement: http://www.highload.ru/news/2824.html -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(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] What is happening on buildfarm member baiji?
> > > Given this, I propose we simply #ifdef out the SO_REUSEADDR on win32. I agree, that this is what we should do. > > > (A fairly good reference to read up on the options is at > > > http://msdn2.microsoft.com/en-us/library/ms740621.aspx > > > > Hmm ... if accurate, that page says in words barely longer than one > > syllable that Microsoft entirely misunderstands the intended meaning > > of SO_REUSEADDR. > > Yes, that's how I read it as well. > > > It looks like SO_EXCLUSIVEADDRUSE might be a bit closer to the > > standard semantics; should we use that instead on Windoze? > > I think you're reading something wrong. The way I read it, > SO_EXCLUSIVEADDRUSE gives us pretty much the same behavior we have on Unix > *without* SO_REUSEADDR. There's a paragraph specificallyi > talking about the problem of restarting a server having to > wait for a timeout when using this switch. Yup, that switch is no good eighter. Andreas ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] What is happening on buildfarm member baiji?
On Sun, Jun 03, 2007 at 10:44:13PM -0400, Tom Lane wrote: > Andrew Dunstan <[EMAIL PROTECTED]> writes: > > Magnus Hagander wrote: > >> Given this, I propose we simply #ifdef out the SO_REUSEADDR on win32. > >> Anybody see a problem with this? > > > Is that true even if the backend crashes? > > It would take a postmaster crash to make this an issue, and those are > pretty doggone rare. Not that the question shouldn't be checked, but > we might decide to tolerate the problem if there is one ... The closest I can get is a kill -9 on postmaster, and that does work. I can't start a new postmaster while the old backend is running - because of the shared memory detection stuff. But the second it's gone I can start a new one, so it doesn't have that wait-until-timeout behavior. Since that's expected behavior and there were no other complaints, I think I'll go ahead an put this one in later today. //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Running all tests by default
What happened to the idea to run all tests (including PL, ECPG, contrib(?)) by default, that is, by the top-level check/installcheck targets. Those who want to run individual tests could still do so in the respective subdirectories. What requirements does the buildfarm have? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] What is happening on buildfarm member baiji?
On Sun, Jun 03, 2007 at 11:29:33PM -0400, Tom Lane wrote: > Magnus Hagander <[EMAIL PROTECTED]> writes: > > Given this, I propose we simply #ifdef out the SO_REUSEADDR on win32. > > Anybody see a problem with this? > > > (A fairly good reference to read up on the options is at > > http://msdn2.microsoft.com/en-us/library/ms740621.aspx > > Hmm ... if accurate, that page says in words barely longer than one > syllable that Microsoft entirely misunderstands the intended meaning > of SO_REUSEADDR. Yes, that's how I read it as well. > It looks like SO_EXCLUSIVEADDRUSE might be a bit closer to the standard > semantics; should we use that instead on Windoze? I think you're reading something wrong. The way I read it, SO_EXCLUSIVEADDRUSE gives us pretty much the same behavior we have on Unix *without* SO_REUSEADDR. There's a paragraph specificallyi talking about the problem of restarting a server having to wait for a timeout when using this switch. //Magnus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] So, why isn't *every* buildfarm member failing ecpg right now?
AFAICS, Peter's recent incomplete updating of error message wording should have broken every last man jack of 'em. And yet there's still some green to be seen. I think we are looking at problems in the ecpg test scaffolding. For instance, dragonfly claims a green build, but http://www.pgbuildfarm.org/cgi-bin/show_stage_log.pl?nm=dragonfly&dt=2007-06-04%20043001&stg=ecpg-check shows this interesting trace: == running regression test queries== /export/home/pgfarm/gcc/HEAD/pgsql.28740/src/interfaces/ecpg/test/./tmp_check/install//export/home/pgfarm/gcc/HEAD/inst/bin/createuser -R -S -D -q regressuser1 /export/home/pgfarm/gcc/HEAD/pgsql.28740/src/interfaces/ecpg/test/./tmp_check/install//export/home/pgfarm/gcc/HEAD/inst/bin/createuser -R -S -D -q connectuser /export/home/pgfarm/gcc/HEAD/pgsql.28740/src/interfaces/ecpg/test/./tmp_check/install//export/home/pgfarm/gcc/HEAD/inst/bin/createuser -R -S -D -q connectdb testing connect/test1.pgc ... skipped testing connect/test2.pgc ... ./pg_regress: bad substitution make[1]: Leaving directory `/export/home/pgfarm/gcc/HEAD/pgsql.28740/src/interfaces/ecpg/test' which says that (a) we have a scripting bug, and (b) some other layer is failing to notice the failure. Which is two separate bugs by my count. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
