[HACKERS] Why an array in pg_group?
Hi, Is there any reason why the grolist field in the table pg_group is implemented as an array and not as a separate table? According to the documentation: Arrays are not sets; using arrays in the manner described in the previous paragraph is often a sign of database misdesign. I have trouble implementing a way to easily check whether a user is part of a group. (I use Apache::AuthDBI to implement authentication and wanted to make a view with columns username, userid , groupname. And installing the contrib/array give's me a postgresql that is different from all the others :-( -- __ "Nothing is as subjective as reality" Reinoud van Leeuwen[EMAIL PROTECTED] http://www.xs4all.nl/~reinoud __ ---(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] Why an array in pg_group?
Reinoud van Leeuwen <[EMAIL PROTECTED]> writes: > Is there any reason why the grolist field in the table pg_group is > implemented as an array and not as a separate table? It's easier to cache a single entry per group in the GRONAME and GROSYSID syscaches than a bunch of them. The design is optimized for the needs of the system's internal permissions-checking code, not for the convenience of people trying to interrogate pg_group in SQL. > I have trouble implementing a way to easily check whether a user is part > of a group. Perhaps you could create a table that has no purpose except to be a permissions-check target, and set it up to have permissions granted only to the group you care about. Then use has_table_privilege(). In the long run I'd have no objection to adding an is_group_member() function (need a better choice of name, perhaps) to cater to this sort of request. Too late for 7.3 though. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Error when comparing an integer to an empty string.
Hi!
I'm new on this list, my name is David Pradier, and i'm french.
I'm currently trying the new postgresql 7.3rc1, and i've noticed that if
i compared an integer to an empty string, i ran in an error.
Example :
=# select nom_comm from operation where id_operation = '';
ERROR: pg_atoi: zero-length string
\d operation gives :
id_operation | integer | not null default
nextval('"operation_type_id_operation_seq"'::text)
Is this a bug or a feature of the new 7.3 version ?
Is there a purpose ?
Thanks for your help.
Best regards,
David.
--
[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] Error when comparing an integer to an empty string.
On Thu, Nov 21, 2002 at 17:30:10 +0100, David Pradier <[EMAIL PROTECTED]> wrote: > Hi! > > I'm new on this list, my name is David Pradier, and i'm french. > > I'm currently trying the new postgresql 7.3rc1, and i've noticed that if > i compared an integer to an empty string, i ran in an error. > > Is this a bug or a feature of the new 7.3 version ? > Is there a purpose ? What number do you expect '' to represent? Probably you either want to use: = '0' or is null depending on what you are really trying to do. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Error when comparing an integer to an empty string.
On Thu, Nov 21, 2002 at 11:07:55AM -0600, Bruno Wolff III wrote: > On Thu, Nov 21, 2002 at 17:30:10 +0100, > David Pradier <[EMAIL PROTECTED]> wrote: > > Hi! > > > > I'm new on this list, my name is David Pradier, and i'm french. > > > > I'm currently trying the new postgresql 7.3rc1, and i've noticed that if > > i compared an integer to an empty string, i ran in an error. > > > > Is this a bug or a feature of the new 7.3 version ? > > Is there a purpose ? > > What number do you expect '' to represent? > > Probably you either want to use: > = '0' > or > is null > depending on what you are really trying to do. The point David was trying to make is: with 7.2: template1=# select 1 = ''; ?column? -- f (1 row) with 7.3rc1: template1=# select 1 = ''; ERROR: pg_atoi: zero-length string Is this change of behavior intentional? -- HIPPOLYTE: Trézène m'obéit. Les campagnes de Crète Offrent au fils de Phèdre une riche retraite. (Phèdre, J-B Racine, acte 2, scène 2) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Error when comparing an integer to an empty string.
Louis-David Mitterrand <[EMAIL PROTECTED]> writes: > with 7.2: > > template1=# select 1 = ''; >?column? > -- >f > (1 row) > > > with 7.3rc1: > > template1=# select 1 = ''; > ERROR: pg_atoi: zero-length string > > > Is this change of behavior intentional? Yes. I raised it as a possible point of backwards incompatibility at the time the change was made, but the consensus was that this behavior was worth getting rid of. Cheers, Neil -- Neil Conway <[EMAIL PROTECTED]> || PGP Key ID: DB3C29FC ---(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] Optimizer & boolean syntax
Are those two syntaxes eqivalent ? select * from users where monitored; select * from users where monitored=true; If the answer is yes, the optimimer probably doesn't agree with you :) Tested on RC1: template1=# create table a (a boolean, b text); CREATE TABLE inserted ~18000 rows with just one true (just to make an index scan meaningful) template1=# vacuum analyze a; VACUUM template1=# explain select * from a where a; QUERY PLAN Seq Scan on a (cost=0.00..802.64 rows=1 width=11) Filter: a (2 rows) template1=# explain select * from a where a=true; QUERY PLAN -- Index Scan using a_a on a (cost=0.00..2.01 rows=1 width=11) Index Cond: (a = true) (2 rows) Bye! -- Daniele Orlandi Planet Srl ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Error when comparing an integer to an empty string.
Louis-David Mitterrand <[EMAIL PROTECTED]> writes: > The point David was trying to make is: > with 7.2: > template1=# select 1 = ''; >?column? > -- >f > (1 row) > with 7.3rc1: > template1=# select 1 = ''; > ERROR: pg_atoi: zero-length string > Is this change of behavior intentional? Yes. 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
Re: [HACKERS] xBSD shmem doc deficiency
Hi Neil, > However, the FreeBSD box I'm playing with isn't mine, so I'm not too > keen to change sysctls (well, that and I don't have root :-) ). Would > a kind BSD user confirm that: > > (a) the sysctls above *can* be used to change kernel shared > memory settings, and the default value of the sysctl is > the kernel option referred to in the docs. Unless this has changed in 4.7, lots of those shm sysctls are read-only...ie. you cannot set the shared memory pool size at runtime. I'll look at it again tho. > (c) the 'prevent shared memory paging' sysctl vaguely referred > to in the docs is 'kern.ipc.shm_use_phys', right? I'll have to investigate that one... Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Optimizer & boolean syntax
Using the famous WAG tech, in your first query the optimizer has to evaluate monitored for each record to determine its value. Robert Treat On Thu, 2002-11-21 at 13:39, Daniele Orlandi wrote: > > Are those two syntaxes eqivalent ? > > select * from users where monitored; > select * from users where monitored=true; > > If the answer is yes, the optimimer probably doesn't agree with you :) > > Tested on RC1: > > template1=# create table a (a boolean, b text); > CREATE TABLE > > > inserted ~18000 rows with just one true (just to make an index scan > meaningful) > > template1=# vacuum analyze a; > VACUUM > template1=# explain select * from a where a; > QUERY PLAN > > Seq Scan on a (cost=0.00..802.64 rows=1 width=11) > Filter: a > (2 rows) > > template1=# explain select * from a where a=true; >QUERY PLAN > -- > Index Scan using a_a on a (cost=0.00..2.01 rows=1 width=11) > Index Cond: (a = true) > (2 rows) > > Bye! > > -- > Daniele Orlandi > Planet Srl > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html ---(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] Optimizer & boolean syntax
On Thu, 21 Nov 2002, Daniele Orlandi wrote: > Are those two syntaxes eqivalent ? > > select * from users where monitored; > select * from users where monitored=true; > > If the answer is yes, the optimimer probably doesn't agree with you :) That depends on the definition of equivalent. They presumably give the same answer (I'm assuming monitored is a boolean), but the latter has something that's considered an indexable condition and I believe the former does not (even with enable_seqscan=off the former syntax appears to give a sequence scan, usually a good sign it's not considered indexable). ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Optimizer & boolean syntax
> > Are those two syntaxes eqivalent ? > > > > select * from users where monitored; > > select * from users where monitored=true; > > > > If the answer is yes, the optimimer probably doesn't agree with you :) > > That depends on the definition of equivalent. They presumably give the > same answer (I'm assuming monitored is a boolean), but the latter has > something that's considered an indexable condition and I believe the > former does not (even with enable_seqscan=off the former syntax > appears to give a sequence scan, usually a good sign it's not considered > indexable). I think his point is that they _should_ be equivalent. Surely there's something in the optimiser that discards '=true' stuff, like 'a=a' should be discarded? Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Optimizer & boolean syntax
On Thu, 21 Nov 2002, Christopher Kings-Lynne wrote: > > > Are those two syntaxes eqivalent ? > > > > > > select * from users where monitored; > > > select * from users where monitored=true; > > > > > > If the answer is yes, the optimimer probably doesn't agree with you :) > > > > That depends on the definition of equivalent. They presumably give the > > same answer (I'm assuming monitored is a boolean), but the latter has > > something that's considered an indexable condition and I believe the > > former does not (even with enable_seqscan=off the former syntax > > appears to give a sequence scan, usually a good sign it's not considered > > indexable). > > I think his point is that they _should_ be equivalent. Surely there's > something in the optimiser that discards '=true' stuff, like 'a=a' should be > discarded? I figure that's what he meant, but it isn't what was said. ;) "col" isn't of the general form "indexkey op constant" or "constant op indexkey" which I presume it's looking for given the comments in indxpath.c. I'm not sure what the best way to make it work would be given that presumably we'd want to make col IS TRUE/FALSE use an index at the same time (since that appears to not do so as well). ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Optimizer & boolean syntax
> > I think his point is that they _should_ be equivalent. Surely there's > > something in the optimiser that discards '=true' stuff, like 'a=a' should be > > discarded? > > I figure that's what he meant, but it isn't what was said. ;) > > "col" isn't of the general form "indexkey op constant" or "constant op > indexkey" which I presume it's looking for given the comments in > indxpath.c. I'm not sure what the best way to make it work would be given > that presumably we'd want to make col IS TRUE/FALSE use an index at the > same time (since that appears to not do so as well). Not that I see the point of indexing booleans, but hey :) Chris ---(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] Optimizer & boolean syntax
On Thu, Nov 21, 2002 at 02:45:34PM -0800, Christopher Kings-Lynne wrote: > > > I think his point is that they _should_ be equivalent. Surely there's > > > something in the optimiser that discards '=true' stuff, like 'a=a' > should be > > > discarded? > Not that I see the point of indexing booleans, but hey :) If one of the values is much more infrequent than the other, you can probably get a substantial win using a partial index, can't you? -- Alvaro Herrera () "Everybody understands Mickey Mouse. Few understand Hermann Hesse. Hardly anybody understands Einstein. And nobody understands Emperor Norton." ---(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] Optimizer & boolean syntax
On Thu, 21 Nov 2002, Christopher Kings-Lynne wrote: > > > I think his point is that they _should_ be equivalent. Surely there's > > > something in the optimiser that discards '=true' stuff, like 'a=a' > should be > > > discarded? > > > > I figure that's what he meant, but it isn't what was said. ;) > > > > "col" isn't of the general form "indexkey op constant" or "constant op > > indexkey" which I presume it's looking for given the comments in > > indxpath.c. I'm not sure what the best way to make it work would be given > > that presumably we'd want to make col IS TRUE/FALSE use an index at the > > same time (since that appears to not do so as well). > > Not that I see the point of indexing booleans, but hey :) While full indexes do seem futile, partial indexes can be quite useful. select articles from forum where approved is false if 99.9% of all articles are approved would be quite common. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Optimizer & boolean syntax
On Thu, 21 Nov 2002, Christopher Kings-Lynne wrote: > > > I think his point is that they _should_ be equivalent. Surely there's > > > something in the optimiser that discards '=true' stuff, like 'a=a' > should be > > > discarded? > > > > I figure that's what he meant, but it isn't what was said. ;) > > > > "col" isn't of the general form "indexkey op constant" or "constant op > > indexkey" which I presume it's looking for given the comments in > > indxpath.c. I'm not sure what the best way to make it work would be given > > that presumably we'd want to make col IS TRUE/FALSE use an index at the > > same time (since that appears to not do so as well). > > Not that I see the point of indexing booleans, but hey :) also, in reference to my last message, even if the % was 50/50, if the table was such that the bool was in a table next to a text field with 20k or text in it, an index on the bool would be much faster to go through than to seq scan the table. ---(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] Optimizer & boolean syntax
> > Not that I see the point of indexing booleans, but hey :) > > If one of the values is much more infrequent than the other, you can > probably get a substantial win using a partial index, can't you? Yes, I thought of the partial index after I wrote that email :) Chris ---(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] Optimizer & boolean syntax
> > > "col" isn't of the general form "indexkey op constant" or "constant op > > > indexkey" which I presume it's looking for given the comments in > > > indxpath.c. I'm not sure what the best way to make it work would be given > > > that presumably we'd want to make col IS TRUE/FALSE use an index at the > > > same time (since that appears to not do so as well). > > > > Not that I see the point of indexing booleans, but hey :) > > also, in reference to my last message, even if the % was 50/50, if the > table was such that the bool was in a table next to a text field with 20k > or text in it, an index on the bool would be much faster to go through > than to seq scan the table. Hmmm...I'm not sure about that. Postgres's storage strategry with text will be to keep it in a side table (or you can use ALTER TABLE/SET STORAGE) and it will only be retrieved if it's in the select parameters. Chris ---(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] Optimizer & boolean syntax
On Thu, 21 Nov 2002, Christopher Kings-Lynne wrote: > > > > "col" isn't of the general form "indexkey op constant" or "constant op > > > > indexkey" which I presume it's looking for given the comments in > > > > indxpath.c. I'm not sure what the best way to make it work would be > given > > > > that presumably we'd want to make col IS TRUE/FALSE use an index at > the > > > > same time (since that appears to not do so as well). > > > > > > Not that I see the point of indexing booleans, but hey :) > > > > also, in reference to my last message, even if the % was 50/50, if the > > table was such that the bool was in a table next to a text field with 20k > > or text in it, an index on the bool would be much faster to go through > > than to seq scan the table. > > Hmmm...I'm not sure about that. Postgres's storage strategry with text will > be to keep it in a side table (or you can use ALTER TABLE/SET STORAGE) and > it will only be retrieved if it's in the select parameters. True, but replace that text with 1500 integers. :) The only problem with the partial index solution is that it seems to still only work for the same method of asking for the result, so if you make an index where col=true, using col IS TRUE or col in a query doesn't seem to use it. ---(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] Optimizer & boolean syntax
Stephan Szabo wrote: On Thu, 21 Nov 2002, Daniele Orlandi wrote: Are those two syntaxes eqivalent ? select * from users where monitored; select * from users where monitored=true; If the answer is yes, the optimimer probably doesn't agree with you :) That depends on the definition of equivalent. By equivalent I mean "means the same thing so, behaves in the same way". I consider the former syntax to be cleaner and I would tend to use it most of times. For what concerns partial indexes, I agree, it's a better approach for this kind of indexing and I did some test: - ctonet=# create index users_monitored on users (monitored) where monitored; CREATE ctonet=# explain select * from users where monitored; NOTICE: QUERY PLAN: Index Scan using users_monitored on users (cost=0.00..9.44 rows=6 width=186) EXPLAIN Nice, it appears to use the index, but: ctonet=# explain select * from users where monitored=true; NOTICE: QUERY PLAN: Seq Scan on users (cost=0.00..8298.84 rows=59 width=186) EXPLAIN - The problem is the opposite... so, effectively, seems that the optimizer considers "monitored" and "monitored=true" as two different expressions... The viceversa is analog and we also can see that the syntax "monitored is true" is considered different from the other two syntaxes: --- ctonet=# drop index users_monitored; DROP ctonet=# create index users_monitored on users (monitored) where monitored=true; CREATE ctonet=# explain select * from users where monitored=true; NOTICE: QUERY PLAN: Index Scan using users_monitored on users (cost=0.00..9.45 rows=6 width=186) EXPLAIN ctonet=# explain select * from users where monitored; NOTICE: QUERY PLAN: Seq Scan on users (cost=0.00..8077.07 rows=59 width=186) EXPLAIN ctonet=# create index users_monitored on users (monitored) where monitored=true; CREATE ctonet=# explain select * from users where monitored is true; NOTICE: QUERY PLAN: Seq Scan on users (cost=0.00..8077.07 rows=59 width=186) EXPLAIN - What I propose is that all those syntaxes are made equivalent (by, for example, rewriting boolean comparisons to a common form) in order to have a more consistent index usage. Bye! -- Daniele Orlandi Planet Srl ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] bug in pg_dumpall 7.3
Do this: create database "adsf asdf"; Then to a pg_dumpall and you get this: \connect "adsf asdf" pg_dump: too many command line options (first is 'asdf') Try 'pg_dump --help' for more information. pg_dumpall: pg_dump failed on adsf asdf, exiting LOG: pq_recvbuf: unexpected EOF on client connection Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Optimizer & boolean syntax
Daniele Orlandi <[EMAIL PROTECTED]> writes: > The problem is the opposite... so, effectively, seems that the optimizer > considers "monitored" and "monitored=true" as two different expressions... Check. > The viceversa is analog and we also can see that the syntax "monitored > is true" is considered different from the other two syntaxes: As it should be. > What I propose is that all those syntaxes are made equivalent Only two of them are logically equivalent. Consider NULL. Even for the first two, assuming equivalence requires hard-wiring an assumption about the behavior of the "bool = bool" operator; which is a user-redefinable operator. I'm not totally comfortable with the idea. 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
Re: [HACKERS] bug in pg_dumpall 7.3
"Christopher Kings-Lynne" <[EMAIL PROTECTED]> writes: > Do this: > create database "adsf asdf"; > Then to a pg_dumpall and you get this: > \connect "adsf asdf" > pg_dump: too many command line options (first is 'asdf') Good catch --- fixed. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] xBSD shmem doc deficiency
Neil Conway wrote: > (c) the 'prevent shared memory paging' sysctl vaguely referred > to in the docs is 'kern.ipc.shm_use_phys', right? I have added a mention of this to the 7.4 docs: You might also want to use the sysctl setting to lock shared memory into RAM and prevent it from being paged out to swap, e.g. kern.ipc.shm_use_phys. -- 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [GENERAL] Bug with sequence
Oliver Elphick wrote: > I deleted the first table. The sequence was deleted too, leaving the > default of the second table referring to a non-existent sequence. > > > Could this be a TODO item in 7.4, to add a dependency check when a > sequence is set as the default without being created at the same time? Added to TODO: * Have sequence dependency track use of DEFAULT sequences, seqname.nextval -- 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] Why an array in pg_group?
Tom Lane wrote: > > I have trouble implementing a way to easily check whether a user is part > > of a group. > > Perhaps you could create a table that has no purpose except to be a > permissions-check target, and set it up to have permissions granted only > to the group you care about. Then use has_table_privilege(). > > In the long run I'd have no objection to adding an is_group_member() > function (need a better choice of name, perhaps) to cater to this sort > of request. Too late for 7.3 though. I believe Joe Conway already coded that, but it didn't make it into 7.3. -- 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 4: Don't 'kill -9' the postmaster
[HACKERS] nested transactions
I am going to work on nested transactions for 7.4. My goal is to first implement nested transactions: BEGIN; SELECT ... BEGIN; UPDATE; COMMIT; DELETE; COMMIT; and later savepoints (Oracle): BEGIN; SELECT ... SAVEPOINT t1; UPDATE; SAVEPOINT t2; DELETE; ROLLBACK TO SAVEPOINT t2; COMMIT; I assume people want both. As an implementation, I will hack xact.c to create a transaction status stack so when you do a BEGIN inside a transaction, it saves the transaction status, the transaction block status, and perhaps the command counter. A COMMIT restores these values. I also plan to modify the on commit/abort actions. On a subtransaction commit, little has to be done, but on an ABORT, you must execute any abort actions required by that subtransaction _and_ remove any on commit actions for the subtransaction. There will need to be some code reorganization because some on commit/abort activity assumes only one transaction can be in process. A stack will need to be added in those cases. And finally, I must abort tuple changes made by the aborted subtransaction. One way of doing that is to keep all relation id's modified by the transaction, and do a sequential scan of the tables on abort, changing the transaction id's to a fixed aborted transaction id. However, this could be slow. (We could store tids if only a few rows are updated by a subtransaction. That would speed it up considerably.) Another idea is to use new transaction id's for the subtransactions, and update the transaction id status in pg_clog for the subtransactions, so that there is no transaction id renumbering required. One problem with this is the requirement of updating all the clog transaction id statuses atomically. One way to do that would be to do parent/child dependency in clog so that if a child is looked up and it is marked as "in process", a check could be done against the parent. Once the outer transaction is committed/aborted, those subtransactions could be updated so there would be no need to reference the parent any longer. This would increase the clog size per transaction from 2 bits to 4 bytes (two bits for status, 30 bits for offset to parent). -- 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 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_stat_database shows userid as OID
Does anyone want userid to be an OID? Peter? Anyone? If not, I will add it to the TODO list or work on the patch myself. --- Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > Tom Lane wrote: > >> I'd recommend not making any piecemeal changes, especially not when > >> there's not yet a consensus which way to converge. > > > Well, seems we should make it consistent at least. > > I think the original argument stemmed from the idea that we ought to use > pg_shadow's OID column as the user identifier (eliminating usesysid per > se). This seems like a good idea at first but I think it has a couple > of fatal problems: > * disappearance of pg_shadow.usesysid column will doubtless break some > applications > * if we use OID then it's much more difficult to support explicit > assignment of userid > > > I think some wanted it to be an int so they could use the > > same unix uid for pg_shadow, but I think we aren't using that idea much > > anymore. > > I don't think anyone worries about making usesysid match /etc/passwd > anymore, but nonetheless CREATE USER WITH SYSID is still an essential > capability. What if you drop a user accidentally while he still owns > objects? You *must* be able to recreate him with the same sysid as > before. pg_depend cannot save us from this kind of mistake, either, > since users span databases. > > So it seems to me that we must keep pg_shadow.usesysid as a separate > column and not try to make it the OID of pg_shadow. > > Given that decision, the argument for making it be type OID seems very > weak, so I'd lean to the "use int4" camp myself. But I'm not sure > everyone agrees. I think Peter was strongly in favor of OID when he > was revising the session-authorization code (that's why it all uses OID > for user IDs...) > > As far as the actual C code goes, I'd lean to creating new typedefs > UserId and GroupId (or some such names) and making all the routine > and variable declarations use those, and not either OID or int4. > But I'm not excited about promoting these typedefs into actual SQL > types, as was done for TransactionId and CommandId; the payback seems > much less than the effort needed. > > regards, tom lane > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -- 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/users-lounge/docs/faq.html
Re: [HACKERS] nested transactions
Bruce Momjian <[EMAIL PROTECTED]> writes: > I am going to work on nested transactions for 7.4. > [some details] This is, of course, barely scratching the surface of what will need to be done. I assume you've abandoned the notion of a fast release cycle for 7.4? 'Cause if you start on this, we ain't releasing any time soon ... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] nested transactions
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > I am going to work on nested transactions for 7.4. > > [some details] > > This is, of course, barely scratching the surface of what will need to > be done. > > I assume you've abandoned the notion of a fast release cycle for 7.4? > 'Cause if you start on this, we ain't releasing any time soon ... Abandoned because of the delay in Win32 (end of Dec), PITR (not being worked on), and mostly because very few wanted a short release cycle. I will keep the transaction changes private to my tree, so if I can't get it done, I will just keep it for the next release. -- 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 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] Fw: Missing file from CVS?
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches I will try to apply it within the next 48 hours. --- Al Sutton wrote: > Heres a patch which will create the sql_help.h file if it doesn't already > exist using an installed copy of perl. I've tested it using perl v5.6.1 from > ActiveState and all appears to work. > > Can someone commit this for me, or throw back some comments. > > Thanks, > > Al. > > > --- src/bin/psql/win32.mak 2002/10/29 04:23:30 1.11 > +++ src/bin/psql/win32.mak 2002/11/20 19:44:35 > @@ -7,14 +7,16 @@ > !ENDIF > > CPP=cl.exe > +PERL=perl.exe > > OUTDIR=.\Release > INTDIR=.\Release > +REFDOCDIR= ../../../doc/src/sgml/ref > # Begin Custom Macros > OutDir=.\Release > # End Custom Macros > > -ALL : "$(OUTDIR)\psql.exe" > +ALL : sql_help.h "$(OUTDIR)\psql.exe" > > CLEAN : > -@erase "$(INTDIR)\command.obj" > @@ -91,3 +93,7 @@ > $(CPP) @<< > $(CPP_PROJ) $< > << > + > +sql_help.h: create_help.pl > +$(PERL) create_help.pl $(REFDOCDIR) $@ > + > > > > > - Original Message - > From: "Al Sutton" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Friday, November 15, 2002 8:48 PM > Subject: Missing file from CVS? > > > > All, > > > > I've just tried to build the Win32 components under Visual Studio's C++ > > compiler from the win32.mak CVS archive at > > :pserver:[EMAIL PROTECTED]:/projects/cvsroot and found that > the > > following file was missing; > > > > src\bin\psql\sql_help.h > > > > I've copied the file from the the source tree of version 7.2.3 and the > > compile works with out any problems. > > > > Should the file be in CVS? > > > > Al. > > > > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > -- 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [GENERAL] Bug with sequence
It seems worth pointing out, too, that some SQL purists propose not
relying on product-specific methods of auto-incrementing.
I.e., it is possible to do something like:
insert into foo( col, ... )
values( coalesce( ( select max( col ) from foo ), 0 ) + 1, ... );
and this is easily placed in a trigger.
-tfo
In article <[EMAIL PROTECTED]>,
[EMAIL PROTECTED] (Tom Lane) wrote:
> Justin Clift <[EMAIL PROTECTED]> writes:
> > Oliver Elphick wrote:
> >> I created a sequence using SERIAL when I created a table. I used the
> >> same sequence for another table by setting a column default to
> >> nextval(sequence).
> >>
> >> I deleted the first table. The sequence was deleted too, leaving the
> >> default of the second table referring to a non-existent sequence.
>
> > This sounds like a serious bug in our behaviour, and not something we'd
> > like to release.
>
> We will be releasing it whether we like it or not, because
> nextval('foo') doesn't expose any visible dependency on sequence foo.
>
> (If you think it should, how about nextval('fo' || 'o')? If you think
> that's improbable, consider nextval('table' || '_' || 'col' || '_seq').)
>
> The long-term answer is to do what Rod alluded to: support the
> Oracle-style syntax foo.nextval, so that the sequence reference is
> honestly part of the parsetree and not buried inside a string
> expression.
>
> In the meantime, I consider that Oliver was misusing the SERIAL
> feature. If you want multiple tables fed by the same sequence object,
> you should create the sequence as a separate object and then create
> the tables using explicit "DEFAULT nextval('foo')" clauses. Doing what
> he did amounts to sticking his fingers under the hood of the SERIAL
> implementation; if he gets his fingers burnt, it's his problem.
>
> > Specifically in relation to people's existing scripts, and also to
> > people who are doing dump/restore of specific tables (it'll kill the
> > sequences that other tables depend on too!)
>
> 7.3 breaks no existing schemas, because older schemas will be dumped
> as separate CREATE SEQUENCE and CREATE TABLE ... DEFAULT nextval()
> commands.
>
> 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
Re: [HACKERS] [GENERAL] Bug with sequence
Of course, those would be SQL purists who _don't_ understand
concurrency issues. ;-)
---
Thomas O'Connell wrote:
> It seems worth pointing out, too, that some SQL purists propose not
> relying on product-specific methods of auto-incrementing.
>
> I.e., it is possible to do something like:
>
> insert into foo( col, ... )
> values( coalesce( ( select max( col ) from foo ), 0 ) + 1, ... );
>
> and this is easily placed in a trigger.
>
> -tfo
>
> In article <[EMAIL PROTECTED]>,
> [EMAIL PROTECTED] (Tom Lane) wrote:
>
> > Justin Clift <[EMAIL PROTECTED]> writes:
> > > Oliver Elphick wrote:
> > >> I created a sequence using SERIAL when I created a table. I used the
> > >> same sequence for another table by setting a column default to
> > >> nextval(sequence).
> > >>
> > >> I deleted the first table. The sequence was deleted too, leaving the
> > >> default of the second table referring to a non-existent sequence.
> >
> > > This sounds like a serious bug in our behaviour, and not something we'd
> > > like to release.
> >
> > We will be releasing it whether we like it or not, because
> > nextval('foo') doesn't expose any visible dependency on sequence foo.
> >
> > (If you think it should, how about nextval('fo' || 'o')? If you think
> > that's improbable, consider nextval('table' || '_' || 'col' || '_seq').)
> >
> > The long-term answer is to do what Rod alluded to: support the
> > Oracle-style syntax foo.nextval, so that the sequence reference is
> > honestly part of the parsetree and not buried inside a string
> > expression.
> >
> > In the meantime, I consider that Oliver was misusing the SERIAL
> > feature. If you want multiple tables fed by the same sequence object,
> > you should create the sequence as a separate object and then create
> > the tables using explicit "DEFAULT nextval('foo')" clauses. Doing what
> > he did amounts to sticking his fingers under the hood of the SERIAL
> > implementation; if he gets his fingers burnt, it's his problem.
> >
> > > Specifically in relation to people's existing scripts, and also to
> > > people who are doing dump/restore of specific tables (it'll kill the
> > > sequences that other tables depend on too!)
> >
> > 7.3 breaks no existing schemas, because older schemas will be dumped
> > as separate CREATE SEQUENCE and CREATE TABLE ... DEFAULT nextval()
> > commands.
> >
> > 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
>
--
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 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] [GENERAL] Bug with sequence
On Thu, 2002-11-21 at 14:11, Bruce Momjian wrote: > Of course, those would be SQL purists who _don't_ understand > concurrency issues. ;-) Or they're the kind that locks the entire table for any given insert. -- Rod Taylor <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [GENERAL] Bug with sequence
"Thomas O'Connell" <[EMAIL PROTECTED]> writes: > It seems worth pointing out, too, that some SQL purists propose not > relying on product-specific methods of auto-incrementing. > I.e., it is possible to do something like: > insert into foo( col, ... ) > values( coalesce( ( select max( col ) from foo ), 0 ) + 1, ... ); > and this is easily placed in a trigger. ... but that approach is entirely unworkable if you want any concurrency of insert operations. (Triggers are a tad product-specific, too :-() regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [GENERAL] Bug with sequence
On 21 Nov 2002, Rod Taylor wrote: > On Thu, 2002-11-21 at 15:09, scott.marlowe wrote: > > On 21 Nov 2002, Rod Taylor wrote: > > > > > On Thu, 2002-11-21 at 14:11, Bruce Momjian wrote: > > > > Of course, those would be SQL purists who _don't_ understand > > > > concurrency issues. ;-) > > > > > > Or they're the kind that locks the entire table for any given insert. > > > > Isn't that what Bruce just said? ;^) > > I suppose so. I took what Bruce said to be that multiple users could > get the same ID. > > I keep having developers want to make their own table for a sequence, > then use id = id + 1 -- so they hold a lock on it for the duration of > the transaction. I was just funnin' with ya, but the point behind it was that either way (with or without a lock) that using something other than a sequence is probably a bad idea. Either way, under parallel load, you have data consistency issues, or you have poor performance issues. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [GENERAL] Bug with sequence
On 21 Nov 2002, Rod Taylor wrote: > On Thu, 2002-11-21 at 14:11, Bruce Momjian wrote: > > Of course, those would be SQL purists who _don't_ understand > > concurrency issues. ;-) > > Or they're the kind that locks the entire table for any given insert. Isn't that what Bruce just said? ;^) ---(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] [GENERAL] Bug with sequence
On Thu, 2002-11-21 at 15:09, scott.marlowe wrote: > On 21 Nov 2002, Rod Taylor wrote: > > > On Thu, 2002-11-21 at 14:11, Bruce Momjian wrote: > > > Of course, those would be SQL purists who _don't_ understand > > > concurrency issues. ;-) > > > > Or they're the kind that locks the entire table for any given insert. > > Isn't that what Bruce just said? ;^) I suppose so. I took what Bruce said to be that multiple users could get the same ID. I keep having developers want to make their own table for a sequence, then use id = id + 1 -- so they hold a lock on it for the duration of the transaction. -- Rod Taylor <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
