Re: [HACKERS] \xDD patch for 7.5devel
On Thu, 6 Nov 2003, Jason Godden wrote: On Thu, 6 Nov 2003 06:25 am, Markus Bertheau wrote: , 05.11.2003, 16:25, Tom Lane : +#define HEXVALUE(c) (((c)='a') ? ((c)-87) : (((c)='A') ? ((c)-55) : ((c)-'0'))) This seems excessively dependent on the assumption that the character set is ASCII. Why have you hard-coded numeric equivalents into this macro? What not ASCII compatible character sets are out there in use still today? Ah, yes - didn't even think about the character sets. If thats the case then octal needs attention as well because it makes a similar assumption. Peter I haven't looked at the code in question, but assuming the digits are contiguous and in order is safe, the C spec mandates that. Assuming that the letters are in order and contiguous is not safe. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Deferrable triggers
On Thu, 7 Nov 2003, Grant McLean wrote: So it would seem that if I include the clauses: on delete restrict on update restrict Then the 'deferrable' which follows is only applied to creates and not to updates or deletes. Since 'restrict' is the default, the clauses aren't adding any value and can be omitted. In my case, the SQL is generated for me by PowerDesigner. My workaround is to tweak the PowerDesigner output definition to not include this line. I have seen this behaviour in both 7.2 and 7.3. Is it a bug? Or am I misunderstanding something? Restrict is not the default, there is a difference between restrict and no action. In fact I believe the main point of restrict (which IIRC was added for sql99) is to allow you to have a deferred constraint that can do immediate checking of validity on pk changes. ---(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] Deferrable triggers
On Thu, 7 Nov 2003, Grant McLean wrote: On Fri, 2003-11-07 at 11:31, Stephan Szabo wrote: On Thu, 7 Nov 2003, Grant McLean wrote: So it would seem that if I include the clauses: on delete restrict on update restrict Then the 'deferrable' which follows is only applied to creates and not to updates or deletes. Since 'restrict' is the default, the clauses aren't adding any value and can be omitted. In my case, the SQL is generated for me by PowerDesigner. My workaround is to tweak the PowerDesigner output definition to not include this line. I have seen this behaviour in both 7.2 and 7.3. Is it a bug? Or am I misunderstanding something? Restrict is not the default, there is a difference between restrict and no action. In fact I believe the main point of restrict (which IIRC was added for sql99) is to allow you to have a deferred constraint that can do immediate checking of validity on pk changes. I was basing my reasoning on the CREATE TABLE documentation which says: NO ACTION Produce an error indicating that the deletion or update would create a foreign key constraint violation. This is the default action. RESTRICT Same as NO ACTION. So as you pointed out, RESTRICT is not the default, but according to the docs NO ACTION is the default and RESTRICT is the same as NO ACTION. Is the difference between the two documented anywhere? Hmm, I don't think so actually. I'm surprised that we hadn't had that mistake pointed out before. The restrict entry should mention the fact that it's non-deferring. To -hackers: Is it still safe to send small documentation patches for 7.4 at this point? ---(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] start of transaction (was: Re: [PERFORM] Help with
On Sun, 17 Nov 2003, Greg Stark wrote: Neil Conway [EMAIL PROTECTED] writes: What does BEGIN actually do now, from a user's perspective? I think you're thinking about this all wrong. BEGIN doesn't do anything. It's not a procedural statement, it's a declaration. It declares that the block of statements form a transaction so reads should be consistent and failures should be handled in a particular way to preserve data integrity. Given that declaration and the guarantees it requires of the database it's then up to the database to figure out what constraints that imposes on what the database can do and still meet the guarantees the BEGIN declaration requires. The more clever the database is about minimizing those restrictions the better as it means the database can run more efficiently. For what it's worth, this is how Oracle handles things too. On the command-line issuing a BEGIN following a COMMIT is just noise; you're _always_ in a transaction. A COMMIT ends the previous the transaction and implicitly starts the next transaction. But the snapshot isn't frozen until you first read from a table. The earlier portion of the described behavior is AFAICS not complient to SQL99 at least. COMMIT (without AND CHAIN) terminates a transaction and does not begin a new one. The new transaction does not begin until a transaction initiating command (for example START TRANSACTION, CREATE TABLE, INSERT, ...) is executed. The set of things you can do that aren't initiating is fairly small admittedly, but it's not a null set. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] logical column position
On Tue, 18 Nov 2003, Peter Eisentraut wrote: Christopher Kings-Lynne writes: BTW, one main consideration is that all the postgres admin apps will now need to support ORDER BY attlognum for 7.5+. But that is only really important if they've also used the ALTER TABLE RESHUFFLE COLUMNS feature. So if they make one alteration for 7.5, they need to do another. That seems fair. The ability to reshuffle and to get the correct ordering in a client app are separate unless we're going to assume that all access goes through that particular client. If one user uses psql and shuffles them, a second user using fooclient may not see the new ordering. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Anyone working on pg_dump dependency ordering?
On Sat, 22 Nov 2003, Andreas Pflug wrote: Christopher Kings-Lynne wrote: There are two levels (sort of) of dependency. The first is that whole classes of objects can be dependent on whole other classes. eg. databases depend on users, or ALL FK's can be dumped after ALL tables, etc.. It would make the dump more readable if you dumped those definite dependencies in that order, rather than shuffling everything up. I agree that dumping should be done class-wise (Tables, Functions, Views) whenever possible, but I don't agree on FKs dumped separately from the table. IMHO indexes and constraints belong to the table, and modifying the dump will be hard if a table's code is scattered all around. You're going to potentially have the constraints scattered in any case due to circular dependency chains. I'd think that having all the constraints in one place would be easier than trying to go through the list of tables that might be in a circular chain in order to find the constraints. ---(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
Re: [HACKERS] pg_restore and create FK without verification check
On Wed, 26 Nov 2003, ow wrote: People might be more interested in debating this topic with you if we hadn't discussed it at length just a couple months back. There wasn't consensus then that we had to offer an escape hatch, and you've not offered any argument that wasn't made before. I'm simply presenting a problem for which I currently do not see any solution (it's very important for us to be able to restore db within a reasonable amount of time). If there's no solution and none is planned, then we cannot use pgsql, can we? You can make your own solution, that's the nice thing about open source stuff. If you wanted to go the SET variable route to control alter time checks of CHECK and FOREIGN KEY constraints, it's almost certainly less than an hours worth of work. ---(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] pg_restore and create FK without verification check
On Wed, 26 Nov 2003, Tom Lane wrote: Quite honestly, I think they should check their foreign keys. In a partial restore situation there is no guarantee that the referenced table and the referencing table are being restored at the same time from the same dump. An override in that situation looks like a great tool for shooting yourself in the foot. People might be more interested in debating this topic with you if we hadn't discussed it at length just a couple months back. There wasn't consensus then that we had to offer an escape hatch, and you've not offered any argument that wasn't made before. I actually thought the majority in the past discussion thought that an escape hatch was a good idea, but that the discussion broke down in trying to determine what sort of hatch that might be (iirc, it got off into the general discussion of disabling constraints for normal operation as opposed to at alter time). ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_restore and create FK without verification check
On Wed, 26 Nov 2003, Tom Lane wrote: ow [EMAIL PROTECTED] writes: --- Tom Lane [EMAIL PROTECTED] wrote: Quite honestly, I think they should check their foreign keys. Generally speaking, I agree. The problem is that verification of FK constraint(s) may take too long, depending on the size of the db and other conditions. In my case, on test data, it takes about 1 hour to create tables and copy the data, then about 40 min to create indexes, then 4.5 hours to create one (1) FK constraint. If you're seeing this on 7.4, I'd like to see the details of the exact commands being issued. If it's not 7.4, it's not a relevant IIRC, he was. I think the thing causing the difference between his times and the ones we saw typically when doing the tests was that he didn't have an index on the fktable's referencing column. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Foreign key data type mismatches
On Mon, 1 Dec 2003, Peter Eisentraut wrote: I was just annoyed to find out that a foreign key doesn't check whether the referenced column has a sufficiently similar data type, it only checks whether an = operator exists. This masks schema design errors and typos. Should this be tightened up, for example using the castability characteristics between the two data types? Maybe, but IIRC the spec only requires comparability between the types involved. Since we don't use the same rules as the spec for that, existance of equality comparison was treated as the closest match to the requirement at the time. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Max number of rows in a table
On Mon, 1 Dec 2003, ow wrote: --- Tom Lane [EMAIL PROTECTED] wrote: Actually you can only have 4 billion SQL commands per xid, because the CommandId datatype is also just 32 bits. I've never heard of anyone running into that limit, though. Wouldn't the above put a limit on a number of records one could have in table? One have to use pg_dump/pg_restore to be able to upgrade between pgSql releases and/or to backup data. If one cannot backup data and/or upgrade between pg releases, then 4B records is the effective limit on the number of records in pgSql ... or am I missing something? I'd expect copy to be a single command, no matter how many rows were copied. ---(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
Re: [HACKERS] Encoding problem with 7.4
On Wed, 3 Dec 2003, E.Rodichev wrote: On Fri, 28 Nov 2003, Tom Lane wrote: E.Rodichev [EMAIL PROTECTED] writes: /e:2createdb test test | er | SQL_ASCII - Incorrect! (3 rows) Let's note than the last line is in fact completely incorrect. What's incorrect about it? You didn't ask for any other encoding than SQL_ASCII. It is incorrect, because database test is, really, in KOI8, NOT in SQL_ASCII in this example, as I explained in my mail. No, it isn't. As far as PostgreSQL is concerned the database is SQL_ASCII since you didn't override the default encoding at initdb time or at createdb time. You did choose LC_ values that seem to want KOI8, but locale and encoding are separate, if you want KOI8 encoding, you have to say so. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Encoding problem with 7.4
On Wed, 3 Dec 2003, E.Rodichev wrote: On Wed, 3 Dec 2003, Stephan Szabo wrote: On Wed, 3 Dec 2003, E.Rodichev wrote: On Fri, 28 Nov 2003, Tom Lane wrote: E.Rodichev [EMAIL PROTECTED] writes: /e:2createdb test test | er | SQL_ASCII - Incorrect! (3 rows) Let's note than the last line is in fact completely incorrect. What's incorrect about it? You didn't ask for any other encoding than SQL_ASCII. It is incorrect, because database test is, really, in KOI8, NOT in SQL_ASCII in this example, as I explained in my mail. No, it isn't. As far as PostgreSQL is concerned the database is SQL_ASCII since you didn't override the default encoding at initdb time or at createdb time. You did choose LC_ values that seem to want KOI8, but locale and encoding are separate, if you want KOI8 encoding, you have to say so. Yes, it is! *sigh* (the first order is true for ru_RU.KOI8-R, the latter one - for C). To summarize shortly: - initdb _without_ -E flag, but with ru_RU.KOI8-R environment; - createdb with any environment; - psql indicates SQL_ASCII; - sorting and upper/lowercasing are in ru_RU.KOI8-R, even with LC_* environment is set to C. Only the locale settings at initdb time matter. Changing the LC_* later is not going to change what the database does. Encoding and locale are separate (but related) and it is your responsibility to make sure the choices are consistent. If you do not specify an encoding, SQL_ASCII is used for the encoding. If the characters happen to line up appropriately for what your ru_RU.KOI8-R locale expects it'll even happen to appear to work for sorting and case changes (and things like isprint). Which part of this are you not understanding? ---(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] Encoding problem with 7.4
On Thu, 4 Dec 2003, E.Rodichev wrote: On Wed, 3 Dec 2003, Stephan Szabo wrote: Only the locale settings at initdb time matter. Changing the LC_* later is not going to change what the database does. Encoding and locale are separate (but related) and it is your responsibility to make sure the choices are consistent. If you do not specify an encoding, SQL_ASCII is used for the encoding. If the characters happen to line up appropriately for what your ru_RU.KOI8-R locale expects it'll even happen to appear to work for sorting and case changes (and things like isprint). Which part of this are you not understanding? Thank you, it is much more consistent answer. But again, the things are going not exactly the way you wrote. From your opinion the chain is data - encoding transform - locale transform - output It looks clean and reasonable. Encoding transform may be set during initdb or createdb (is it true?) But when locale transform is defined? In general unix flavor it should depend on LC_* setting (is it true?) As I described in my first posting the situation is different. Namely, locale setting now defines _encoding transform_ (and data representation in storage), but _locale transform_ doesnt depend on LC_*. The locale settings depend on LC_* at initdb time only. When the postmaster starts it sets the locale based on the stored values from initdb, not on the current environment. With an SQL_ASCII database being accessed from a client with client_encoding set to SQL_ASCII (which it should be if you aren't setting it) the byte values of a string are passed along with no conversion for the encoding. This means that from within one environment you should get back what you put in, so it might *look* like it's KOI8-R if that's what you're in, but it's not because someone accessing it from say an ISO8859-1 system may see something different. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Encoding problem with 7.4
On Thu, 4 Dec 2003, E.Rodichev wrote: On Wed, 3 Dec 2003, Stephan Szabo wrote: The locale settings depend on LC_* at initdb time only. When the postmaster starts it sets the locale based on the stored values from initdb, not on the current environment. With an SQL_ASCII database being accessed from a client with client_encoding set to SQL_ASCII (which it should be if you aren't setting it) the byte values of a string are passed along with no conversion for the encoding. This means that from within one environment you should get back what you put in, so it might *look* like it's KOI8-R if that's what you're in, but it's not because someone accessing it from say an ISO8859-1 system may see something different. As a result, the possibility to control encodings and locales looks as follows: initdb createdb psql Encoding: Y Y Y As a note you can change the *client* encoding from psql, not the *server* encoding. They're also two separate notions. Andrew already commented on the TODO list. You may also wish to look through the archives for a recent message from Peter E on the subject as he was looking into starting towards multiple collations and such. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] index scan with functional indexes
On Tue, 27 Jan 2004, Dave Cramer wrote: same answer davec=# show enable_seqscan; enable_seqscan off (1 row) davec=# explain analyze select * from url where fn_strrev(url) like '%beta12.html'; That's still an unanchored like clause, besides I think that would get urls that begin with lmth.21ateb. I think the condition you want would be: fn_strrev(url) like 'lmth.21ateb%' ---(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
Re: [HACKERS] [SQL] 7.4 - FK constraint performance
On Fri, 13 Feb 2004, Stephan Szabo wrote: On Fri, 13 Feb 2004, Tom Lane wrote: Stephan Szabo [EMAIL PROTECTED] writes: On Fri, 13 Feb 2004, Tom Lane wrote: I was looking at that last night. It seems like we could add a LIMIT at least in some contexts. In the case at hand, we're just going to error out immediately if we find a matching row, and so there's no need for FOR UPDATE, is there? I think there still is, because a not yet committed transaction could have deleted them all in which case I think the correct behavior is to wait and if that transaction commits allow the action and if it rolls back to error. Good point. Okay, we can't put in a LIMIT. But we could still hack the planner to prefer a fast-start plan by passing an out-of-band tuple fraction, for those RI plans where it's appropriate. That would not affect correctness. Right, I can try to look through the stuff you pointed at in the previous message over the weekend. It looks to me that we could make this available to SPI fairly simply by taking the current version of the following four routines: planner, pg_plan_query, _SPI_execute and SPI_prepare, renaming them and giving them a planning tuple fraction as a parameter, change references to the other routines to the new names and then making four new functions with the current names that call the renamed versions. In all the cases other than planner I think we can have the new version pass 0.0 and in the case of planner either 0.1 or 0.0 based on the isCursor parameter. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] No Timeout in SELECT..FOR UPDATE
On Tue, 10 Feb 2004, Anthony Rich wrote: In other words, by the time the second process has run the SELECT...FOR UPDATE statement, it's too late!! This second process is now locked forever, waiting for the Or until statement_timeout is reached if it's set to a non-zero value. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] Foreign key type checking patch
On Tue, 2 Mar 2004, Fabien COELHO wrote: Hello again, I turn the discussion to the dev list as it seems more appropriate. So about the proposed patch to warn if foreign key type do not match the target key: Stephan Szabo [EMAIL PROTECTED] writes: I'm really not sure that it makes sense to warn for the fk cases where the semantics should be correct (if they're not we need to fix it or make it an error) but in which an error might have been made by the user because the types are different given that it at least seems reasonable to me that the fk type is allowable to be a subset of the referenced type. I don't think simply different types is sufficient to be warning material. I can think of several cases where it might be reasonable for the types to be different. One case in particular that needs some thought is where the FK and referenced PK are domains on a common base type. I'm looking forward to see an example where: 1) the difference in type is actually needed by the application. 2) a simple warning about the issue would be considered harmful. Let me describe some examples where IMVVHO a simple warning make sense, although they are silently accepted by postgres at the moment: 1/ integers CREATE TABLE foo(fid INT4 NOT NULL PRIMARY KEY, ...); CREATE TABLE bla(fid INT2 REFERENCES foo, ...); The application will be fine till you enter fid=32767, and it inserts will fail in bla with fid=32768. Much later on. Which is fine if bla is meant to store a subset of the allowable foo values. It'd be really hard to say at bla creation time that there isn't going to be a bla2 which say takes an int4 check (fid=32768) which might be being used for dividing up the foo space between multiple tables. 2/ chars CREATE TABLE foo(fid VARCHAR(4) NOT NULL PRIMARY KEY, ...); CREATE TABLE bla(fid VARCHAR(2) REFERENCES foo, ...); bla will be able to reference all 2-letters keys of foo, but no more. If you have some counter in foo, it will fail when it turns 3 letters. Same as above. 3/ chars CREATE TABLE foo(fid VARCHAR(4) NOT NULL PRIMARY KEY, ...); CREATE TABLE bla(fid VARCHAR(8) REFERENCES foo, ...); declaring a larger size is not a problem here, however you will never be able to but any reference in bla larger than 4 as it must match its counter part in foo. So it is just a little bit stupid. This one is fairly pointless for the single column case but a multiple column match unspecified constraint could allow the full 8 characters if there's a second column which is null. 4/ time CREATE TABLE day(quand DATE NOT NULL PRIMARY KEY, ...); CREATE TABLE event(quand TIMESTAMP REFERENCES day, ...); The intent could be that events should refer to some day already registered in the base. Obviously it does work, because the = will cast to timestamp, to only the 00:00:00 timestamp will match a day. This one does seem fairly broken. 5/ domains CREATE DOMAIN posint AS int4 check(value0); CREATE TABLE foo(fid int4 primary key); CREATE TABLE bla(fid posint references foo); The intent here is that foo may contain negative numbers but that those rows won't be referenced by bla. This is similar to 1 and 2. ---(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
Re: [HACKERS] [PATCHES] Foreign key type checking patch
On Tue, 2 Mar 2004, Fabien COELHO wrote:
Hello Stephan,
CREATE TABLE foo(fid INT4 NOT NULL PRIMARY KEY, ...);
CREATE TABLE bla(fid INT2 REFERENCES foo, ...);
The application will be fine till you enter fid=32767, and
it inserts will fail in bla with fid=32768. Much later on.
Which is fine if bla is meant to store a subset of the allowable foo
values.
[...]
Sure. This is NOT my point. I totally agree with you that the above
example MAY BE what the user intends, and that it must be allowed.
However it may ALSO be a bug that will pop up later on.
Although it is POSSIBLE that this is fine, it is much more PROBABLE that
it is a bug, hence I just suggest to issue a mere simple basic plain
user-friendly little warning, what is quite different from issuing an
error.
Thus, the user has the information. He may chose to go on as that is what
was meant, or maybe check the stuff and correct it.
In postgres compilation, gcc uses the -Wall option to issue warnings about
correct C constructs that may hide application bugs. This is the
philosophy I'm suggesting here for this very small feature.
Dear user, what you ask is right, however it looks a little bit strange,
so I tell you just in case. I'm sure you're pretty happy that the gcc
developers put such features for basic programmers, and that you use
them;-) Why not allowing that kind of approach in postgres?
Because producing noise warnings often *lower* the amount of use you get
from real warnings. If one has to wade through useless messages to divine
the ones that are meaningful, overall many people just start ignoring all
warnings. I could be convinced that it is notice material, since people
who don't want to see it probably don't want to see the other notices
either, but warning seems way to strong to me.
Fundamentally, I don't see a huge difference between this and
select * from foo,bla where foo.fid=bla.fid;
where the same general constraints on meaningful values apply.
CREATE TABLE foo(fid VARCHAR(4) NOT NULL PRIMARY KEY, ...);
CREATE TABLE bla(fid VARCHAR(8) REFERENCES foo, ...);
declaring a larger size is not a problem here, however you will
never be able to but any reference in bla larger than 4 as it must
match its counter part in foo. So it is just a little bit stupid.
This one is fairly pointless
Isn't it what I'm saying?
for the single column case but a multiple column match unspecified
constraint could allow the full 8 characters if there's a second column
which is null.
I do not understand. I can't see how you can put 8 characters in a
reference which must match a 4 characters string.
Because in match unspecified, any column being null means the remainder of
the columns are not checked against the other table. IE given a key of
(fid, other), ('abcdefg', null) is valid in match unspecified even if the
other key can only have 4 characters.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] LIKE and Locale
On Wed, 31 Mar 2004 [EMAIL PROTECTED] wrote: I'm a little frustrated select * from mytable where mystring = 'foo'; Uses an index select * from mytable where mystring like 'foo'; Does not use an index. I know Tom is not to excited about this, but I think it is a serious problem. What really brings me to this is that I just installed 7.4.2. It I agree with Tom mostly. It'd be nice for cases to be better optimized in general, but optimizing basically degenerate cases seems futile especially when there's a generally better workaround (see below) is my first real deployment of PostgreSQL in about a year and a half. Unknown to me, the default for my latest DB was not type 'C' but en_US.iso885915 and thus no amount of work would have allowed a 'LIKE' to use an index without surrounding the index and query with some What about making an index with the whatever_pattern_ops opclass which IIRC is supposed to allow index use on LIKE even for anchored searches in non-C locales. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] LIKE and Locale
On Wed, 31 Mar 2004 [EMAIL PROTECTED] wrote: On Wed, 31 Mar 2004 [EMAIL PROTECTED] wrote: I'm a little frustrated select * from mytable where mystring = 'foo'; Uses an index select * from mytable where mystring like 'foo'; Does not use an index. I know Tom is not to excited about this, but I think it is a serious problem. What really brings me to this is that I just installed 7.4.2. It I agree with Tom mostly. It'd be nice for cases to be better optimized in general, but optimizing basically degenerate cases seems futile especially when there's a generally better workaround (see below) I'm not convinced that one optimization must de-optimize something else. But, given limited developer resources, optimizing degenerate sql is probably not the best use unless someone feels strongly enough about it to do it themselves. Also, I am suspicious of work arounds being suggested as norms. The workaround in this case is to make an index that works with LIKE even in non C locales. I qualified it as a workaround because potentially you might need two indexes on the field. However, given that it's not limited to non-wildcard containing strings, it's also more generally useful. is my first real deployment of PostgreSQL in about a year and a half. Unknown to me, the default for my latest DB was not type 'C' but en_US.iso885915 and thus no amount of work would have allowed a 'LIKE' to use an index without surrounding the index and query with some What about making an index with the whatever_pattern_ops opclass which IIRC is supposed to allow index use on LIKE even for anchored searches in non-C locales. At issue, would this require a change of the SQL query? If it requires changing the query, then PostgreSQL places too much of a burden on the application writer when it comes to supporting multiple databases. No, it involves making an index using the built-in whatever_pattern_ops operator class (which is mentioned in the operator class part of the index documentation I think, but probably needs better mention) Something like: CREATE INDEX indblah on tab(col text_pattern_ops) ---(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] Problems Vacuum'ing
On Fri, 2 Apr 2004, Alvaro Herrera wrote: On Fri, Apr 02, 2004 at 07:35:20PM -0500, Tom Lane wrote: [EMAIL PROTECTED] (Jim Seymour) writes: Again the difference: With WebObjects running, deleting rows and trying to vacuum immediately, even full, fails. Shut-down WebObjects and I can. WebObjects is evidently holding an open transaction. Ergo, anything deleted after the start of that transaction isn't vacuumable. You need to do something about the client-side logic that is holding an open transaction without doing anything ... But, if I read the code correctly, the oldest xmin vacuum cares about for a non-shared relation should be local to the database, shouldn't it? AFAICS it's the oldest transaction at the start of any of the transactions in this database, not the oldest transaction of any transaction in this database. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] make == as = ?
On Wed, 7 Apr 2004, Fabien COELHO wrote: From my point of view, my students come from a java first course, so they have to learn again some new syntax and new operators. Small stuff, but it can help to say same as java and go on to new concepts. Don't you want them to learn SQL? I want to teach them the concepts: relations, views, relationnal algebra, aggregation and so on, and how to build a resonnable schema from a real-world problem. Which are all good things. But even if you wanted to make java-like flavored SQL for such a purpose, I don't see how that ties into changing the default behavior of postgres flavored SQL. I do not consider whether the comparison is == or = as a key issue. It could be if your students think they know SQL and want to get a job doing it. I know that if I were to ask questions and had someone consistenly misuse == for = and such it would certainly raise doubts, just like many answers for a C question about a = a++ + ++a. Moreover, there are many SQL flavors around, so whatever the detailed syntax I learn them, it won't be the one they will have to face if the database they use is different. So why bother? Because hopefully by the time your students are out they know how to generalize their knowledge and be able to use what they've learned as the base point to learn the various flavors. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] make == as = ?
On Mon, 12 Apr 2004, Fabien COELHO wrote: Please see my previous e-mail about the value of international standards for educators. I read your email. I noticed that you want to educate me as an educator;-) I partially agree with your point. We have two words in French: education and formation. - education means teaching how to think right. so I teach programming. It may be with java or pascal or c#... the syntax is not important. what is important is types, functions, control structures... - formation means learning a specific skill. for this purpose, I could have java-programming, and java details are really important in this course. int and long differ, although in C int and long may or may not differ. However given that java's == and SQL = and java's and SQL AND have different semantics in some cases are you sure you want to teach them something that's actually incorrect in any case. Saying same as java is not any help to your students when they run into cases where string or date comparison with == is not the same as in java or foo.a=bar.a some condition containing 1/foo.a errors with a division by 0 when there are no 0 values in bar.a. ---(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] PostgreSQL configuration
On Mon, 12 Apr 2004, Bruce Momjian wrote: [EMAIL PROTECTED] wrote: The only other idea I can think of is to create a new pg_path.conf file. It would have the same format as postgresql.conf, but contain information about /data location, config file location, and perhaps pg_xlog location. The file would be created by special flags to initdb, and once created, would have to be used instead of pgdata for postmaster startup. That seems like a lot more risky, doesn't it? What is technically bad about my patch? Why is it bad? Everyone is offering something different than what I suggest. What is technically wrong with the patch? What can I alter to correct any concerns? I'm not a very good at politics, I sometimes tend to alianate people in discussions, but I am simply unable to understand why the features I suggest are not being considered as is. I have been using them for a while now, I find them very useful, and I have people downloading the patch from my site on a regular basis. Yet I an unable to say Here can we add this. The response is We don't like this for x, y, and z, but reasons x, y, and z already exist in one form or another in the current implementation. (1) What tangable harm comes to postgresql.conf from these features? (2) What problem (security, stabilitry, safety, etc.) is created by these features that doesn't already exist in some form already. (3) Isn't having this as an option better than making it normal for people to mess around in the PGDATA directory? (4) Isn't open source and UNIX phylosophy about providing capability not enforcing policy? I think the major problem with your -C -D idea is that you require the administrator to link the config file and data directory everytime you start the db, and that might be error-prone. Well, AFAICS the patch doesn't require that actually, it merely allows the separation. You can place the data directory in the configuration file and only use -C, you can place the configuration in the standard place under data and only use -D or you can specify both on the command line. I think the real potential harm would be from any current or future options where it'd be possible to have the system behave improperly when started up with the wrong value relative to a particular data directory. This would be especially bad if it was difficult or impossible to realize that it had happened and might then actually destroy data. I'm reasonably sure that such an option shouldn't be in an expected to be edited by admin configuration file, though. ---(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] make == as = ?
On Tue, 13 Apr 2004, Fabien COELHO wrote: Your Java students would be lulled into a false sense of understanding out of the belief that == in PostgreSQL would work exactly like == in Java ... when it wouldn't work the same in corner cases. For the class I have in mind, there are no corner cases, just concepts and basic practice. They are not going to be db developers, not even computer So no string comparisons? I know that's a mostly unused corner case and all, but... ;) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] make == as = ?
Dear Stephan, For the class I have in mind, there are no corner cases, just concepts and basic practice. They are not going to be db developers, not even computer So no string comparisons? I know that's a mostly unused corner case and all, but... ;) They survive to the idea that text/date/... are basic types in SQL. Maybe I'm lucky... they could prefer java references with new/equals...;-) If I take your example about details of vs AND semantics, while teaching programming concepts I'm not going to discuss the fact that is shortcut by the evaluator, as this is very specific. I'm not planing my students to know what i=++i+i++; could mean. And I wouldn't expect that in a programming concepts course. But, if you're going to (for example) say that, preincrement and postincrement work exactly as in C, you've got to realize that there's a chance a student will know that the i++ + ++i is undefined and expect it to be undefined in the language you're talking about. That's the problem with using shorthand phrases like exactly in X without the except ... ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Lexing with different charsets
On Wed, 14 Apr 2004, Tatsuo Ishii wrote:
I've spent some more time reading specs today. Together with Peter E's
explanataion (Thanks!) I think I've got a farily good understanding of the
parts talking about locales now.
My next question is about lexing. The spec says that one can use strings
of different charsets in the queries, like:
... WHERE field1 = _latin1'FooBar' and field2 = _utf8'Åäö'
In my understanding this was removed as of SQL:1999. I'm not sure
about SQL:2003 though.
AFAICS, it still basically has:
character string literal ::=
[ introducercharacter set specification ]
quote [ character representation... ] quote
[ { separator quote [ character representation... ] quote }... ]
---(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
Re: [HACKERS] License question
On Thu, 22 Apr 2004, Shachar Shemesh wrote: Tom Lane wrote: You can take some Postgres pieces and use them in a project with a different overall license, but those pieces are still under BSD license. But that's not the BSD license. [...] The BSD license, in contrast to PostgreSQL's, does NOT require me to copy license related texts around, only the copyrights themselves. It From the BSD license template on the OSI site: # Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer. # Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution. So, afaics, it *does* require you to copy the list of conditions along with the files. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [pgsql-advocacy] [HACKERS] What can we learn from MySQL?
On Fri, 23 Apr 2004, Robert Treat wrote: On Fri, 2004-04-23 at 05:22, Dennis Bjorklund wrote: On Fri, 23 Apr 2004, Shachar Shemesh wrote: When I ask about non-standard complience of Pg (turning unquoted identifiers to lowercase instead of uppercase, violating the SQL standard, and requring an expensive rewrite of clients), and I get the answer uppercase is ugly, I think something is wrong. I would love if someone fixed pg so that one can get the standard behaviour. It would however have to be a setting that can be changed so we are still backward compatible. that even if I write a patch to start migration, I'm not likely to get it in. Just changing to uppercase would break old code so such a patch should not just be commited. But would people stop a patch that is backward compatible (in the worst case a setting during initdb)? I'm not so sure they will. I know this to be true, but don't fully understand it... if our default behavior is to fold lower, and we change it to just fold upper... then in theory this shouldn't break anything since what used to be folder lower will now simply be folder upper. the only people who will have a problem are those who quote on one end but not the other, which is bad practice anyways... so i would say if your serious about it, make the patch as GUC case_folding for upper or lower and get a taste for what breaks inside the db. I've tried just changing the parser to unconditionally casefold to upper. First thing that happens is that initdb breaks. In addition, you have potential issues with comparisons against the catalog's versions of standard functions as such if you allow the case folding to be changed after the catalogs are setup. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [pgsql-advocacy] [HACKERS] What can we learn from MySQL?
On Fri, 23 Apr 2004, Shachar Shemesh wrote: Stephan Szabo wrote: I've tried just changing the parser to unconditionally casefold to upper. First thing that happens is that initdb breaks. In addition, you have potential issues with comparisons against the catalog's versions of standard functions as such if you allow the case folding to be changed after the catalogs are setup. That's not the migration path I was thinking of. What I was thinking of was: 1. Have a setting, probably per-session. Per database works too. 2. Aside from the folder upper and folder lower, have a third option. This is fold upper, if fails, fold lower. If succeeds, issue a warning. This should allow programs that rely on the folding (such as initdb) to be debugged during the transition period. If you can do this in a clean fashion without tromping all around the code, that'd be reasonable, however, istm that you'd need to either pre-fold both directions from the given identifier string and pass an extra copy around or pass the original identifier and its quoted status and fold on use. I think either of these are likely to be very intrusive for what essentially amounts to a transitional feature. In addition, I'm not sure that this would always work in any case, since some of those usages may be quoted identifiers that were once generated from a case-folded string (for example, looking up a name in the catalogs and quoting it). ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [pgsql-advocacy] [HACKERS] What can we learn from MySQL?
On Fri, 23 Apr 2004, Stephan Szabo wrote: On Fri, 23 Apr 2004, Shachar Shemesh wrote: Stephan Szabo wrote: I've tried just changing the parser to unconditionally casefold to upper. First thing that happens is that initdb breaks. In addition, you have potential issues with comparisons against the catalog's versions of standard functions as such if you allow the case folding to be changed after the catalogs are setup. That's not the migration path I was thinking of. What I was thinking of was: 1. Have a setting, probably per-session. Per database works too. 2. Aside from the folder upper and folder lower, have a third option. This is fold upper, if fails, fold lower. If succeeds, issue a warning. This should allow programs that rely on the folding (such as initdb) to be debugged during the transition period. If you can do this in a clean fashion without tromping all around the code, that'd be reasonable, however, istm that you'd need to either pre-fold both directions from the given identifier string and pass an extra copy around or pass the original identifier and its quoted status and fold on use. I think either of these are likely to be very intrusive for what essentially amounts to a transitional feature. In addition, I'm not sure that this would always work in any case, since some of those usages may be quoted identifiers that were once generated from a case-folded string (for example, looking up a name in the catalogs and quoting it). To clarify, I'm thinking about things where an application had gotten a quoted name and is now trying to use it where the object's canonical name was changed due to quoting changes. This only happens when quoting is inconsistently applied, but that's most of the problem. ---(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] Do we prefer software that works or software that looks good?
On Sat, 24 Apr 2004, Shachar Shemesh wrote: Tom Lane wrote: So what I'm holding out for is a design that lets me continue to see the current behavior if I set a GUC variable that says that's what I want. This seems possible (not easy, but possible) if we are willing to require the choice to be made at compile time ... but that sounds too restrictive to satisfy anybody ... what we need is a design that supports such a choice per-session, and I dunno how to do that. In other words, you are going to reject the simpler solutions that treat this as a transition problem, because of asthetic issue? Not even program design issue, mind you. Sounds strange to me, and also pretty much guarentees that this will never happen. That would be a shame. [ Tom, we know your opinion on the first part of the next paragraph, so you don't need to reply to that part. ;) ] Are we going to get rid of the current behavior entirely? If so, how are we going to handle issues like current databases with names like foo and FOO (and what if the name was given as foo)? If not, when can one set the folding options and how do we (in the long term) make the database work properly in both settings. Things like don't worry about the catalog entries don't fly when your standard functions are defined and looked up there. Depending on the answers to the above, we need to think about things like the transitional plans put forth. Do these plans actually help transition things. The fold up and down compare one then the other on a failure of the first may be fairly invasive changes, still has problems when quotes are used inconsistently and can also silently change behavior from old versions (on that database mentioned above, what does select * from foo do, is it the same as before?). These may or may not be huge issues and it may or may not be easily solvable, but these things need to be figured out IMHO before something can be considered a solution. ---(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] Do we prefer software that works or software that looks good?
On Sat, 24 Apr 2004, Shachar Shemesh wrote: Stephan Szabo wrote: Are we going to get rid of the current behavior entirely? I doubt that will be a good idea. You want to let applications created for previous versions of PostgreSQL continue to work. The idea, I think, is to have either a DB wide, or a session wide, option to have it either way. We may have to create a DB conversion tool, that converts a DB from one way to the other (and changes the case of functions, along the way). I'm going to assume that we're making the assumption that the user isn't going to try to do this on databases where it doesn't work? I think we've lost any information about quoting (was that named foo or foo?) so I don't think we can meaningfully make a current PostgreSQL app that's inconsistent about quoting work after the conversion. I think this is reasonable, but others may disagree. If so, how are we going to handle issues like current databases with names like foo and FOO (and what if the name was given as foo)? I think these are really rare. The conversion tool can warn about these cases. I agree, but we need to think about these cases (and any other wacky cases like this) so that we can warn about these cases rather than just not handle them. If not, when can one set the folding options and how do we (in the long term) make the database work properly in both settings. I don't think having the same DB work in both folding options is really a big issue. Having two databases on the same server, one this way and one the other is, however. You don't want to install two database servers, merely because you have two applications developed for two different PG versions. To be honest for me, it really doesn't feel much different than an app written for 7.2 and one written for 7.4 where the former uses things that were removed and so cannot be moved to 7.4 without changes. But that's just an option. Things like don't worry about the catalog entries don't fly when your standard functions are defined and looked up there. Answer above. Okay, under that world view (as opposed to on the fly), I think the only issues come in from shared catalogs, most importantly user names. This is certainly solvable, but we need to consider how we handle them when given to commands like ALTER USER or CREATE USER. The fold up and down compare one then the other on a failure of the first may be fairly invasive changes, In what way invasive? Right now AFAIK most of the case folding stuff pretty much happens in one place during normal queries and the identifier string you get out has the post-folding identifier for unquoted or the contained literal for quoted. In a system where you fold both directions, I can see a few obvious options: a) keep around the real identifier that was given plus whether or not it was quoted. b) keep around both folded identifiers (for non-quoted names). c) fold one direction then the other. This may potentially do the wrong thing in some locales I don't know how you were planning to handle this issue so I don't know if any of these scenarios were what you were thinking of or if you had a better idea. I think all of these potentially may need to touch at least some places where the identifier is used and I think all of them need information that is not AFAIK currently returned from scan.l which means passing that information along (which may change stuff along the way). still has problems when quotes are used inconsistently The main issue, as far as I'm concerned, is not with PG apps that need to be ported to the new scheme. I don't have any qualm with never deprecating the lowercase folding. This, of course, puts a burden on utilities that work as infrastructure to always quote or always not-quote (depending on exact semantics), but that, I believe, is solveable. My problem is with applications written for other, more standard complient, databases, and with porting these into PG. As such, if the app uses inconsistent quoting, it today relies on uppercase folding, and will not have any problem. That sounds like a plus for having the option for full uppercase folding. I have no problems with that (I wouldn't have even looked at initdb if I didn't want to give an option for uppercase folding) but I'm not convinced it actually is a plus for the transitional setting. An app written for full uppercase should work in said option without needing the transitional setting and in fact the transitional setting might do the wrong thing for said application. The only place I can see transitional being useful is for upgrading and testing our own stuff (make the server work, make pg_dump work, etc) and for applications moving from supporting only the lowercase to supporting both or only upper. For the former, it doesn't need to be a truly supported feature if it's going in in a single version, and for the latter, I think as many of the wierd change
Re: [HACKERS] Do we prefer software that works or software that
On Sat, 24 Apr 2004, Stephan Szabo wrote: On Sat, 24 Apr 2004, Shachar Shemesh wrote: Stephan Szabo wrote: Things like don't worry about the catalog entries don't fly when your standard functions are defined and looked up there. Answer above. Okay, under that world view (as opposed to on the fly), I think the only issues come in from shared catalogs, most importantly user names. This is In fact the above is incomplete. You also need to be able to do the right thing when creating a database with a different setting than its template database. I'm not really sure how to define right thing however if things have been added to the template db. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] An Index Scanning Solution question
On Thu, 20 May 2004, Bruno Wolff III wrote: On Wed, May 19, 2004 at 15:17:01 +0200, Atesz [EMAIL PROTECTED] wrote: I'd like to ask why the index scaning can't move on an index in multi-order directions (For exapmle: 1.column: forward, 2.column: backward and 3.column: forward again)? So I wouldn't have to use so many indexes. Has somebody tried to implement this idea in Postgres or is there a more difficult reason in the postgres implementation which cause this defect? Because there is only one order on an index. So you can only go forward and backwards over all of the columns/functions. If you're willing to make multiple visits you might be able to scan past and back but I don't know how that'd work for our indexes. ---(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
Re: [HACKERS] Reconstructing FKs in pg_dump
On Thu, 26 Sep 2002, Tom Lane wrote: Patrick Welche's recent problems (see pgsql-general) point out that the old CREATE CONSTRAINT TRIGGER syntax that optionally omits a FROM table clause doesn't work anymore --- the system *needs* tgconstrrelid to be set in an RI constraint trigger record, because the RI triggers now use that OID to find the referenced table. (The table name in the tgargs field isn't used anymore, mainly because it's not schema-aware.) This means that RI trigger definitions dating back to 7.0 (or whenever it was that we fixed the pg_dump bug about not dumping tgconstrrelid) don't work anymore. There are a couple things I think we should do. One: modify the CREATE CONSTRAINT TRIGGER code to try to extract a foreign relation name from the tgargs if FROM is missing. Without this, we have no hope of loading working FK trigger definitions from old dumps. Two: modify pg_dump to extract a name from the tgargs in the same fashion. I'd rather have pg_dump do this than the backend, and this will at least make things better in the case where you're using a 7.3 pg_dump against an older database. I'd worry about doing things only to pg_dump since that'd still leave people that did use the old dump in the dark and there'd be nothing even indicating a problem until they did something that used the constraint. Even a notice for a missing FROM would be better (although at that point how far is it to just fixing the problem). I can look at it this weekend (since it probably was my bug in the first place) unless you'd rather do it. However, if we are going to put that kind of knowledge into pg_dump, it would only be a small further step to have it dump these triggers as ALTER TABLE ADD CONSTRAINT commands instead. Which would be a lot better for forward compatibility than dumping the raw triggers. Wasn't there still some question about the fact that ATAC causes a check of the constraint which for large tables is not insignificant. I don't remember if there was any consensus on how to deal with that. ---(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] Reconstructing FKs in pg_dump
On Thu, 26 Sep 2002, Tom Lane wrote: Stephan Szabo [EMAIL PROTECTED] writes: However, if we are going to put that kind of knowledge into pg_dump, it would only be a small further step to have it dump these triggers as ALTER TABLE ADD CONSTRAINT commands instead. Which would be a lot better for forward compatibility than dumping the raw triggers. Wasn't there still some question about the fact that ATAC causes a check of the constraint which for large tables is not insignificant. I don't remember if there was any consensus on how to deal with that. Hmm, good point. That's probably why we didn't go ahead and do it already... Maybe we should just put the lookup hack into the backend's CREATE CONSTRAINT TRIGGER code and leave it at that. That seems reasonable. And probably not too hard. There might still be cases where we can't get it, and I think we probably should at least throw a notice on the create in that case, the admin will *probably* ignore it, but if they want to fix the situation right away they can. ---(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] 7.2.3?
On Sat, 28 Sep 2002, Bruce Momjian wrote: I have seen no discussion on whether to go ahead with a 7.2.3 to add several serious fixes Tom has made to the code in the past few days. Are we too close to 7.3 for this to be worthwhile? Certainly there will be people distributing 7.2.X for some time as 7.3 stabilizes. The vacuum thing is big enough that there should be since as always people aren't going to move immediately forward with a major version change. ---(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] Request for suggestions
I've been working on kludging a working for update barrier style lock (*) for reads using HeapTupleSatisfiesDirty to test accessibility to make the foreign keys work better. I'm fairly close to getting a testable kludge for the fk/noaction cases for people to check real sequences against (since I'm using simple examples as I think of it). At some point I'm going to want to do something that's less of a kludge which might hopefully also let me remove the whole hack in tqual.c (right now the hack's gotten worse since I use the value to specify what kind of check to do). In addition, I'm not 100% sure how to proceed on the non-noaction/restrict cases, since I'd kind of want to do a dirty read to find candidate rows for the update/delete which gets into having heap_delete fail for example since the row is invisible. For the lock above I made a new for ... specifier for the statement to separate the behavior, but I'm not sure something like that is really a good idea in practice and I'm a little worried about changing the logic in heap_delete (etc) for invisible rows in any case. So, I'm looking for suggestions on the best way to proceed or comments that I'm going about this entirely the wrong way... :) (*) - It blocks on the transaction which has a real lock on the row, but does not itself get a persistent lock on it. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] (Followup) Request for suggestions
I wasn't particularly clear (sorry, wrote the message
1/2 right before bed, 1/2 right after getting up) so
I'm going to followup with details and hope that
I'm more awake.
A little background just in case there are people
that haven't looked.
Right now, foreign key checks always default to using
HeapTupleSatisfiesNow to check for the validity of
rows and uses for update to do the locking. I believe
that we can use something like the lock suggested
by Alex Hayard which does not actually lock the row
but only waits for concurrent modification that
actually has a lock to finish, except that to do
so would make the constraint fail, unless checks
for changes to the primary key actually could see
uncommitted changes to the foreign key table. Unless
the old row being worked on was made by this transaction
in which case you shouldn't need to do a dirty check.
To that end, I've put together some modifications
for testing on my local system (since I'm not 100%
sure that the above is true for all cases) where
the primary key triggers actually use
HeapTupleSatisfiesDirty using the
ReferentialIntegritySnapshotOverride hack (making
it contain three values, none, use now, use dirty)
and added a for foreign key specifier to selects
which has the semantics of Alex's lock. The code
in heap_mark4fk is called in effectively the same place
as heap_mark4update in the execution code. Basically
if (...) { heap_mark4update() } else { heap_mark4fk() }.
However, the heap_mark4update code (which I cribbed
the mark4fk code from) doesn't like getting rows which
HeapTupleSatisfiesUpdate says are invisible (it throws an
invalid tid error IIRC).
Right now, I'm waiting for the transaction that made
the row to complete and returning HeapTupleUpdated if
it rolled back and HeapTupleMayBeUpdated if it didn't,
but I know that's wrong.
I think the logic needs to be something like:
If the row is invisible,
If the row has xmax==0, wait for xmin to complete
If the transaction rolled back, ignore the row.
Otherwise, check to see if someone else has locked it.
If so, go back to the the HeapTupleSatisfiesUpdate test
Otherwise, work with the row as it was.
Otherwise,
If xmax==xmin, we want to ignore the row
Otherwise, -- can this case even occur? --
Wait on xmax per normal rules of heap_mark4update
but I'm very fuzzy on this.
In addition, at some point I'm going to have to modify
the actual referential actions (as opposed to no action)
to do a similar check, which means I'm going to want
a delete or update statement which needs to wait
on uncommitted transactions to modify the rows. It looks
like heap_delete and heap_update also will error
on rows that HeapTupleSatisfiesUpdate says are invisible.
For heap_mark4fk it was reasonably safe to change the
result==HeapTupleInvisible case since it was new code
that I was adding, but I'm a bit leery about doing
something similar to heap_delete or heap_update.
Is the coding for result==HeapTupleInvisible in those
functions meant as a defensive measure that shouldn't
occur?
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Damn slow query
On Wed, 9 Oct 2002, Magnus Naeslund(f) wrote: Hello, i've got this query that's really slow... Figure this: testdb= select now() ; select gid from bs where gid not in ( select x from z2test ); select now(); Per FAQ suggestion, try something like select gid from bs where not exists (select * from z2test where z2test.x=bs.gid); to see if it is faster. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] foreign key problem with pg_dump under 7.3b2
On 15 Oct 2002, John Halderman wrote: I'm currently using 7.3b2 for test and development. I ran into a problem using a dumped schema from pg_dump. After importing the dumped schema, any delete or update involving a foreign key results in a relation 0 does not exist error. I noticed that all my foreign key declarations were moved from the table create to separate statements at the bottom of the dump file. Thanks in advance for any insight into this problem you can lend. If the data has moved from earlier versions (I think 7.0.x) there was a bug in an older pg_dump that dropped a piece of information (the related table) and the loss would be carried along. That information is now used rather than the name passed in the args so said dumps break on b2. Current sources should fill in the missing information whenever possible. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Strange cost computation?
On 29 Oct 2002, Ives Landrieu wrote: Hi, Can anybody explain the following results when using EXPLAIN, one time with enable_seqscan=on and one time with enable_seqscan=off. What I don't understand is that the nodes created are the same (index scan, seq scan), but the costs differ. Enable seq scan actually just sets a large cost disbenefit to seq scans. Is alias96.STATUS indexed? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Strange cost computation?
On Wed, 30 Oct 2002, Stephan Szabo wrote: On 29 Oct 2002, Ives Landrieu wrote: Hi, Can anybody explain the following results when using EXPLAIN, one time with enable_seqscan=on and one time with enable_seqscan=off. What I don't understand is that the nodes created are the same (index scan, seq scan), but the costs differ. Enable seq scan actually just sets a large cost disbenefit to seq scans. Boy, that sentence sure didn't make sense. enable_seqscan=off actually gives a large cost disbenefit to seqscans. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] float output precision questions
On Wed, 30 Oct 2002, Pedro Miguel Frazao Fernandes Ferreira wrote:
In C this is possible:
#include stdio.h
#include stdlib.h
int main(void)
{
double v;
char a[30];
v=1.79769313486231571e+308;
printf( Stored double number: %25.18g\n,v);
sprintf(a,%25.18g,v);
printf(Converted to string: %s\n,a);
v=atof(a);
printf(Converted from string to double: %25.18g\n,v);
}
AFAICT, this is not guaranteed to give you the same representation that
you're using in the initializer however.
Using standard PostgreSQL query output it would not be possible to get
this number, which has representation as a double.
I fetched the PostgreSQL source from Debian, changed
src/backend/utils/adt/float.c to do sprintf(ascii, %25.18g, num)
instead of sprintf(ascii, %.*g, DBL_DIG, num), compiled and installed.
Now I can get the number as is.
I understand that if people insert a value of 1.1 in a double, they want
to get 1.1 without knowing that in fact the stored number is
1.10009. But do you understand that if some people insert,
for example, a value of 1.79769313486231571e+308 they dont want to get
1.79769313486232e+308 which does not compare equal (in Matlab or C) to
the first ? This is a bug.
I disagree to some extent. I'm not sure it's meaningful to expect that
(what if the database and the client are on different architectures) in
general. In any case, you're effectively going from decimal
representation to double to decimal representation (the string you used
to insert it - internal representation - string used to output it) and
that's only guaranteed to be correct up to DBL_DIG digits as far as I can
tell. I think it'd be nice to have an option to get more digits for those
sorts of applications, however.
This would probably make sense as an option, so why don't you look at the
past discussions and see if you can come up with a solution that keeps
everyone happy (and preferably implement it, but...) :)
but ???
, but I realize that you might not be interested in doing such. (I figured
the last part was implied)
I have a sugestion:
To have parameters, say DOUBLE_FORMAT and FLOAT_FORMAT, which could have
option values of 'SHORT' and 'LONG'.
Option 'SHORT' would be default and produce the standard sprintf(ascii,...
Option 'LONG' would produce sprintf(ascii, %25.18g, num).
Other way would be to have number parameters to be used in the sprintf
calls, in place of 25 and 18, in the format string.
From what Tom said, something similar was suggested and there were issues
brought up. I don't know what they were, since I wasn't personally
terribly interested, but it should be in the archives. If there were any
concerns, you'll probably need to deal with those as well.
---(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] Request for supported platforms
On 26 Oct 2002, Doug McNaught wrote: Tom Lane [EMAIL PROTECTED] writes: Doug McNaught [EMAIL PROTECTED] writes: make[3]: Entering directory `/home/doug/src/pgsql/src/backend/utils/mb/conversion_procs/ascii_and_mic' gcc -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -fpic -I../../../../../../src/include -c -o ascii_and_mic.o ascii_and_mic.c ascii_and_mic.c:19: syntax error before `extern' ascii_and_mic.c:21: syntax error before `extern' That should be fixed as of now. OK, compile went fine, but I get multiple regression test failures: test geometry ... FAILED select_views ... FAILED foreign_key ... FAILED limit... FAILED plpgsql ... FAILED copy2... FAILED temp ... FAILED domain ... FAILED rangefuncs ... FAILED prepare ... FAILED without_oid ... FAILED conversion ... FAILED truncate ... FAILED alter_table ... FAILED I have attached a gzipped copy of regression.diffs. Let me know if I can supply any other help. The geometry one looked like rounding issues. Did you run out of space on where the data directory was mounted? At least some of the other errors were complaining about no space left on device. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] float output precision questions
On Tue, 29 Oct 2002, Peter Eisentraut wrote: Pedro Miguel Frazao Fernandes Ferreira writes: Is there a way to set query output precision to maximum precision ? For the type of application I mentioned this is crucial. People want to get the 'same' numbers, from querys or dumps, as they inserted them. How do you define maximum precision and same? With simple test programs in C, using two digits more than DBL_DIG for printf specifier, it's easy for me to find numbers that change decimal string representation in the decimal representation - double - decimal representation conversion(*). The final double you get from the second conversion should be the same as the first, but is that what you need or do you need a stronger guarantee than that? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] float output precision questions
On Wed, 30 Oct 2002, Pedro Miguel Frazao Fernandes Ferreira wrote: Stephan Szabo wrote: On Tue, 29 Oct 2002, Peter Eisentraut wrote: Pedro Miguel Frazao Fernandes Ferreira writes: Is there a way to set query output precision to maximum precision ? For the type of application I mentioned this is crucial. People want to get the 'same' numbers, from querys or dumps, as they inserted them. How do you define maximum precision and same? With simple test programs in C, using two digits more than DBL_DIG for printf specifier, it's easy for me to find numbers that change decimal string representation in the decimal representation - double - decimal representation conversion(*). The final double you get from the second conversion should be the same as the first, but is that what you need or do you need a stronger guarantee than that? When I say same I am talking about having a number 'stored' in double format in some client, inserting it in PostgreSQL float8 field and get it to the client as it was before: [Some client] (insert) [PostgreSQL] (query) [Some client] (double number a)(float8 number)---(double number b) same is so that a==b is true. With current float8 output this is not allways true. I believe this should allways be true for numbers which are originally stored in double format. The problem is that there are two competing needs here. One is the above, the other other is that you get something that has the same decimal representation (within the float's ability to store the number). Right now the system does the latter since for most people, that seems to be the guarantee they want. This would probably make sense as an option, so why don't you look at the past discussions and see if you can come up with a solution that keeps everyone happy (and preferably implement it, but...) :) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] float output precision questions
On Wed, 30 Oct 2002, Pedro M. Ferreira wrote: Stephan Szabo wrote: On Wed, 30 Oct 2002, Pedro Miguel Frazao Fernandes Ferreira wrote: I understand that if people insert a value of 1.1 in a double, they want to get 1.1 without knowing that in fact the stored number is 1.10009. But do you understand that if some people insert, for example, a value of 1.79769313486231571e+308 they dont want to get 1.79769313486232e+308 which does not compare equal (in Matlab or C) to the first ? This is a bug. I disagree to some extent. I'm not sure it's meaningful to expect that (what if the database and the client are on different architectures) in general. In any case, you're effectively going from decimal representation to double to decimal representation (the string you used to insert it - internal representation - string used to output it) and that's only guaranteed to be correct up to DBL_DIG digits as far as I can tell. I think it'd be nice to have an option to get more digits for those sorts of applications, however. In the previous email example, in C, I was going from decimal to double and so on, but this is not the case when I do some simulation. In this case it will allways be from Matlab double to PostgreSQL float8 and from libpq PQgetvalue() string to Matlab double. The example was just a x86 number example where (got the string from Matlab double) query output would fail. I was just responding to it being a bug. I don't think that expecting a float-db-float (double-db-double) giving the same double is always safe when you assume that the PostgreSQL machine might be on a system with different guarantees about precision. In practice, it's probably not a big deal. , but I realize that you might not be interested in doing such. (I figured the last part was implied) ok! :) No problem. I have seen the GUC thing in the source (guc.c etc...) and it does not look too dificult. It has lots of examples in the code itself. What I am saying is that I can do it if pg-people agree on the (some) way to do it. Yeah, I didn't think it'd be hard, but sometimes people are unable or unwilling to do C code for things. I looked at some of these emails and it seemed to me that the problem was that Tom did'nt want a parameter that would force people to know about printf number formatting. I think the first solution above (the SHORT and LONG way) is simple, maintains usual output as default and enables 'maximum' precision at request. That seems reasonable then, Tom'll probably give any other objections he might have if he has any. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [ADMIN] Security question : Database access control
On Tue, 22 Oct 2002, Igor Georgiev wrote: edit *pg_hba.conf * # Allow any user on the local system to connect to any # database under any username, but only via an IP connection: host all 127.0.0.1 255.255.255.255trust # The same, over Unix-socket connections: localall trust what about reading pg_hba.conf comments? localall md5 Ok, but my question actually isn't about pg_hba.conf comments, i read enough but what will stop root from adding this lines or doing su - postgres ?? Not much really. But given that they have access to the raw data files, preventing them access to the server doesn't gain you that much if they really want to get the data. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [ADMIN] [HACKERS] Security question : Database access control
On Fri, 25 Oct 2002, Igor Georgiev wrote: Next your going to ask what will stop root from stopping your PostgreSQL, compiling a second copy with authentication disabled and using your data directory as it's source :) He he somebody can blow up ur home with C4, but this don't stop you from locking ur door !?! It's more equivalent to giving him the keys and then wondering why the lock doesn't stop him. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [SQL] Problem: Referential Integrity Constraints lost
On Wed, 6 Nov 2002, Achilleus Mantzios wrote: Hi i think a hit a major problem on 7.2.1. I run 3 systems with postgresql 7.2.1. Its a redhat 7.1 for development, a redhat 7.3 for production and a FreeBSD 4.6.1RC2 for testing. After long runs (with periodic (daily) vacuum analyze's) i noticed that some of the triggers that implement referential integrity constraints just disapeared. Some of these triggers were still present on the FreeBSD system (which has been idle for a month or so), whereas on the linux systems they were absent. Has any one have a clue?? Hmm, you haven't done anything like cluster or an incomplete dump and reload have you? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Question about row visibility after locks
I've been wondering (and probably should look through the code, but figured asking would be faster) if there's any guarantee that I'll see rows inserted by a transaction I'm waiting on in the middle of a query. Basically, if I've got a select that's running using HeapTupleSatisfiesDirty so that I can see uncommitted rows and I block in the middle for another transaction (waiting to see if it commits the row I'm looking at) and that transaction inserts another row that meets my search criteria am I guaranteed to see that second row in all cases? Basically for the foreign keys, I'm doing something like the above, but I'm worried that if the waiting for transaction does something like: insert ... -- At this point the select runs delete ... insert ... that I won't see the last row and I'll let through an update or delete that should have failed. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Question about row visibility after locks
On Sat, 9 Nov 2002, Tom Lane wrote: Stephan Szabo [EMAIL PROTECTED] writes: Basically, if I've got a select that's running using HeapTupleSatisfiesDirty so that I can see uncommitted rows and I block in the middle for another transaction (waiting to see if it commits the row I'm looking at) and that transaction inserts another row that meets my search criteria am I guaranteed to see that second row in all cases? No; it might get inserted into a page you've already scanned over. You'd have to restart your scan if you wanted that. Okay, that's what I figured, but wanted to check before doing something like that. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [GENERAL] PostgreSQL JDBC and sub-select
On Sat, 9 Nov 2002, snpe wrote: Hello, I work with JDeveloper and PostgreSQL JDBC and I have one problem. I get error : sub-SELECT in FORM must have an alias I can't change SQL command, but it is internal JDeveloper command Is it SQL standard (must have alias) or PostgreSQL specific ? It looks to me to be standard. I think the appropriate portion of the grammar is: table reference := derived table [ AS ] correlation name [ left paren derived column list right paren ] derived table := table subquery correlation name := identifier ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] RI_FKey_check: foreign key constraint blocks parallel
On Wed, 13 Nov 2002, Peter Schindler wrote: But, if a lot of inserts happens into the child table and there is a mix of short and long running transactions, the likelihood of blocking is very high, even the inserts are independent and everything is ok (prim. key etc.). This is even more extreme, the smaller parent table is. FYI, I've tried the same with Oracle and there is no such problem. The insert in the second session will come back immediately without blocking, though it will still maintain the integrity from other txns. I wonder if there is a lower level way to maintain the locking and having the same behavior as oracle. So, instead of using a SELECT ... FOR UPDATE, using some pg function to lock a row with a different mode? I've been working on something of the sort. I've got a test patch (against about 7.3b2) that I'm trying to validate which cases it does and does not work for. I'm still looking for more volunteers if you've got a dev system you're willing to use. :) Right now, I know that it has a hole that lets through invalid data in one case that it got while trying to fix a deadlock case. Hopefully in the next week or so I'll have figured out a way around it. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] create or replace view
On Thu, 14 Nov 2002, Scott Shattuck wrote: It might just be me but it seems that this discussion is missing the point if we believe this request is about saving some characters. I don't think it is. I think it's about being able to write simple SQL scripts that don't produce errors when you use the syntax below in an adminstration or development script and the object doesn't exist: I think there are two groups of people who have different ideas of what this functionality is supposed to do. From my understanding of the discussions on create or replace function, the point really was to do an in place modification to not need to drop and recreate dependent objects. Note that afaik you also can't change the return type of a function in a create or replace if it already exists with a different return type. The other usage is useful, but I don't think it was the intended way to be used. I use it that way too, but if I get an error on a create or replace I do the more involved version (dump dependents if necessary, drop cascade, create, edit dump, restore). If we're concerned with this change from a consistency perspective, look at triggers. The programmer drops a function and the triggers relying on that function go to hell. Sure, and if we said you can't drop the function because triggers might break then it'd parallel what we're saying here -- in effect we know better than you do what you want. Or to use M$ terminology we know where you want to go today ;). In fact, afaict 7.3 does exactly this unless you use drop cascade. I don't think that the past way was particularly easier, with needing to dump/restore dependent objects in order to make them work again. I think of it like constraints, as much as you can you enforce the constraint. It's possible that the next statement will make the sequence work for the constraint, but you don't wait to find out. B. We want to treat people who are interested in PostgreSQL with respect at all times, keeping in mind that we communicate with them not only through this forum, but through the code we write for them. This is always true. Even if we forget sometimes. :) ---(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] create or replace view
On Thu, 14 Nov 2002, snpe wrote: Problem is when I want change view (or functions) with a lot of dependecies I must drop and recreate all dependent views (or functions) - I want add only one column in view I don't know if solution hard for that. Well, doing create or replace as a drop/create might very well do the same thing, and even if it got the same oid, we'd have to be really sure that nothing would misbehave upon receiving that extra column before allowing it for purposes of avoiding recreation of dependencies. ---(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] create or replace view
On Fri, 15 Nov 2002, Zeugswetter Andreas SB SD wrote: Problem is when I want change view (or functions) with a lot of dependecies I must drop and recreate all dependent views (or functions) - I want add only one column in view I don't know if solution hard for that. I do not see how adding a column to a view would invalidate dependent objects. (Except an object that uses select *, in which case the writer of the object explicitly states that he can cope with changing column count and order). I'm not sure, but can all the places that currently save a plan deal with getting a longer rowtype than expected? I'd guess so due to inheritance, but we'd have to be absolutely sure. It'd also change the return type for functions that are defined to return the composite type the view defines. ---(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] RI_FKey_check: foreign key constraint blocks parallel
On Fri, 15 Nov 2002, Manfred Koizar wrote:
On Wed, 13 Nov 2002 14:22:51 -0800 (PST), Stephan Szabo
[EMAIL PROTECTED] wrote:
Right now, I know that it has a hole that lets through invalid data
Stephan, your patch has been posted to -general (Subject: Re:
[GENERAL] Help..Help...). Is this version still valid?
I have a newer version of it on my machine, but I was still sending out
that version of the patch. :( Thanks for letting me know before even more
people got a version that was broken. :)
For anyone working with the patch, you need to fix the lines below as
noted by Manfred. This is mostly unrelated to the hole mentioned in the
quoted message above (it's a bug that with the bug you actually partially
fill the hole but instead deadlock). I wonder if there were any other
stupdities in there.
void
heap_mark4fk_lock_acquire(Relation relation, HeapTuple tuple) {
[...]
/* try to find the list for the table in question */
This part of the patch works, if the list
(a) is initially empty or
(b) already contains relid or
(c) starts with a table relid.
while (ptr!=NULL) {
if (relidptr-table) {
ptr=ptr-next;
oldptr=ptr;
// AFAICT above two lines should be swapped ...
}
else
break;
}
... otherwise
(d) if the new relid is to be inserted between two existing entries,
we get two items pointing to each other
(e) if the new relid is the last table in the list, we lose the
whole list.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] RI_FKey_check: foreign key constraint blocks parall
On Fri, 15 Nov 2002, Mikheev, Vadim wrote: Just wonder how are you going to implement it - is it by using some kind of read-locks, ie FK transaction locks PK to prevent delete (this is known as pessimistic approach)? About two years ago we discussed with Jan optimistic approach with using dirty reads, when PK/FK transactions do not check existence of FK/PK untill constraint should be checked (after statement processed for immediate mode, at the commit time/ set constraint immediate for deferred constraints). So, at the check time, FK transaction uses dirty reads to know about existence/status of PK: 1. No PK - abort. 2. PK (inserted?/)deleted/updated/selected for update by concurrent transaction P - wait for P commit/abort (just like transactions do for concurrent same-row-update); go to 1. 3. Else (PK exists and no one changing it right now) - proceed. PK transaction does the same: 1. No FK - proceed. 2. FK inserted/updated/selected for update by concurrent transaction F - wait for F commit/abort; go to 1. This would be more in MVCC style -:) Right now, it's similar to the above, but only one direction is doing the dirty reads right now. I don't do the dirty reads on the fk transactions right now. It'll still see delete/update/selected for update on a row that would have otherwise existed for the transaction, but not see the new rows (I'd like to switch it to dirty both directions, but I'm having enough trouble with deadlocks as it is). Or, at least that's the intention behind the code if not the actual effect. It gets rid of the concurrency issues of two fk transactions, but it doesn't get rid of deadlock cases. T1: insert into fk values (1); T2: delete from pk; T1: insert into fk values (1); shouldn't need to deadlock. The lock stuff is actually more like an un-lock to make it not wait on the second T1 statement. It's broken, however, as I just thought of some more things it doesn't handle correctly. Oh well. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Ideas needed: How to create and store collation tables
On Mon, 18 Nov 2002, Peter Eisentraut wrote: A collation table essentially consists of a mapping 'character code - weight' for every character in the set and some additional considerations for one-to-many and many-to-one mappings, plus a few feature flags. How would a user go about creating such a table? CREATE COLLATION foo ( ... 1 lines of data ... ); or would it be preferrable to store the table in some external file and then have the call simply be, say, CREATE COLLATION foo SOURCE 'some file'; I'd say the latter makes more sense, but would it be better to use CREATE COLLATION foo FROM EXTERNAL 'some file'; where we say valid implementation defined collation names are references to files of the appropriate type? Secondly, because each collation table depends on a particular character encoding (since it is indexed by character code), some sort of magic needs to happen when someone creates a database with a different encoding than the template database. One option is to do some mangling on the registered external file name (such as appending the encoding name to the file name). Another option is to have the notional pg_collate system catalog contain a column for the encoding, and then simply ignore all entries pertaining to encodings other than the database encoding. The SQL92 CREATE COLLATION seems to create a collation for a particular character set, so the latter seems more appropriate to me, especially if we plan to support the full range of SQL's character set/collation/padding attributes at some point. ---(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
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
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
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] 7.4 Wishlist
On Fri, 29 Nov 2002, Christopher Kings-Lynne wrote: Just out of interest, if someone was going to pay you to hack on Postgres for 6 months, what would you like to code for 7.4? What would you guys do? Even if it isn't feasible right now... Hmm, mine would probably be fixing foreign keys (along with making it work with inheritance and match partial) and check constraints with subselects. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 7.4 Wishlist
On 30 Nov 2002, Neil Conway wrote: On Sat, 2002-11-30 at 12:47, Stephan Szabo wrote: check constraints with subselects. Have we decided how this would even work? Last I heard, Tom still had some major reservations about the practicality of implementing these -- for example, would you re-evaluate all constraints that SELECT from a table when the table changes? You'd have to either do it in all cases or come up with something that was smart enough to limit the cases to some extent based on the expression. I doubt that it'd perform terribly well, especially at first. I can't see any justification for doing it as insert/update on main table only since by my reading of the spec the constraint is logically checked at the end of each statement (or transaction) even if we would normally not do so in practice when we know the constraint shouldn't be violated. Of course this was in the general set of, if I had months and months and nothing else to do (like work) then I'd want to look at it because I think it'd be useful. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 7.4 Wishlist
On Sun, 1 Dec 2002, Tom Lane wrote: Stephan Szabo [EMAIL PROTECTED] writes: On 30 Nov 2002, Neil Conway wrote: Have we decided how this would even work? Last I heard, Tom still had some major reservations about the practicality of implementing these -- for example, would you re-evaluate all constraints that SELECT from a table when the table changes? You'd have to either do it in all cases or come up with something that was smart enough to limit the cases to some extent based on the expression. I doubt that it'd perform terribly well, especially at first. Note that you can get the stupid semantics (run the subselect only when the constrained table changes) today: just hide the subselect in a user-defined function that's called from the constraint expression. Or put the whole check in a trigger instead of using a constraint. I don't think we should bother with direct support of subselects in constraints unless we can come up with an implementation that is significantly better than what you can accomplish with these workarounds. Well, the problem is that user defined triggers trying to do the real semantics for update/insert on the other tables of the constraint seem to me like they'll have the same issues as foreign keys do currently, either you'll be forced to write something too strong and deadlock alot, or you'll write something too weak and end up with constraint violations with concurrent transactions unless you basically write a very low level C function to do it for you. I guess this, since in general, the non-action foreign keys really are just check constraints with a subselect effectively. ---(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] 7.4 Wishlist
On Mon, 2 Dec 2002, Magnus Naeslund(f) wrote: Stephan Szabo [EMAIL PROTECTED] wrote: If you use a scalar subquery, yes, but I think a subselect in from would help, maybe something like (if you want the total count) select table_name.id, sum(sum_col)||'/'||t2.count from table_name, (select count(*) as count from table_name) as t2 group by table_name.id,t2.count; or (if you want each count the counter per group) either select id, sum(sum_col)||'/'||count(*) from table_name group by id; or select table_name.id, sum(sum_col)||'/'||t2.count from table_name, (select id, count(*) as count from table_name group by id) as t2 where table_name.id=t2.id group by table_name.id,t2.count; Give it up already, i was MAKING A POINT, not trying to make an optimized count(*) thing :) There are other examples that you cannot get around, that will be evaluated more than once when a local user variable would make it not need to. For most cases sticking an expression in a subselect in from works to not re-evaluate it (unless it's correlated in which case I don't think local variables help). It might not be as optimal in all cases, but probably is sufficient in most cases. ---(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] 7.4 Wishlist
On Tue, 3 Dec 2002, Magnus Naeslund(f) wrote:
Now convert this query so that it only evaluates the date_part thing
ONCE:
That's not a good idea as long as t.stamp varies from row to row. ;)
Perhaps once per row, maybe... :)
select t.id, date_part('days',now()-t.stamp) from table_name t where
date_part('days',now()-t.stamp) 20;
Potentially I think something like this would do it:
select t.id, t.foo from (select id, date_part('days', now()-stamp)
as foo from table_name except select null, null) as t where foo20;
It's not really an optimization given the required except, but if there
was some way to tell the system not to push clauses down into a subselect
you wouldn't even need that.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 7.4 Wishlist
On Tue, 3 Dec 2002, Bruce Momjian wrote:
Magnus Naeslund(f) wrote:
Good!
Now convert this query so that it only evaluates the date_part thing
ONCE:
select t.id, date_part('days',now()-t.stamp) from table_name t where
date_part('days',now()-t.stamp) 20;
I hope you all are kidding me in not seeing the real issue i'm trying to
show here.
Does this work?
SELECT t.id, x.date_part
FROM table_name t, (select date_part('days',now()-t.stamp)) as x
WHERE x.date_part 20;
No, because the values in x are correlated to the particular row in
table_name, so I think you have to make it one big subselect in from. In
addition the optimizer is smart enough to push the condition down in
most cases which I think will force the function to be called twice unless
you trigger one of its cases that prevent it from doing so. That's an
optimizer hint I'd like (don't push conditions into this subquery,
really...). :)
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 7.4 - TODO : alter table drop foreign key
On Thu, 5 Dec 2002, Dan Langille wrote: We support alter table add foreign key. How about supporting alter table drop foreign key? - he said as he went to drop a foreign key It seems to work for me on my 7.3b2 system with alter table table drop constraint constraint name; ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 7.4 - TODO : alter table drop foreign key
On Thu, 5 Dec 2002, Dan Langille wrote: On 5 Dec 2002 at 8:20, Stephan Szabo wrote: On Thu, 5 Dec 2002, Dan Langille wrote: We support alter table add foreign key. How about supporting alter table drop foreign key? - he said as he went to drop a foreign key It seems to work for me on my 7.3b2 system with alter table table drop constraint constraint name; Premature send.. sorry How was that FK added? How did you determine the constraint name? alter table table add constraint name foreign key ... How would you do that if the FK was added with the following syntax? alter table table add foreign key (column) references othertable (othercolumn) on update cascade on delete cascade; IIRC, the constraint will get an automatic name of the form $n in such cases. I believe if you do a \d on the table, it gives the name in the constraint definitions (on one of mine i get: Foreign Key constraints: $1 FOREIGN KEY (a) REFERENCES qqq(a) ON UPDATE CASCADE ON DELETE NO ACTION Where $1 is the name of the constraint. ---(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] 7.4 - TODO : alter table drop foreign key
On Thu, 5 Dec 2002, Dan Langille wrote: Found the solution: drop trigger RI_ConstraintTrigger_4278488 on watch_list_staging; Actually there are three triggers for the constraint. You may have dangling triggers on the other table of the constraint. It's one on the table the constraint's defined on and two on the referenced table. Given that the FK in question did not have a name to start with, I concede that it would be difficult to code DROP FOREIGN KEY. What about supporting ALTER TABLE table ADD FOREIGN KEY keyname ... which at present we don't? That would then make dropping the FK a simple coding issue? ISTM, that's ALTER TABLE table ADD CONSTRAINT name FOREIGN KEY ... which should be there in any 7.x. And the drop constraint for foreign keys (and the \d display stuff) is new in 7.3. ---(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] 7.4 - TODO : alter table drop foreign key
On Thu, 5 Dec 2002, Dan Langille wrote: On 5 Dec 2002 at 9:02, Stephan Szabo wrote: On Thu, 5 Dec 2002, Dan Langille wrote: Found the solution: drop trigger RI_ConstraintTrigger_4278488 on watch_list_staging; Actually there are three triggers for the constraint. You may have dangling triggers on the other table of the constraint. It's one on the table the constraint's defined on and two on the referenced table. Given that the FK in question did not have a name to start with, I concede that it would be difficult to code DROP FOREIGN KEY. What about supporting ALTER TABLE table ADD FOREIGN KEY keyname ... which at present we don't? That would then make dropping the FK a simple coding issue? ISTM, that's ALTER TABLE table ADD CONSTRAINT name FOREIGN KEY ... which should be there in any 7.x. Agreed. But the syntax is different. If we are supporting ALTER TABLE table ADD FOREIGN KEY without a name, why not support it with a name? When we talk about ALTER TABLE ADD FOREIGN KEY we're being imprecise, so I think that might be why we're talking past each other here. Technically the syntax in question is: ALTER TABLE table ADD table constraint definition where CONSTRAINT name is an optional leading clause in a table constraint definition. ADD FOREIGN KEY is a shorthand for a foreign key constraint (technically unnamed). Thus you can also say things like: ALTER TABLE table ADD CONSTRAINT blah CHECK (foo!=0); to make a named check constraint. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 7.4 - TODO : alter table drop foreign key
On Thu, 5 Dec 2002, Dan Langille wrote: On 5 Dec 2002 at 9:31, Stephan Szabo wrote: When we talk about ALTER TABLE ADD FOREIGN KEY we're being imprecise, so I think that might be why we're talking past each other here. Technically the syntax in question is: ALTER TABLE table ADD table constraint definition where CONSTRAINT name is an optional leading clause in a table constraint definition. ADD FOREIGN KEY is a shorthand for a foreign key constraint (technically unnamed). Understood. What about allowing a named foreign key? I haven't checked the RFCs Here's a part of what SQL92 (draft) has to say about table constraint definitions: table constraint definition ::= [ constraint name definition ] table constraint [ constraint attributes ] table constraint ::= unique constraint definition | referential constraint definition | check constraint definition constraint name definition ::= CONSTRAINT constraint name referential constraint definition ::= FOREIGN KEY left paren referencing columns right paren references specification 11.6 Syntax Rules 2) If constraint name definition is not specified, then a con- straint name definition that contains an implementation- dependent constraint name is implicit. The assigned con- straint name shall obey the Syntax Rules of an explicit con- straint name. In our case, the implementation dependent naming scheme is I believe $n where n is the maximum one already there for that table +1 I would guess. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Postgres syscalls
On Fri, 13 Dec 2002, [iso-8859-1] Diego T. wrote: Hello I'm an Italian student of computer science at University of Rome La Sapienza. I've to analyze some daemons which run under root privileges with a tool developed by my departement. This tool intercepts critical syscalls, like Execve, and blocks illegal invocation of that primitives (E.g. Execve(/bin/sh)) performed by a daemon which runs under root privileges. This approach blocks buffer overflow attacks before they can complete (or I hope so). Now, the problem is that postgres doesn' t run under root privileges and that the tool intercepts only the syscalls invoked by a process with root privileges. Is possible to force postgres to run under root privileges? How can be done? I know my request is anomalous but i've to do this for my laboratory project course. I should be very grateful if you'll answer as soon as possible. You could probably just hack out the checks in main/main.c and recompile, but postgres does call system and such to do things (like create databases) so I'm not sure it'd be terribly useful for you. ---(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] Bug in pg_get_constraintdef (for deferrable constraints)
On Wed, 1 Jan 2003, Bruce Momjian wrote:
Tom Lane wrote:
Bruce Momjian [EMAIL PROTECTED] writes:
I see the values being stored on constriant creation, but not being used
anywhere:
I believe the values that actually get inspected at runtime are the
tgdeferrable and tginitdeferred fields in pg_trigger. The columns in
pg_constraint are just copies of these.
It is not real clear to me whether it should be allowed to alter the
deferrability status of a foreign-key constraint --- is that in the spec?
The big problem is that while pg_dump's dump_trigger() looks at
tginitdeferred and dumps accordingly, pg_get_constraintdef doesn't look
at tginitdeferred, and therefore doesn't record the requirement as part
of ALTER TABLE ADD CONSTRAINT.
pg_get_constraintdef should probably be looking at condeferrable
and condeferred in the pg_constraint row it's looking at. Maybe something
like the attached.
*** pgsql/src/backend/utils/adt/ruleutils.c 2003-01-01 15:03:35.0 -0800
--- pgsql/src/backend/utils/adt/ruleutils.c.new 2003-01-01 15:02:32.0 -0800
***
*** 688,693
--- 688,704
}
appendStringInfo(buf, ON DELETE %s, string);
+ if (!conForm-condeferrable) {
+ appendStringInfo(buf, NOT);
+ }
+ appendStringInfo(buf, DEFERRABLE);
+ if (conForm-condeferred) {
+ appendStringInfo(buf, INITIALLY DEFERRED);
+ }
+ else {
+ appendStringInfo(buf, INITIALLY
+IMMEDIATE);
+ }
+
break;
}
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Autocommit off and transaction isolation level
On Thu, 2 Jan 2003, Tom Lane wrote: Michael Paesold [EMAIL PROTECTED] writes: Now setting autocommit=off the set transaction isolation level command does not show any effect: billing=# set autocommit to off; SET billing=# set transaction isolation level serializable; SET SET does not start a transaction block, so this will not work. You must use an explicit BEGIN before setting TRANSACTION ISOLATION LEVEL. You might instead set default_transaction_isolation to get the behavior I think you are looking for. The overall behavior appears to be against spec, but I figure this was discussed at the time the set transation was added. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Bug in pg_get_constraintdef (for deferrable
On 2 Jan 2003, Rod Taylor wrote: I think I initially forgot those options, and Stephans patch seems to be everything required -- though the psql display is a little more cluttered. IIRC, theoretically only initially immediate deferrable actually needs to specify both clauses (initially deferred guarantees deferrable and not deferrable doesn't need an initially at all). It seemed like that was getting too cute though for a quick patch. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] UNION result
On Wed, 15 Jan 2003, Tatsuo Ishii wrote: Does anybody know: select 1.0 union select 1; or select 1 union select 1.0; should return 1 or 1.0? Hmm, I think (but am not sure) that the spec bit in SQL92 that addresses this is 9.3 Set operation result data types based on the text in 7.10 query expression. It seems to say to me that should always be an approximate numeric (if 1.0 is an approximate numeric). Am I reading that right? ---(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] UNION result
On Wed, 15 Jan 2003, Tom Lane wrote: Stephan Szabo [EMAIL PROTECTED] writes: Hmm, I think (but am not sure) that the spec bit in SQL92 that addresses this is 9.3 Set operation result data types based on the text in 7.10 query expression. It seems to say to me that should always be an approximate numeric (if 1.0 is an approximate numeric). Am I reading that right? Yeah, the existing algorithm for determining CASE/UNION result datatype does not have any smarts about preferring numeric over integer, which is what's missing to handle this case per-spec. There has been some speculation about junking the existing code (which is mostly driven by a hardwired notion of preferred types) in favor of something driven by the contents of pg_cast. (At least I recall a message or two about it, but I can't find it in the archives at the moment.) It seems to me that the spec has a fairly hardwired notion of what types should come out given the sql types. The biggest problems that I can see are that it doesn't extend well to an extensible type system and that in alot of cases it doesn't seem to allow conversions (for example select CAST(1 as float) union select '1' - if you were to allow conversions the rules seem to be ambiguous) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] UNION result
On Wed, 15 Jan 2003, Tom Lane wrote: Stephan Szabo [EMAIL PROTECTED] writes: It seems to me that the spec has a fairly hardwired notion of what types should come out given the sql types. The biggest problems that I can see are that it doesn't extend well to an extensible type system and that in alot of cases it doesn't seem to allow conversions (for example select CAST(1 as float) union select '1' - if you were to allow conversions the rules seem to be ambiguous) Agreed, we can't make use of the spec's rules as anything much better than spiritual guidance. But it'd be nice if the rules we use match what the spec says for the cases covered by the spec. In particular, I think it's intuitively correct that numeric union int should yield numeric no matter which order you write them in. Actually, now that I look at the code, 7.3 does in fact get this case right, because we did add a check on pg_cast: it will prefer a type over another if there is an implicit cast in only one direction. The OP may have been fooled by this behavior: regression=# select 1 union select 1.0; And I was fooled by select '1' union select 1; because I'd forgotten that '1' isn't exactly a character string constant. select '1'::text union select 1; properly errors. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [mail] Re: [HACKERS] Win32 port patches submitted
On Tue, 21 Jan 2003, Al Sutton wrote: I would back keeping the windows specific files, and if anything moving the code away from using the UNIX like programs. My reasoning is that the more unix tools you use for compiling, the less likley you are to attract existing windows-only developers to work on the code. I see the Win32 patch as a great oppertunity to attract more eyes to the code, and don't want the oppertunity to be lost because of the build requirements. The problem is that when either side (unix developer or windows developer) wants to do anything that changes the build procedure, the other side breaks until someone makes the appropriate changes on the other build. Unless some committer is going to commit to looking over patches to dsp files and making makefile changes and vice versa or we were to require that anyone that wants to change build procedure must make both sets of changes, I'd think this is going to be a mess. And in the latter case, I think you're going to lose developers as well. ---(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] SET NULL on NOT NULL field
On Tue, 28 Jan 2003, Christopher Kings-Lynne wrote: I just noticed you can do this: create table blah ( a not null references test on delete set null ) Should that be prevented? It shouldn't be too hard to test for really... Maybe, although I don't think the spec prevents it. In practice I'd guess it ends up being a more expensive way of saying no action. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] SET NULL on NOT NULL field
On Tue, 28 Jan 2003, Bruno Wolff III wrote: On Mon, Jan 27, 2003 at 21:23:01 -0800, Stephan Szabo [EMAIL PROTECTED] wrote: On Tue, 28 Jan 2003, Christopher Kings-Lynne wrote: I just noticed you can do this: create table blah ( a not null references test on delete set null ) Should that be prevented? It shouldn't be too hard to test for really... Maybe, although I don't think the spec prevents it. In practice I'd guess it ends up being a more expensive way of saying no action. No. You end up not being able to delete the referenced keys. I tested this in 7.3 and you get the following message when you try it: ERROR: ExecUpdate: Fail to add null value in not null attribute col1 Right, and NO ACTION shouldn't allow you delete the referenced keys either except that it gives you a meaningful error message as well. :) I think you may have been confusing NO ACTION and CASCADE. ---(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] location of the configuration files
On Thu, 13 Feb 2003, mlw wrote: Robert Treat wrote: On Thu, 2003-02-13 at 09:23, mlw wrote: I deal with a number of PG databases on a number of sites, and it is a real pain in the ass to get to a PG box and hunt around for data directory so as to be able to administer the system. What's really annoying is when you have to find the data directory when someone else set up the system. You realize that the actual code feature doesn't necessarily help this case, right? Putting configuration in /etc and having a configuration file option on the command line are separate concepts. I think the feature is worthwhile, but I have some initial condition functionality questions that may have been answered in the previous patch, but I don't remember at this point. Mostly these have to deal with initial creation. Does the user specify an output location to initdb, do they just specify a data dir as now where the configuration goes but then they need to move it somewhere, does initdb now do nothing relating to configuration file and the user should make one on his own. Related, is the admin expected to have already made (say) /etc/postgresql to stick the config in and set the permissions correctly (since initdb doesn't run as root)? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] location of the configuration files
On Thu, 13 Feb 2003, mlw wrote: Stephan Szabo wrote: On Thu, 2003-02-13 at 09:23, mlw wrote: I deal with a number of PG databases on a number of sites, and it is a real pain in the ass to get to a PG box and hunt around for data directory so as to be able to administer the system. What's really annoying is when you have to find the data directory when someone else set up the system. You realize that the actual code feature doesn't necessarily help this case, right? Putting configuration in /etc and having a configuration file option on the command line are separate concepts. Re-read my statement and yours about the case you were mentioning. ;) Sure, putting the files in /etc lets you find them easily. However, if you're doing things like finding configuration made by someone else and said configuration isn't in /etc (which if they wanted to they could do now with symlinks I believe - yes symlinks aren't a complete solution, but I think they're reasonable on most of our current ports) then you still have to search the system for the configuration file, except now it might not even be postgresql.conf. That's why I said the two issues aren't the same. I think the feature is worthwhile, but I have some initial condition functionality questions that may have been answered in the previous patch, but I don't remember at this point. Mostly these have to deal with initial creation. Does the user specify an output location to initdb, do they just specify a data dir as now where the configuration goes but then they need to move it somewhere, does initdb now do nothing relating to configuration file and the user should make one on his own. Related, is the admin expected to have already made (say) /etc/postgresql to stick the config in and set the permissions correctly (since initdb doesn't run as root)? My patch only works on the PostgreSQL server code. No changes have been made to the initialization scripts. The patch declares three extra configuration file parameters: hbafile= '/etc/postgres/pg_hba.conf' identfile='/etc/postgres/pg_ident.conf' datadir='/RAID0/postgres' The command line option is a capital 'C,' as in: postmaster -C /etc/postgresql.conf I have no problem leaving the default configuration files remaining in the data directory as sort of a maintenance / boot strap sort of thing, so I don't see any reason to alter the installation. As for this feature helping or not, I think it will. I think it accomplishes two things: (1) Separates configuration from data. (2) Allows an administrator to create a convention across multiple systems regardless of the location and mount points of the database storage. (3) Lastly, it is a familiar methodology to DBAs not familiar with PostgreSQL. I agree on all these points (I think the feature is worthwhile, but...). I just wonder if we were going to do this, we might as well look at all of the various things people want and decide what we want to do, for example, people commenting on default configuration locations through configure, how does this interact with what we have now, etc. I'd rather have a month spent arguing out a behavior rather than just adding a new behavior that we'll need to possibly revisit again in the future. :) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Foreign key quandries
Going through the issues in doing dirty reads in foreign keys I've come up with a few cases that I'm fairly uncertain about how to handle with regards to deadlocks and figured I should ask for advice because I think I'm missing something painfully obvious, but don't get large enough blocks of time to think about it to figure out what it is. I'd thought maybe it'd be enough to say which type of thing on which constraint and use that to basically say that we don't need to wait on a transaction that's waiting on us due to a modification to the other table, but AFAICS that lets through a bad case: T1: insert into fk values (2); T2: delete from pk where key=3; T2: delete from pk where key=2; T1: insert into fk values (3); If T1 doesn't wait in this case, you can get into a case where a bad row is inserted into fk if you then have: T1: delete from fk where key=2; T1: commit; Now there's no row to make the second delete fail but transaction 2 still can't commit due to the fk row with key 3. I'd then thought of doing something based on what row/value transaction 2 was waiting on, but that has problems. Given a foreign key with no referential actions and a sequence like: Transaction 1 inserts into the foreign key table a row with a referencing key of 2. Transaction 1 checks the foreign key Transaction 2 deletes the primary key rows having keys 2 and 3 Transaction 1 inserts another row into the foreign key table with a referencing key of 2. Transactions 1 and 2 start checking the foreign key. AFAICS, transaction 2 needs to wait since there's already a row it can see in the foreign key table that's not yet committed (so it doesn't know if the delete works or not). We can tell transaction 1 that it doesn't need to wait on transaction 2 because transaction 1 is inserting a value that transaction 2 will see in its check, thus we're saved from the first case. However, this has the potential to deadlock if we had for example, inserted a foreign key table row of 3 rather than 2 as the second insert in transaction 1 and the delete check for 2 went first. If we knew that it was also going to be checking the 3 rows, we'd be safe, but then we've got to keep that information in some way that's visible to other transactions AFAICS. And, if the checks were done in the order delete check for 3, delete check for 2(t2 blocks), insert check for 3, we'd be back in the state of the first example. :( ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] division by zero
On Mon, 10 Mar 2003, Tom Lane wrote: Merlin Moncure [EMAIL PROTECTED] writes: The only other solution is a #ifdef win32 around places that potentially use integers in the divisor and do some nasty hacking. Well, it seems to me that we have two different issues to worry about: 1. There are only about half a dozen places for a user-triggered division by zero to occur (the div and mod functions for int2, int4, int8; have I missed anything?). It would not be very painful to insert It's unlikely to come up in practice, but chardiv as well for char. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Another naive question, inheritance and foreign key
On Wed, 19 Mar 2003, Shridhar Daithankar[EMAIL PROTECTED] wrote: Just stumbled upon this. Is it correct to conclude that foreign keys are not inherited from this text? Yes. If you want more info, check out the archives. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Deadlock situation using foreign keys (reproduceable)
On Thu, 11 Apr 2002, Mario Weilguni wrote: As promised here's an example of deadlock using foreign keys. create table lang ( id integer not null primary key, name text ); insert into lang values (1, 'English'); insert into lang values (2, 'German'); create table country ( id integer not null primary key, name text ); insert into country values (10, 'USA'); insert into country values (11, 'Austria'); create table entry ( id integer not null primary key, lang_id integer not null references lang(id), country integer not null references country(id), txt text ); insert into entry values (100, 1, 10, 'Entry 1'); insert into entry values (101, 2, 11, 'Entry 2'); insert into entry values (102, 1, 11, 'Entry 3'); transaction A:begin; transaction A:update entry set txt='Entry 1.1' where id=100; transaction B:begin; transaction B:update entry set txt='Entry 3.1' where id=102; transaction A:update entry set txt='Entry 2.1' where id=101; transaction A:deadlock detected Please see past disussions on the fact that the lock grabbed is too strong. I'm going to (when I get time to work on it) try out a lower strength lock that Alex Hayward made a patch for that should limit/prevent these cases. Thanks for sending a nice simple test case to try against :) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Index Scans become Seq Scans after VACUUM ANALYSE
On Tue, 16 Apr 2002, Michael Loftis wrote: I know I know, replying to myself is bad and probably means I'm going insane but thought of one other thing... Realistically the system should choos *ANY* index over a sequential table scan. Above a fairly low number of records any indexed query should be much faster than a seqscan. Am I right, or did I miss something? (wouldn't be the first time I missed something)... Right Because the validity information is stored with the row and not the index you have to read rows for any potential hit in the index. Depending on the clustering of the table, the width of the rows and the percentage of the table being hit by the scan (or estimated to be hit) you may read most or all of the table as well as the index and be paying a penalty for doing it randomly as opposed to be sequentially. IIRC, there are some settings in the configuration that let you play around with the relative costs the estimator uses (the random page cost and cpu costs for dealing with index entries and such). ---(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] Odd(?) RI-trigger behavior
On Thu, 18 Apr 2002, Tom Lane wrote: This particular test involves a table with a foreign-key reference to itself, ie, it's both PK and FK. What apparently is happening is that the two RI triggers are now being fired in a different order than before. While either of them would have detected an error, we now get the other error first. Does this bother anyone? It seems to me that the old code essentially had no guarantee at all about the order in which the triggers would fire, and so it was pure luck that the regression test never showed the other message. That's probably a bad thing even if I doubt that it'd ever come up the other way barring changes to other regression tests in practice. Forcing an order probably helps with this case anyway. With the modified code, because we load the triggers by scanning an index on (tgrelid, tgname), it is actually true that triggers are fired in name order. We've had requests in the past to provide a well-defined firing order for triggers --- should we document this behavior and support it, or should we pretend it ain't there? Didn't someone (Peter?) say that the mandated firing order was based on creation order/time in SQL99? ---(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] Odd(?) RI-trigger behavior
On Thu, 18 Apr 2002, Tom Lane wrote: Stephan Szabo [EMAIL PROTECTED] writes: Didn't someone (Peter?) say that the mandated firing order was based on creation order/time in SQL99? It does say that: The order of execution of a set of triggers is ascending by value of their timestamp of creation in their descriptors, such that the oldest trigger executes first. If one or more triggers have the same timestamp value, then their relative order of execution is implementation-defined. However, this strikes me as fairly brain-dead; it's unnecessarily hard to control the order of trigger execution. You have to drop and recreate triggers if you want to insert a new one at a desired position. Worse, if you create several triggers in the same transaction, they'll have the same timestamp --- leaving you right back in the implementation-defined case. But if you want to make your rearrangement atomically with respect to other transactions, you have little choice but to drop/recreate in one xact. Looks like a catch-22 to me. ISTM we had discussed this before and concluded that name order was a more reasonable definition. Nobody had got round to doing anything about it though. (Indeed my current hack was not intended to provide a predictable firing order, it just fell out that way...) I agree that name is better, I wasn't sure if we'd reached a consensus on it or if the conversation drifted away due to the fact that noone was looking at it at the time. ---(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] DROP TABLE hangs because of same table foreign key
On Thu, 2 May 2002, Louis-David Mitterrand wrote: Hi, On 7.2.1 debian-unstable PG hangs when trying to drop a table which contains a field referencing another field in the same table as a foreign key. Is it legal/orhtodox to use a references on another field of the same table? Should be. Were there any other transactions open at the time? Given it went away after restarting, I'd first guess that something else might have a lock on the table. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] set constraints behavior
On Fri, 3 May 2002, Neil Conway wrote: Hi all, The SQL92 spec has this to say about SET CONSTRAINTS DEFERRED: a) If ALL is specified, then the constraint mode in TXN of all constraints that are DEFERRABLE is set to deferred. b) Otherwise, the constraint mode in TXN for the constraints identified by the constraint names in the constraint name list is set to deferred. (section 14.2, page 401) My reading of this: if you specify ALL, only the constraints marked as DEFERRABLE are affected. If you specify a specific constraint, it is deferred, whether the constraint is marked as DEFERRABLE or not. Current Postgres behavior is incompatible with this interpretation: I think you missed Syntax Rule 2: The constraint specified by constraint name shall be DEFERRABLE ---(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] set constraints behavior
On Fri, 3 May 2002, Neil Conway wrote: On Fri, 3 May 2002 10:39:28 -0700 (PDT) Stephan Szabo [EMAIL PROTECTED] wrote: On Fri, 3 May 2002, Neil Conway wrote: My reading of this: if you specify ALL, only the constraints marked as DEFERRABLE are affected. If you specify a specific constraint, it is deferred, whether the constraint is marked as DEFERRABLE or not. Current Postgres behavior is incompatible with this interpretation: I think you missed Syntax Rule 2: The constraint specified by constraint name shall be DEFERRABLE Ah, okay. Yeah, I missed that part. Stupid standards, they're practically unreadable :-) (My other question, regarding transaction and SET CONSTRAINTS, is still valid) Didn't answer that part because I'm not sure what's best for that given the way we handle out of transaction statements (the other I remembered from past readings and rechecked). ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
