Re: [HACKERS] with vs without oids in pg_catalog.*
Dear Tom, > > I notice that some tables in pg_catalog have oids, and some do not have > > them (e.g. pg_attribute, pg_group, pg_shadow...). > > That's not a bug, it's a feature. We don't use up OIDs on tables that > don't need them. Sure. I did not suggest that this is a bug! I'm sorry if it sounded so. As I'm playing quite thoroughly with pg_catalog, I bump into every inconsistency there, "historical and backwards compatibility" stuff as you named it in a previous mail. Now as I'm developping (slowly in my free time) some "pg_advisor" queries, I wish I had some way of referencing objects that I need to designate (say, an attribute, an index, a table, a constraint, and so on). So my question still is: Given the fact that I have some use for these oids, would it make sense to submit a patch to add them? Or if they are not useful within pg_catalog, then no modification will be accepted for an "external" tool? It is sure possible to circumvent the issue by putting the needed composite keys here and there, but simple plain oids would look better. One concept/one field looks nicer. Thanks in advance, -- Fabien Coelho - [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] logging statement levels
Andrew Dunstan wrote: > I wrote: > > > > > > > If nobody is working on this I am prepared to look at it: > > > > . Allow logging of only data definition(DDL), or DDL and modification > > statements > > > > > > Here are some options: > > 1. change the type of "log_statement" option from boolean to string, > with allowed values of "all, mod, ddl, none" with default "none". > 2. same as 1. but make boolean true values synonyms for "all" and > boolean false values synonyms for "none". > 3. keep "log_statement" option as now and add a new option > "log_statement_level" with the same options as 1. but default to "all", > which will have no effect unless "log_statement" is true. I like 1. > Also, I assume "modification statements" means insert/update/delete, or Yes. > are we talking about DDL mods (like "alter table")? Alter is DDL. > Finally, what about functions that have side effects? It would be nice > to be able to detect the statements to be logged at the syntactic level, > but it strikes me that that might not be possible. Not possible. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] pg_dump end comment
I like an end-of-dump marker for folks who want to check if the dump got truncated somehow. I can see how to do that for text dumps, but what about for tar or custom dumps? Wouldn't it be more effective to test for non zero return status as this handles -Fc cases, etc, which would be non-trivial to test. That assumes you are there at the time of dump... Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] pg_dump end comment
At 12:13 AM 31/03/2004, Bruce Momjian wrote: Yes, they have to check for a proper exit from pg_dump, but there is still a file sitting around after the dump, with no way to tell if it is accurate. Why don't we write a hash into the header or footer. Then use something like: pg_restore --verify if file integrity is the objective. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] psql \d option list overloaded
I have added this psql backslash discussion to TODO.detail.
---
Peter Eisentraut wrote:
> Tom Lane wrote:
> > But this interacts with point 3 (psql breaks on every new backend
> > version). It's not desirable to have every GUI and large custom
> > program implementing its own set of metadata inquiry commands: they
> > all have to go through the same update pain as psql. Perhaps if
> > people start to rely on information_schema for those things, life
> > will get better, but I'm unconvinced that will happen. psql itself
> > certainly hasn't moved in that direction.
>
> IIRC, the two killers in psql compatibility have been outer joins and
> schemas. I don't see how we could have avoided that, except with
> highly specialized and static (parameter-less) commands. There have
> been additional minor issues, but I suppose we could have avoided those
> if we had cared to do so at all.
>
> Several people have in the past proposed to keep psql backward
> compatible, even if only by means of
>
> if (version =x) {
>...
> }
> else if (version = y) {
>...
> }
>
> (which would be fine by me), but apparently no one has felt pressed
> enough yet.
>
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
>
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] cvs HEAD regression
Joe Conway <[EMAIL PROTECTED]> writes: > I've been seeing 2 regression failures (diff attached) for the past > couple of days. Both appear to be ordering related. Yeah, I'm getting that too. It seems to be a side effect of my "fuzzy cost comparison" patch. I've been trying to figure out why I did not notice it before committing. I don't normally make commits without checking the regression results --- did I miss a step, or is there something else involved? Is there anyone who is using CVS from the last day or so and does *not* see these diffs? Anyway, the new choices of plans appear to be reasonable AFAICT, so I will probably end up just changing the regression expected outputs. In the select_views case, for instance, it is now preferring a plan with cost 1.05 .. 709.74-plus-epsilon over one with cost 132.24 .. 709.74 (the total costs are now considered equal so it looks to the startup cost as tiebreaker). Seems like a win to me. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Transaction question
Thank you. At the first time, it works well. But if I try to do same command again, it still give me a server crash error. > On Tue, Mar 30, 2004 at 12:58:31PM -0500, [EMAIL PROTECTED] wrote: > >> I'm trying to insert new row in a system catalog table and then I'd like >> to retrieve this value in one command internally. I started new >> transaction for insertion operation and commited that transaction. And >> insert operation works well, but I give me server crash error. Could you >> let me know how can I handle transactions for insertion and selection >> sequentially in one command? > > I think rather than starting and committing a new transaction, what you > want to do is CommandCounterIncrement() after inserting without messing > the transaction mechanism. > > -- > Alvaro Herrera () > "The eagle never lost so much time, as > when he submitted to learn of the crow." (William Blake) > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] Update on PITR
A brief update on PITR status: I've completed successful unit testing of the PostgreSQL client-side code for the XLogArchive API. Just about to start moving on to pg_arch and the archiver side code. At this rate, I should have a system-testable set of patches in around 2 weeks time for the first phase of PITR. I'll release the code as soon as I can: I'm conscious that other work may be updating xlog code also and I'm sure the committers will want some thorough checks before acceptance. Quick update on overall plan: Phase 1: xlog archiving api, with functional pg_arch utility - ETA mid-April - changes so far to xlog.c, xlog.h, guc.c - will allow rollforward along extended archive history till-end of logs, diskspace permitting Phase 2: add code to control recovery (to a point-in-time) - should be there by mid-May, though may yield more quickly - will allow rollforward along extended archive history to point in time, diskspace permitting Phase 3: various additional tweaks, suggestions & better documentation - early June (following some time travelling in May) - will allow rollforward along extended archive history to point in time, even when logs > available disk Is this all still OK for 7.5? (My attempts at cataloguing changes has fallen by the wayside in concentrating on the more important task of PITR.) Do we have a planned freeze month yet? ...Those with a wry sense of humour may be interested to know that I recently lost my hard-drive on my main business workstation. Not uncommon, I grant you, but in the circumstances I didn't really need the extra recovery practice... Best Regards, Simon Riggs ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Transaction question
On Tue, Mar 30, 2004 at 12:58:31PM -0500, [EMAIL PROTECTED] wrote: > I'm trying to insert new row in a system catalog table and then I'd like > to retrieve this value in one command internally. I started new > transaction for insertion operation and commited that transaction. And > insert operation works well, but I give me server crash error. Could you > let me know how can I handle transactions for insertion and selection > sequentially in one command? I think rather than starting and committing a new transaction, what you want to do is CommandCounterIncrement() after inserting without messing the transaction mechanism. -- Alvaro Herrera () "The eagle never lost so much time, as when he submitted to learn of the crow." (William Blake) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] Inconsistent behavior on Array & Is Null?
Joe,
First off, pardon me if these are known things which are already fixed in CVS.
Also, let me again thank you for all the work on Arrays in 7.4; I've been
able to tremendously simplify quite a number of procedures in my databases
thanks to the new array support.
Now, the issue:
I'm noticing some inconsistent behavior regarding empty arrays and IS NULL
status.For example:
net_test=# select array_upper('{}'::INT[], 1);
array_upper
-
(1 row)
net_test=# select array_upper('{}'::INT[], 1) IS NULL;
?column?
--
t
(1 row)
net_test=# select '{}'::INT[] IS NULL;
?column?
--
f
(1 row)
I feel that this is confusing; an empty array should be considered NULL
everywhere or nowhere. For that matter, the new array declaration syntax
does not support empty arrays:
net_test=# select ARRAY[ ]::INT[];
ERROR: syntax error at or near "]" at character 15
net_test=# select ARRAY[]::INT[];
ERROR: syntax error at or near "]" at character 14
net_test=#
--
-Josh Berkus
Aglio Database Solutions
San Francisco
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
[HACKERS] cvs HEAD regression
I've been seeing 2 regression failures (diff attached) for the past couple of days. Both appear to be ordering related. Joe regression.diffs.gz Description: GNU Zip compressed data ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Better support for whole-row operations and composite
Andrew Dunstan <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Only named composite types, not RECORD, will be allowed to be used as >> table column types. > Interesting. I'm slightly curious to know if there's an external driver > for this. There's noplace to store a permanent record of an anonymous rowtype's structure. To do otherwise would amount to executing an implicit CREATE TYPE AS for the user, so we might as well just say up front that you have to create the type. > Will this apply recursively (an a has a b which has an array of c's)? Yup. > Are there indexing implications? Could one index on a subfield? Using an expression index, sure. I don't think we need to support it as a "primitive" index type. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] with vs without oids in pg_catalog.*
Fabien COELHO <[EMAIL PROTECTED]> writes: > I notice that some tables in pg_catalog have oids, and some do not have > them (e.g. pg_attribute, pg_group, pg_shadow...). That's not a bug, it's a feature. We don't use up OIDs on tables that don't need them. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] Transaction question
Hello, I'm trying to insert new row in a system catalog table and then I'd like to retrieve this value in one command internally. I started new transaction for insertion operation and commited that transaction. And insert operation works well, but I give me server crash error. Could you let me know how can I handle transactions for insertion and selection sequentially in one command? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Better support for whole-row operations and composite types
Tom, > We have a number of issues revolving around the fact that composite types > (row types) aren't first-class objects. I think it's past time to fix > that. Here are some notes about doing it. I am not sure all these ideas > are fully-baked ... comments appreciated. I'll want to add to the documentation on composite types, then. We'll need a stern warning to users not to abuse them. Easily done, I think. Composite types are frequently abused by OO and Windows programmers to break the relational model.I used to be an MSDN member (thank you, I've recovered) and frequently ran into, on the mailing list, users getting themselves into some unresolvable mess becuase they'd used composite types in SQL server to combine several rows ... or even effectively an entire child table ... into one field. Othewise, looks good to me. I don't think I'm qualified to second-guess you on the implementation. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Hash cost
Hi Could someone please try and explain why the cost estimator for the hash is implemented as it is? (cost_hashjoin in costsize.c) Especially these issues: First, there is the estimation on the number of rows and their size. ExecChooseHashTableSize() apparently trusts neither and doubles them. Thus the function estimates the input relation is 4 times larger than the rest of the optimizer thinks. Why is that? And why is this doubling also applied to the size of HashJoinTupleData? But not applied twice to the size of estimated bytes the hash would use, a number used in the calculation on the number of batches? Second, why does the optimizer first guess that there can be 10 values in a bucket and then afterwards spend a lot of time estimating this number for use in another calculation? Using numbers that was based on the guess that there can be 10 values... Third, the calculation assumes that the most common value is most dominant by far, but that the other common value mean nothing? Fourth, the hashfunction does not create any collisions between non-identical values? And multiple join qualifiers does not affect this either? Fifth, a probe most often looks in a chain with the average number of buckets? I would assume that a lot more time is spent looking in the chains with the most buckets... -- Dennis ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Dates BC.
I have applied a patch to fix the issues mentioned below. Thanks.
---
Karel Zak wrote:
> On Fri, Dec 19, 2003 at 01:12:08AM -0800, Dann Corbit wrote:
> > There is no zero calendar year. The first year of Anno Domini is 1. It's
> > ordinal, not cardinal.
>
> I agree. But the follow quoted code is not use in date_part() there
> Kurt found bug. It's used in to_timestamp() _only_, and it works,
> because tm2timestamp() and date2j() work with zero year.
>
> > > Is there connection between formatting.c and date_part() ?
> > > I don't think so...
> > >
> > > > In backend/utils/adt/formatting.c:
> > > >
> > > > if (tmfc.bc)
> > > > {
> > > > if (tm->tm_year > 0)
> > > > tm->tm_year = -(tm->tm_year - 1);
>
>
> ... "tm->tm_year = -(tm->tm_year - 1)" is used for:
>
> # select to_timestamp('0001/01/01 BC', '/MM/DD AD');
> to_timestamp
>
> 0001-01-01 00:00:00 BC
>
> and it's OK.
>
>
> I think a bug is somewhere in timestamp2tm() which used in next
> examples and it's shared between more functions:
>
> # select to_char('0001-01-01 BC'::date, '/MM/DD AD');
> to_char
> ---
> /01/01 AD
>
> # SELECT EXTRACT(YEAR from '0001-01-01 BC'::date);
> date_part
> ---
> 0
>
>
> Karel
>
>
>
> --
> Karel Zak <[EMAIL PROTECTED]>
> http://home.zf.jcu.cz/~zakkr/
>
> ---(end of broadcast)---
> TIP 3: 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
>
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [HACKERS] feature request: \qf datatype
I added a mention of how to use the pager to lookup datatype mentions in psql \df: To look up functions taking argument or returning values of a specific type, use your pager's search capability to scroll through the \df output. No one could come up with a good API to make this easier, so I think mentioning a simple solution is best. --- Alex J. Avriette wrote: > On Fri, Dec 26, 2003 at 04:07:50PM -0800, David Fetter wrote: > > > \dfT integer > > > > might bring back all the functions that operate on (or return) > > integers. > > I like this the best so far. My only concern is that there is a > whole lot of output generated by this (see thread parent). > > > > > Duplication--especially for help systems--is not a bad thing, as > > > > long as it's only duplication of access and not of code bases. > > > > > > Duplication of help systems that are never going to be used is a > > > waste of everyone's time. > > > > Perhaps I didn't make clear what I was trying to say. :) > > > > We should continue to avoid the "The docs for any given thing are > > findable in exactly one way. If you don't divine it, you are S.O.L." > > model of documentation. > > Well, I'm perfectly happy to see it in psql. I'm just unable to > actually do the C part myself. If somebody would step up to the plate, > we could see this in the next release. I haven't heard anyone say they > didn't like it. > > alex > > -- > [EMAIL PROTECTED] > Alex J. Avriette, Windows Systems Defenestrator > "Object-oriented programming is an exceptionally bad idea which could only have > originated in California." - Edsger Dijkstra > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Better support for whole-row operations and composite
Tom Lane wrote: We have a number of issues revolving around the fact that composite types (row types) aren't first-class objects. I think it's past time to fix that. Here are some notes about doing it. I am not sure all these ideas are fully-baked ... comments appreciated. [snip] Only named composite types, not RECORD, will be allowed to be used as table column types. [snip] Interesting. I'm slightly curious to know if there's an external driver for this. Will this apply recursively (an a has a b which has an array of c's)? Are there indexing implications? Could one index on a subfield? cheers andrew ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] pg_dump end comment
Gavin Sherry wrote: > On Tue, 30 Mar 2004, Bruce Momjian wrote: > > > Tom Lane wrote: > > > Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: > > > > This might seem a bit silly, but is there any chance we could add a > > > > comment at the end of pg_dump text output that says '-- End of dump'? > > > > > > Sure --- while you're at it, put a "beginning of dump" at the start. > > > > > > Is it worth adding the database name and/or other command-line > > > parameters given to pg_dump? > > > > > > One thing to think about is the difference between a bare pg_dump and a > > > pg_dump/pg_restore sequence. Should these always generate identical > > > text output? (They do as of CVS tip, I believe, though this was not > > > always true before.) > > > > > > Possibly this is all gilding the lily though... > > > > I like an end-of-dump marker for folks who want to check if the dump got > > truncated somehow. I can see how to do that for text dumps, but what > > about for tar or custom dumps? > > Wouldn't it be more effective to test for non zero return status as this > handles -Fc cases, etc, which would be non-trivial to test. Yes, they have to check for a proper exit from pg_dump, but there is still a file sitting around after the dump, with no way to tell if it is accurate. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] GIST code doesn't build on strict 64-bit machines
Ok, I just commited changes, pls, check it on HPPA. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: 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_dump end comment
Gavin Sherry <[EMAIL PROTECTED]> writes: > On Tue, 30 Mar 2004, Bruce Momjian wrote: >> I like an end-of-dump marker for folks who want to check if the dump got >> truncated somehow. I can see how to do that for text dumps, but what >> about for tar or custom dumps? > Wouldn't it be more effective to test for non zero return status as this > handles -Fc cases, etc, which would be non-trivial to test. In the tar and custom archive formats, it's fairly obvious if the file was truncated: you'll have table-of-contents entries that point past the end of file. The only case that really needs an endmarker is textual scripts --- both straight from pg_dump, and produced by pg_restore. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] with vs without oids in pg_catalog.*
Dear hackers, I'm still trying to play with pg_catalog relations. I notice that some tables in pg_catalog have oids, and some do not have them (e.g. pg_attribute, pg_group, pg_shadow...). Also convenient user-oriented views could reproduce the oid of their parent table (e.g. pg_user if pg_shadow had an oid). This situation makes referencing such elements a little bit inhomogeneous. Is there a strong backend rational behind these missing features, or would it be possible to suggest some additions in this area? I may imagine some behind the seen updates which could need to be updated, but I cannot seen any major reason why this could not and should not be done... So my question is : would it be reasonnable to submit a new "posgres.bki" version, by dropping some "without_oids" in some declarations?? -- Fabien Coelho - [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] pg_dump end comment
On Tue, 30 Mar 2004, Bruce Momjian wrote: > Tom Lane wrote: > > Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: > > > This might seem a bit silly, but is there any chance we could add a > > > comment at the end of pg_dump text output that says '-- End of dump'? > > > > Sure --- while you're at it, put a "beginning of dump" at the start. > > > > Is it worth adding the database name and/or other command-line > > parameters given to pg_dump? > > > > One thing to think about is the difference between a bare pg_dump and a > > pg_dump/pg_restore sequence. Should these always generate identical > > text output? (They do as of CVS tip, I believe, though this was not > > always true before.) > > > > Possibly this is all gilding the lily though... > > I like an end-of-dump marker for folks who want to check if the dump got > truncated somehow. I can see how to do that for text dumps, but what > about for tar or custom dumps? Wouldn't it be more effective to test for non zero return status as this handles -Fc cases, etc, which would be non-trivial to test. Gavin ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
