[HACKERS] Foreign keys on array elements
Hi, Can you put a foreign key constraint on an array column that says that each element of the array must match a primary key? If not, is this a TODO perhaps? Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] ORDER BY different locales for 8.0
Am Dienstag, 19. April 2005 09:18 schrieb Honza Pazdziora: Hello, the nls_string function that makes it possible to sort by arbitrary locale has been updated to reflect the changes in error handling in PostgreSQL 8.0, due to users using the nls_string sorting on 7.4 and requesting it for 8.0 as well. The distribution can be downloaded from http://www.fi.muni.cz/~adelton/l10n/ http://www.fi.muni.cz/~adelton/l10n/postgresql-nls-string/postgresql-nls-st ring-8.01.tar.gz I'll appreciate any comments. The README is as follows: - Why this function: PostgreSQL, at least until version 8.0, has rather weak support for various collating sequences -- what you get when you do select ... order by column. The sorting is closely tied to indexes used throughout the database cluster and is specified by locale settings at the initdb time. Yet, people asked for ways of specifying the collating rules at runtime, even if the sorting will not use indexes. Just take the records and sort them. It is reasonable request to want one select to order by using English rules, another one to run with German rules and yet another with Czech ones, without having to dump, initdb, restore. Cool. I'm working on a multi-lingual database, so this is exactly what I looked for. The only disatvantage is the length of the resulting strings, it's around 9 time longer than the original string. Just a suggestion: Could it be done better by not returing a string, but a non-locale affected representation like some sort of byte array, so the size could be reduced alot? However, extremly useful. Thanks! ---(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] Problem with PITR recovery
Hi Simon, Actually, me too. Never saw the need for the Oracle command myself. It actually has. If you want to move your redo logs to a new disk, you create a new redo log file and then issue a ALTER SYSTEM SWITCH LOGFILE; to switch to the new logfile. Then you can remove the old one (speaking just of one file for simplification). Waiting on that event could take ages. Strictly speaking, this doesn't concern postgresql (yet). But if, at the future, we support user defined (= changing these parameters while the db is running) redo log locations, sizes and count, we need a function to switch the logfile manually. Which I think the pg_stop_backup() hack is not suitable for. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Foreign keys on array elements
Hi, Can you put a foreign key constraint on an array column that says that each element of the array must match a primary key? If not, is this a TODO perhaps? Chris Hello, Using array values for foreign key is very special. I not sure, so all people need it. More interesting is CHECK on array. But you can write simply trigger. CREATE OR REPLACE FUNCTION check_() RETURNS TRIGGER AS $$ DECLARE _v integer; BEGIN FOR _i IN array_lower(NEW.array_value,1) .. array_upper(NEW.array_value,1) LOOP PERFORM 1 FROM some_tab WHERE pk = NEW.array_value[_i]; IF NOT FOUND THEN RAISE EXCEPTION '..'; END IF; END LOOP; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER foo BEFORE INSERT OR UPDATE ON ... FOR EACH ROW EXECUTE PROCEDURE check_(); Regards Pavel Stehule ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [GENERAL] Idea for the statistics collector
Bruce Momjian wrote: Christopher Kings-Lynne wrote: Good god - how old was that email? 2002??? Yep, and been in my mailbox since then, waiting for me to process it into a TODO entry. Exciting what one can find wiping the floor of the mailbox :-) Regards, Andreas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Problem with PITR recovery
It is also recommended when creating new standby control files, when Oracle can't automatically expand the data file capacity on a standby like it does with a live database. Nothing like seeing the 'Didn't restore from sufficiently old backup' message when Oracle is confused (which seems to be most of the time) about what transactions have been applied where. This, of course, doesn't matter for postgresql. Thank the gods On Apr 20, 2005, at 3:28 AM, Klaus Naumann wrote: Hi Simon, Actually, me too. Never saw the need for the Oracle command myself. It actually has. If you want to move your redo logs to a new disk, you create a new redo log file and then issue a ALTER SYSTEM SWITCH LOGFILE; to switch to the new logfile. Then you can remove the old one (speaking just of one file for simplification). Waiting on that event could take ages. Strictly speaking, this doesn't concern postgresql (yet). But if, at the future, we support user defined (= changing these parameters while the db is running) redo log locations, sizes and count, we need a function to switch the logfile manually. Which I think the pg_stop_backup() hack is not suitable for. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings Andrew Rawnsley Chief Technology Officer Investor Analytics, LLC (740) 587-0114 http://www.investoranalytics.com ---(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] Problem with PITR recovery
On Wed, 2005-04-20 at 09:28 +0200, Klaus Naumann wrote: Actually, me too. Never saw the need for the Oracle command myself. It actually has. If you want to move your redo logs to a new disk, you create a new redo log file and then issue a ALTER SYSTEM SWITCH LOGFILE; to switch to the new logfile. Then you can remove the old one (speaking just of one file for simplification). Waiting on that event could take ages. Strictly speaking, this doesn't concern postgresql (yet). But if, at the future, we support user defined (= changing these parameters while the db is running) redo log locations, sizes and count, we need a function to switch the logfile manually. Which I think the pg_stop_backup() hack is not suitable for. Thanks Klaus - I never tried that online. We're someway away from functionality for online redo location migration, I agree. Sounds like we'd still be able to do the log switch as part that. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] HAVING alias ...
Is there a reason (other then it hasn't been implemented yet?) that the following couldn't work? SELECT id,count(id) AS cnt FROM table WHERE id IN ( 1,2,3,4,5) GROUP BY id HAVING cnt = 2; instead of: SELECT id,count(id) AS cnt FROM table WHERE id IN ( 1,2,3,4,5) GROUP BY id HAVING count(id) = 2; The second one would have to 're-run' the COUNT against the table, would it not? Whereas the first would take the existing results? Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(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] Foreign keys on array elements
On Wed, 20 Apr 2005, Christopher Kings-Lynne wrote: Hi, Can you put a foreign key constraint on an array column that says that each element of the array must match a primary key? Not currently, because foreign keys are between directly comparable things. If not, is this a TODO perhaps? Maybe. It's been discussed before IIRC. Doing the referential actions might get tricky, and you'd often want to index so that finding the individual array elements isn't expensive. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] HAVING alias ...
Marc G. Fournier [EMAIL PROTECTED] writes: Is there a reason (other then it hasn't been implemented yet?) that the following couldn't work? SELECT id,count(id) AS cnt FROM table WHERE id IN ( 1,2,3,4,5) GROUP BY id HAVING cnt = 2; It's contrary to the SQL spec, for one thing ... SELECT id,count(id) AS cnt FROM table WHERE id IN ( 1,2,3,4,5) GROUP BY id HAVING count(id) = 2; The second one would have to 're-run' the COUNT against the table, would it not? No, it doesn't. We've optimized out duplicate aggregate calls for awhile now. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] argtype_inherit() is dead code
-Original Message- From: Jim C. Nasby [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 19, 2005 5:56 PM To: Christopher Browne Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] argtype_inherit() is dead code [...] On Sun, Apr 17, 2005 at 07:01:41PM -0400, Christopher Browne wrote: [...] Object Orientation is all about the notion of having data that is aware of its type, and where there can be a dispatching of methods against those types. There is already a perfectly functional ability to dispatch based on argument types. These essentials are there. Well, if you go with Bjarne Stroustrup's formulation of OOP (which is, of course, by no means exclusively authoritative), the core of OOP is encapsulation, inheritance, and polymorphism. Inherited tables provide the second, overloaded functions provide the third, but the security model is left providing the first. However, I would say that the first property is the most essential for OOP, because in my view, OOP is about *data hiding*. In particular, it's about separating the implementation from the interface, and forcing users to access objects through the interface. While such a design philosophy is *possible* with Postgres, it is by no means encouraged or *easy*. Furthermore, it probably doesn't make sense in all contexts. One way to think about an object-relational database is as a set of persistent objects stored in well-known containers. While traditional programming languages offer several common access methods for containers, the point of a query language is to offer an extremely powerful and generalized container access system. However, this access system is really an implementation detail of the object system, while at the same time being the primary means of object interaction. In terms of manipulating data, it's really about as OOP as passing around raw pointers to everything. From this perspective, DBs will not support OOP while SQL remains the primary access method; and there is no reason to believe that people will give up SQL in favor of a more OOP-like interface. Yes, but they're only there when it comes to storing data. There's nothing allowing you to cohesively combine code and data. I agree entirely. And I also agree that in many cases, there is no sensible way to do so. One of the ways in which DBs are different from programming language objects is in the data decomposition. Most PLs have self-contained objects whose data is primarily localized within one structure that is more or less contiguous in memory. DBs, on the other hand, tend to have objects that may span multiple tables, because this is the most efficient way of storing the data. In a way, the relational model is the antithesis of the OOP model. The central theme of the relational model is *data-sharing*. The idea that data should be decomposed and the common pieces factored out. Whereas, OOP says that it is the *functionality* that should be factored out into a minimal interface. An object should be able to have methods attached to it, for example. I don't think that's sufficient. To support encapsulation, you also need to enforce access to the data through the method interface. Else, you can simulate methods with stored procedures. And that functionality is essentially missing. There's no way to present a combined set data and code that operates on that data. That's encapsulation. And it's missing. But for a good reason. It doesn't really matter why this kind of functionality is missing; the fact that it is missing means it's much less likely that any of the OO stuff will be used. Actually, it *does* matter why it's missing. The reason it's missing tells us why people don't use the OOP features of the DB. What needs to be done is to construct a consistent theory of how the relational model and the OOP model can be integrated. The OOP model is about data integrity, maintaining object invariants, ensuring program correctness, etc. The relational model is about performance, storing data efficiently, querying it efficiently, etc. These are competing goals, and it may well be that a good object-relational theory simply develops a framework in which the tradeoffs are explicitly stated and describes how to implement different points in the design space in a consistent way. I realize that there is some existing work with object-relational modelling, but my impression is that such work is still fairly immature and scattered. I think the current limitations (foreign keys, and cross-table constraints) are issues as well. It might also help if the docs had some info about how inherited tables worked 'under the covers', so people knew what kind of overhead they implied. I don't think inherited tables work in an entirely intuitive way. It certainly doesn't help that viewing an inherited table through pgAdmin shows records that aren't returned by an equivalent query. I think the problem is that
Re: [HACKERS] Postgres: pg_hba.conf, md5, pg_shadow, encrypted passwords
Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: Simply put, MD5 is no longer strong enough for protecting secrets. It's just too easy to brute-force. SHA1 is ok for now, but it's days are numbered as well. I think it would be good to alter SHA1 (or something stronger) as an alternative to MD5, and I see no reason not to use a random salt instead of username. Well, I have no particular problem with offering SHA1 as an alternative hash method for those who find MD5 too weak ... but I still question the value of putting any random salt in the table. AFAICS you would have to send that salt as part of the initial password challenge, which means any potential attacker could find it out even before trying to compromise pg_shadow; so Stephen's argument that there is a useful improvement in protection against precomputation of password hashes still falls down. BTW, one could also ask exactly what threat model Stephen is concerned about. ISTM anyone who can obtain the contents of pg_shadow has *already* broken your database security. That's what I told him. I think his concern about pre-computed hashes is the only real issue, and give 'postgres' is usually the super-user, I can see someone pre-computing md5 postgres hashes and doing quick comparisons, perhaps as a root kit so you don't have to do the hashing yourself. I personally don't find that very compelling either. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Postgres: pg_hba.conf, md5, pg_shadow, encrypted passwords
Bruce Momjian wrote: BTW, one could also ask exactly what threat model Stephen is concerned about. ISTM anyone who can obtain the contents of pg_shadow has *already* broken your database security. That's what I told him. I think his concern about pre-computed hashes is the only real issue, and give 'postgres' is usually the super-user, I can see someone pre-computing md5 postgres hashes and doing quick comparisons, perhaps as a root kit so you don't have to do the hashing yourself. I personally don't find that very compelling either. The issue is that you should try your best to prevent dictionary attacks, because often people use the same passwords for different things. I know they shouldn't, but sometimes they do, so any measures you can take to make a dictionary attack harder are worth doing, especially when the random salt is so simple to implement. -- David. ---(end of broadcast)--- TIP 8: explain analyze is your friend
