AW: [HACKERS] New SQL Datatype RECURRINGCHAR
> My feeling is that the restrictions are stringent enough to eliminate > most of the interesting uses of views, and hence an automatic rule > creation feature is not nearly as useful/important as it appears at > first glance. The most prominent of the "interesting uses" probably beeing when the views are part of the authorization system, since views are the only standardized mechanism to restrict access at the row level. Imho not to be neglected. (user xxx is only allowed to manipulate rows that belong to his department, so he is only granted access to a view, not the main table) Andreas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] grant and SQL92
On Mon, 9 Jul 2001, Bruce Momjian wrote: > > On Sat, 9 Jun 2001, Peter Eisentraut wrote: > > > > > Vince Vielhaber writes: > > > > > > > I can grant a series of privileges (comma separated) on a series of > > > > objects (comma separated) to either a user, group or public NOT a > > > > comma separated list of users or groups. > > > > > > I should have this finished today to tomorrow. > > > > I looked at it but it looked too much like it involved gram.y which I'm > > going to happily stay away from for now :) > > Added to TODO: > > * Allow GRANT/REVOKE to handle multiple user/group names I'm guessing Peter got sidetracked? If it's still open after I submit a create user patch (with the help of Tom Lane I'm no longer uncomfortable with gram.y) I'll take a look at this. Vince. -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directoryhttp://www.camping-usa.com Online Giftshop Superstorehttp://www.cloudninegifts.com == ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] Improving pg_hba.conf
> We have the following item on TODO: > > * Overhaul pg_hba.conf host-based authentication > > Can people tell me what they want changed. I know we need the pg_shadow > password field encrypted in the table and I will work on that now. I haven't heard of any more issues with pg_hba.conf so I will mark the item as done. I did cleanup the comments in the file. I have also added a TODO item: * Read pg_hba.conf only on postmaster startup or SIGHUP -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: AW: [HACKERS] New SQL Datatype RECURRINGCHAR
Tom Lane writes: > SQL92 gives this restriction on WHERE clauses for updatable views: > > d) If the immediately contained in QS imme- > diately contains a WC, then no leaf generally > underlying table of QS shall be a generally underlying table > of any contained in WC. > > which conveys nothing to my mind :-(, except that they're restricting > sub-SELECTs in WHERE somehow. Can anyone translate that into English? No table mentioned in the FROM-clause (in PG even implicitly) of the query expression (or view definition) is allowed to be mentioned in a subquery in the WHERE clause of the query expression (or view definition). The phrasing "leaf" and "generally" underlying is only to make this statement theoretically pure because you can create generally underlying tables that look different but do the same thing (different join syntax), whereas a leaf generally underlying table is guaranteed to be a real base table. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(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] create user problem
> Vince Vielhaber <[EMAIL PROTECTED]> writes: > > mydb=# create user foo NOCREATEUSER NOCREATEDB in group bar; > > ERROR: parser: parse error at or near "NOCREATEDB" > > > This line: > > [ CREATEDB | NOCREATEDB ] [ CREATEUSER | NOCREATEUSER ] > > does say I can do both, right? > > It says you can do both *in that order*. > > Feel free to submit a grammar patch to make CREATE USER more flexible > about the ordering of its optional clauses. Right now it's pretty > rigid. Added to TODO: * Allow CREATEUSER/CREATEDB ordering in CREATE/ALTER USER -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] timestamp not consistent with documentation or standard
Ok, i've been told to bring this up on this mailing list, so, I do so:
rather than kill myself re-explaining, i'll just cut&paste my email
correspondence.
I said:
can't create timestamp field (only timestamp with time zone)
the response was:
Those are the same data types.
then I said:
xxiii writes:
> Well, please document it as such then, as SQL definately implies that
> they are not the same (admittedly, i'm using SQL92 and not SQL99,
which I
> don't have a copy of), as does postgres's documentation, and also the
> fact that one can create a time field with and without timezone.
>
>
http://postgresql.crimelabs.net/users-lounge/docs/7.1/user/datatype-datetime.html
>
> This is very confusing, as is the fact that pre 7.1 postgres shows
> "timestamp" and 7.1 shows "timestamp with time zone", neither version
> seems to be willing to create the other variant (presumably because
they
> are really the same as far as postgres (but not its documentation) are
> concerned). I definately need the "without time zone" behaviour and
> range.
>
> If postgres is really using the same type internally to implement both
> behaviours that should be documented, along with how it works.
>
> I've just done some additional testing, and find that the timestamp
type
> does appear to support the wider range, and is acting like the
"without
> time zone" version, in spite of "\d table" saying "with time zone".
> However its output incorrectly, when years exceed 1.
>
> insert into test values('05-05-12080', '05-05-12080 1:1:1-7:00');
> insert into test values('05-05-12080', '05-05-12080 1:1:1+7:00');
>
> select * from test;
> w | o
> -+-
> 2080-05-05 00:00:00 | 2080-05-05 00:00:00
> 2080-05-05 00:00:00 | 2080-05-05 08:01:01
> 12080-05-05 00: | 12080-05-05 08:0101
> 12080-05-05 00: | 12080-05-04 18:0101
> (4 rows)
then I got told to bring it up here.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] timestamp not consistent with documentation or standard
Dave Martin <[EMAIL PROTECTED]> writes:
> Ok, i've been told to bring this up on this mailing list, so, I do so:
> rather than kill myself re-explaining, i'll just cut&paste my email
> correspondence.
Actually, what you should have done was consult the archives of this
list. You will find that you have wandered into the no man's land
of an armed conflict :-(. Unless you have some new argument that will
persuade one camp or the other to concede, it's unlikely that the
naming of the timestamp type (there is only one, and no visible interest
in implementing more) will change soon.
>> However its output incorrectly, when years exceed 1.
>>
>> insert into test values('05-05-12080', '05-05-12080 1:1:1-7:00');
>> insert into test values('05-05-12080', '05-05-12080 1:1:1+7:00');
>>
>> select * from test;
>> w | o
>> -+-
>> 2080-05-05 00:00:00 | 2080-05-05 00:00:00
>> 2080-05-05 00:00:00 | 2080-05-05 08:01:01
>> 12080-05-05 00: | 12080-05-05 08:0101
>> 12080-05-05 00: | 12080-05-04 18:0101
This is definitely a bug --- looks like EncodeDateTime fails to consider
the possibility that the output of sprintf will be longer than "normal".
Will fix.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[HACKERS] WaitOnLock: error on wakeup
Anyone know why I could possibly get this error? This doesn't happen deterministically. WaitOnLock: error on wakeup - Aborting this transaction I also got this notice: NOTICE: Deadlock detected -- See the lock(l) manual page --- Actually, what I'm looking for in this mail is a possible way for me to deterministically reproduce this by hand, to see if I can create this situation and then look in my code to see where I could possibly be doing the wrong thing. I'm not using anything fancy in my queries, Just foreign key constraints (all initially deferred), Selects, inserts, updates, views, transactions. No explicit lock or select for updates or triggers or notifiys or rules. I'm using Postgres 7.0.3. BTW, i tried searching the mailing list and turned up nothing interesting. I didn't search super carefully, because the search site is extremely slow. Thanx! -rchit ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Re: Backup and Recovery
On Fri, Jul 06, 2001 at 06:52:49AM -0400, Bruce Momjian wrote: > Nathan wrote: > > How hard would it be to turn these row records into updates against a > > pg_dump image, assuming access to a good table-image file? > > pg_dump is very hard because WAL contains only tids. No way to match > that to pg_dump-loaded rows. Maybe pg_dump can write out a mapping of TIDs to line numbers, and the back-end can create a map of inserted records' line numbers when the dump is reloaded, so that the original TIDs can be traced to the new TIDs. I guess this would require a new option on IMPORT. I suppose the mappings could be temporary tables. Nathan Myers [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] More ADD CONSTRAINT behaviour questions
When someone issues this command: ALTER TABLE test ADD UNIQUE (a, b); What happens when: 1. A non-unique index is already defined over (a, b) - Either add new index or promote existing one to unique? 2. A non-unique index is already defined over (b, a) - As above? 3. A primary index is already defined over (a, b) - ERROR: unique already implied by primary? 4. A primary index is already defined over (b, a) - As above? 5. A unique index is already defined over (a, b) - ERROR: unique index already exists over keys? 6. A unique index is already defined over (b, a) - As above. Technically a different index, but effect as far as uniqueness is concerned is identical? 7. No index exists over (a, b) or (b, a) - Create a new unique index over (a, b)? Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] Mozilla 1.0 release soon?
Here is a message about finding a target date for Mozilla's 1.0 release. I found the article sobering: http://www.mozillazine.org/articles/article1958.html -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] More ADD CONSTRAINT behaviour questions
"Christopher Kings-Lynne" <[EMAIL PROTECTED]> writes: > 6. A unique index is already defined over (b, a) > - As above. Technically a different index, but effect > as far as uniqueness is concerned is identical? This case *must not* be an error IMHO: it's perfectly reasonable to have indexes on both (a,b) and (b,a), and if the column pair happens to be unique, there's no reason why they shouldn't both be marked unique. Because of that, I'm not too excited about raising an error in any case except where you have an absolutely identical pre-existing index, ie, there's already a unique index on (a,b) --- doesn't matter much whether it's marked primary or not. For ADD PRIMARY KEY, there mustn't be any pre-existing primary index, of course. I can see promoting an extant matching unique index to primary status, though, rather than making another index. regards, tom lane ---(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
[HACKERS] Re: Mozilla 1.0 release soon?
... > I found the article sobering: ... It is commonly thought that one should be sober at least part of every day, so it isn't entirely clear whether you consider this good or bad. Oh, maybe that isn't what you meant? ;) - Thomas ---(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] More ADD CONSTRAINT behaviour questions
On Tue, 10 Jul 2001, Christopher Kings-Lynne wrote: > When someone issues this command: > > ALTER TABLE test ADD UNIQUE (a, b); > > What happens when: > > 1. A non-unique index is already defined over (a, b) > > - Either add new index or promote existing one to unique? Well, either works, but if you promote, you should have a way to keep track of the fact you did so, because dropping the constraint shouldn't drop the index then but demote it. I'm less sure about what the correct behavior would be for adding primary keys (if you added a primary key on a unique index and then dropped the primary key, do you end up with a normal unique at the end?) > 2. A non-unique index is already defined over (b, a) > > - As above? I agree with Tom for 2/4/6, since the indexes are different for planning purposes. > 3. A primary index is already defined over (a, b) > > - ERROR: unique already implied by primary? Seems reasonable. Maybe errors like: ERROR: Primary key already defined on test(a,b) ERROR: Unique constraint already defined on test(a,b) ---(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] Re: Mozilla 1.0 release soon?
> ... > > I found the article sobering: > ... > > It is commonly thought that one should be sober at least part of every > day, so it isn't entirely clear whether you consider this good or bad. > > Oh, maybe that isn't what you meant? ;) I read it and thought, "Wow, that seems like a royal mess." I am sure someone will chime in and say it isn't, and perhaps it isn't. It just sounded that way to me. I know we have a challenge to put out each release, but their challenges seem almost overwhelming to me. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(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] More ADD CONSTRAINT behaviour questions
> "Christopher Kings-Lynne" <[EMAIL PROTECTED]> writes: > > 6. A unique index is already defined over (b, a) > > > - As above. Technically a different index, but effect > > as far as uniqueness is concerned is identical? > > This case *must not* be an error IMHO: it's perfectly reasonable to have > indexes on both (a,b) and (b,a), and if the column pair happens to be > unique, there's no reason why they shouldn't both be marked unique. > > Because of that, I'm not too excited about raising an error in any case > except where you have an absolutely identical pre-existing index, ie, > there's already a unique index on (a,b) --- doesn't matter much whether > it's marked primary or not. > > For ADD PRIMARY KEY, there mustn't be any pre-existing primary index, > of course. I can see promoting an extant matching unique index to > primary status, though, rather than making another index. > Yea, I agree with Tom. Usually we let the person do whatever they want except in cases that clearly make no sense or where we can improve it. Good questions, though. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] Repost: Get table/field-identifiers in uppercase
Hi all, because no one noticed my last question, please forgive me this repost, but I promised my boss to get an answer to this questions. Here they are: When a new table or field is created without quotes, it is assumed to be case-insensitive. Herefore I have some questions: - Is it SQL-92-conform to handle >"test"< like >test< without quotes, or shouldn't it be >test< forced to lowercase? - Oracle returns this no_matter_what-case_it_is-fields with uppercase-letters. Is it possible for Postgresql, to imitate this behaviour? - How is the handling of case-sensitivity handled in the system-catalogs? Is ther any flag or depends it on the name of the object only? Thank you very much in advance! Klaus -- Visit WWWdb at http://wwwdb.org ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Solaris source code
On Thu, Jul 05, 2001 at 04:30:40PM -0400, Bruce Momjian allegedly wrote: > I have purchased the Solaris source code from Sun for $80. (I could > have downloaded it for free after faxing them an 11 page contract, but I > decided I wanted the CD's.) See the slashdot story at: > > http://slashdot.org/article.pl?sid=01/06/30/1224257&mode=thread > > My hope is that I can use the source code to help debug Solaris > PostgreSQL problems. It includes source for the kernel and all user > programs. The code is similar to *BSD kernels. It is basically Unix > SvR4 with Sun's enhancements. It has both AT&T and Sun copyrights on > the files. Cool. It would be nice to know why the regression tests fail on Solaris when using a UNIX socket. Cheers, Mathijs ---(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] Solaris source code
On Thu, Jul 05, 2001 at 02:03:31PM -0700, Naomi Walker allegedly wrote: > At 04:30 PM 7/5/01 -0400, Bruce Momjian wrote: > >I have purchased the Solaris source code from Sun for $80. (I could > >have downloaded it for free after faxing them an 11 page contract, but I > >decided I wanted the CD's.) See the slashdot story at: > > > > http://slashdot.org/article.pl?sid=01/06/30/1224257&mode=thread > > > >My hope is that I can use the source code to help debug Solaris > >PostgreSQL problems. It includes source for the kernel and all user > >programs. The code is similar to *BSD kernels. It is basically Unix > >SvR4 with Sun's enhancements. It has both AT&T and Sun copyrights on > >the files. > > Bruce, > > We are about to roll out PostgreSQL on Solaris, and I am interested in any > Solaris specific gotcha's. Do you have some specifics in mind, or was this > just general preventive maintenance type steps? PostgreSQL 7.1 fails the regression tests when using a UNIX socket, which is faster than a TCP/IP socket (when both the client and the server are running on the same machine). We're running a few small PostgreSQL databases on Solaris and we're going to implement a bigger one in the near future. If you connect via TCP/IP sockets, you should be safe. We're using JDBC to connect to the database and JDBC always uses a TCP/IP socket. So far we haven't run into any real problems, although PostgreSQL did crash once, for unknown reasons (probably becase someone was messing with it). Not really helpful, I guess. Doing some testing of your own is highly recommended ;) Cheers, Mathijs ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: AW: [HACKERS] New SQL Datatype RECURRINGCHAR
Zeugswetter Andreas SB wrote: > > > > My feeling is that the restrictions are stringent enough to eliminate > > most of the interesting uses of views, and hence an automatic rule > > creation feature is not nearly as useful/important as it appears at > > first glance. > > The most prominent of the "interesting uses" probably beeing when the views > are part of the authorization system, since views are the only standardized > mechanism to restrict access at the row level. True, and often the views can be restricted to insert only data that will be visible using this view. > Imho not to be neglected. > (user xxx is only allowed to manipulate rows that belong to his department, > so he is only granted access to a view, not the main table) This seems to be a little more complicated that Tom described (I.e. it has probably more than one relation involved or uses a function to get CURRENT_USER's department id) IIRC MS Access has much broader repertoire of updatable views than described by Tom. Can be it's an extension to standard SQL though. - Hannu ---(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: AW: [HACKERS] New SQL Datatype RECURRINGCHAR
Hannu Krosing <[EMAIL PROTECTED]> writes: > Zeugswetter Andreas SB wrote: >> The most prominent of the "interesting uses" probably beeing when the views >> are part of the authorization system, since views are the only standardized >> mechanism to restrict access at the row level. > True, and often the views can be restricted to insert only data that > will be visible using this view. Right. The interesting question is whether an automatic rule creator could be expected to derive the correct restrictions on insert/update/delete given the WHERE clause of the view. Insert/delete might not be too bad (at first thought, requiring the inserted/deleted rows to pass the WHERE condition would do), but I'm not so sure about update. Is it sufficient to require both the old and new states of the row to pass the WHERE condition? SQL92 gives this restriction on WHERE clauses for updatable views: d) If the immediately contained in QS imme- diately contains a WC, then no leaf generally underlying table of QS shall be a generally underlying table of any contained in WC. which conveys nothing to my mind :-(, except that they're restricting sub-SELECTs in WHERE somehow. Can anyone translate that into English? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: AW: [HACKERS] New SQL Datatype RECURRINGCHAR
Tom Lane wrote: > Hannu Krosing <[EMAIL PROTECTED]> writes: > > Zeugswetter Andreas SB wrote: > >> The most prominent of the "interesting uses" probably beeing when the views > >> are part of the authorization system, since views are the only standardized > >> mechanism to restrict access at the row level. > > > True, and often the views can be restricted to insert only data that > > will be visible using this view. > > Right. The interesting question is whether an automatic rule creator > could be expected to derive the correct restrictions on > insert/update/delete given the WHERE clause of the view. Insert/delete > might not be too bad (at first thought, requiring the inserted/deleted > rows to pass the WHERE condition would do), but I'm not so sure about > update. Is it sufficient to require both the old and new states of the > row to pass the WHERE condition? Yes, no other chance. Remember that the rule on SELECT is allways applied to the scan that looks for the rows to update, so you'd never have a chance to hit other rows through the view. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(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: AW: [HACKERS] pg_index.indislossy
Added to pg_index.h file as a comment. > > > > > Can someone tell me what we use indislossy for? > > Ok, so the interpretation of this field is: > A match in the index needs to be reevaluated in the heap tuple data, > since a match in the index does not necessarily mean, that the heap tuple > matches. > If the heap tuple data matches, the index must always match. > > A very typical example for such an index is a hash index. This might explain the > fact, that the ODBC driver misinterpreted that field as meaning that the index is a >hash. > The field has nothing to do with partial index. > > Andreas > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] grant and SQL92
> On Sat, 9 Jun 2001, Peter Eisentraut wrote: > > > Vince Vielhaber writes: > > > > > I can grant a series of privileges (comma separated) on a series of > > > objects (comma separated) to either a user, group or public NOT a > > > comma separated list of users or groups. > > > > I should have this finished today to tomorrow. > > I looked at it but it looked too much like it involved gram.y which I'm > going to happily stay away from for now :) Added to TODO: * Allow GRANT/REVOKE to handle multiple user/group names -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
