Re: [SQL] calculated expressions and index use
I use 7.2.1. :( Do you thing my considerations on constant vs. expression are correct? I tried different syntax, no success. Thanks Pavel Tom Lane wrote: Pavel Hlavnicka <[EMAIL PROTECTED]> writes: select * from foo where created_at >= 'now'::timestamp - '1 hour'::interval; My table is indexed on created_at field. The query above doesn't use it, What's your PG version? In 7.2 and later that expression will be folded to a constant. regards, tom lane -- Pavel Hlavnicka Ginger Alliance www.gingerall.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Postgres MD5 Function
Does there exsist a MD5 Function I can call??? If not, is there any interest in one? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Which version is this?
Since which version PostgreSQL is able to do Vacuum Analyze even in the middle of a transaction, namely, insert, delete, update? Thanks Wei
Re: [SQL] CSV import
In DOS and Windows, text lines end with . In Unix, text lines end with only. hex decoct =CTRL-M or 0x0D or 13 or 015 =CTRL-J or 0x0A or 10 or 012 Chad Thompson wrote: > > > > > Unix EOL is LF not CR. > > > > > > Is this the only difference between a dos and unix text file? > > Thanks > Chad > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] calculated expressions and index use
Yes, you are right. It was an issue reated to the type compatibility. My braindead script created tables with 'datetime' types, what is 'timestamp without time zone' after I switched to 'timestamp', all works fine. Thanks a loc, the fact, you ensured me, that the constant expression should be recognized, pushed me much further. Thanks again Have a nice day Pavel Tom Lane wrote: Pavel Hlavnicka <[EMAIL PROTECTED]> writes: I use 7.2.1. :( 7.2 certainly will fold "'now'::timestamp - '1 hour'::interval" to a timestamp constant. This could be a datatype compatibility issue (is created_at the same type? in particular, with/without time zone?) or it could be a selectivity issue --- ie, the planner might think the condition covers too much of the table to make the index useful. It would be useful to look at the results of EXPLAIN ANALYZE for this query, with and without ENABLE_SEQSCAN turned off. regards, tom lane -- Pavel Hlavnicka Ginger Alliance www.gingerall.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] calculated expressions and index use
Pavel Hlavnicka <[EMAIL PROTECTED]> writes: > I use 7.2.1. :( 7.2 certainly will fold "'now'::timestamp - '1 hour'::interval" to a timestamp constant. This could be a datatype compatibility issue (is created_at the same type? in particular, with/without time zone?) or it could be a selectivity issue --- ie, the planner might think the condition covers too much of the table to make the index useful. It would be useful to look at the results of EXPLAIN ANALYZE for this query, with and without ENABLE_SEQSCAN turned off. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Postgres MD5 Function
--On Friday, January 31, 2003 01:34:42 -0800 David Durst <[EMAIL PROTECTED]> wrote: Does there exsist a MD5 Function I can call??? look at /contrib/pgcrypto in the source distribution. If not, is there any interest in one? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 ---(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: [SQL] Postgres MD5 Function
Larry Rosenman wrote:
--On Friday, January 31, 2003 01:34:42 -0800 David Durst
<[EMAIL PROTECTED]> wrote:
Does there exsist a MD5 Function I can call???
look at /contrib/pgcrypto in the source distribution.
Also worth noting is that 7.4 will have (and cvs HEAD has) a builtin md5 function:
regression=# select md5('Joe');
md5
--
3a368818b7341d48660e8dd6c5a77dbe
(1 row)
HTH,
Joe
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] For each record in SELECT
Hi, I have a question here: I have a table with this fields: month description amount now I have to write a query that retrieves the sum of the amount from the minimum month to the maximum month registered for each diferent description. Of course there are cases when a particular description has not record for all the months in that period. I mean, suppouse you have this records: month description amount --- Junedescription1100 Julydescription1500 August description1600 Junedescription2300 August description2400 how you write a query that outputs something like this: JuneJulyAugust -- description1 | 100 500 600 description2 | 300 0 400 My problem is for the 0 value. I hope I've explained clearly my question. Thanks for the help. Best Regards. -- Luis Magaña. Gnovus Networks & Software. www.gnovus.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] CSV import
FYI In text files on a Mac. the EOL character is a only. What a messy thing this whole EOL cruft is. To convert between these text formats on linux is easy if you have dos2unix. The dos2unix on linux can perform many format conversions to and from unix,dos and mac formats. On BSD you need dos2unix to convert from dos to unix and unix2dos to convert from unix to dos. You probably need to get the GNU version of dos2unix or mac2unix to convert to or from mac formatted text. Guy Jean-Luc Lachance wrote: In DOS and Windows, text lines end with . In Unix, text lines end with only. hex decoct =CTRL-M or 0x0D or 13 or 015 =CTRL-J or 0x0A or 10 or 012 Chad Thompson wrote: Unix EOL is LF not CR. Is this the only difference between a dos and unix text file? Thanks Chad ---%<...snip... ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] For each record in SELECT
On Friday 31 January 2003 14:21, Luis Magaña wrote: > Hi, > > I have a question here: > > I have a table with this fields: > > month > description > amount > > now I have to write a query that retrieves the sum of the amount from > the minimum month to the maximum month registered for each diferent > description. > >From what I understand in your email you just need to add a GROUP BY clause. Try a query like this. SELECT description, sum(amount) FROM table GROUP BY description; That should do what you need, Andy ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Controlling access to Sequences
Hi All, I'm implementing a system where I need to be reasonably careful about security. One thing that worries me is SEQUENCES. My understanding is that I need to GRANT the UPDATE privilege on the SEQUENCE if I want a user to be able to to use nextval() on it. The trouble is, if they can do a nextval() they can also do a setval() which would allow them to set the sequence to any value they felt like and potentially cause problems for other user's use of the SEQUENCE. I've considered forcing the user to use the nextval() by using a RULE on a VIEW, but it appear as though the nextval() function still runs as the logged-in user not the owner of the VIEW. Is there any effective method for controlling access to a SEQUENCE? or should I do something like in the view: INSERT INTO x VALUES ( ( SELECT MAX( id ) + 1 FROM x ), some_field ); Is this safe to do? Or might this lead to undesirable problems if called concurrently? Any assistance would be appreciated. Matthew. ---(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: [SQL] Controlling access to Sequences
Matthew Horoschun <[EMAIL PROTECTED]> writes: > My understanding is that I need to GRANT the UPDATE privilege on the > SEQUENCE if I want a user to be able to to use nextval() on it. The > trouble is, if they can do a nextval() they can also do a setval() So? With enough time on your hands, you can apply nextval() often enough to get from any point A to any point B. It seems illusory to think that forbidding setval() makes things more secure. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Controlling access to Sequences
Hi Tom, Thanks for the response. On Saturday, February 1, 2003, at 03:09 PM, Tom Lane wrote: Matthew Horoschun <[EMAIL PROTECTED]> writes: My understanding is that I need to GRANT the UPDATE privilege on the SEQUENCE if I want a user to be able to to use nextval() on it. The trouble is, if they can do a nextval() they can also do a setval() So? With enough time on your hands, you can apply nextval() often enough to get from any point A to any point B. It seems illusory to think that forbidding setval() makes things more secure. Absolutely, You're right. nextval() is just as troublesome. I don't want to arbitrarily restrict access to setval(). I just want a safer way of handling automatic allocation of primary keys on certain tables. Should I just avoid SEQUENCES altogether and use the OIDs under normal circumstances and the MAX( id ) + 1 style thing when I need a human-usable number? Matthew. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Controlling access to Sequences
Matthew Horoschun <[EMAIL PROTECTED]> writes: > Should I just avoid SEQUENCES altogether and use the OIDs under normal > circumstances and the MAX( id ) + 1 style thing when I need a > human-usable number? I don't think so. MAX()+1 has more than enough problems of its own. The real bottom line here is that you should not allow untrustworthy users any sort of direct access to SQL commands of any kind. They should only be allowed access to an application that issues suitably restricted SQL commands on their behalf. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Which version is this?
your question doesnt' seem to be very clear. But the following appeared in release note of version 7.2 hope it helps regds mallah. ` A.5. Release 7.2 Release date: 2002-02-04 A.5.1. Overview This release improves PostgreSQL for use in high-volume applications. Major changes in this release: VACUUM Vacuuming no longer locks tables, thus allowing normal user access during the vacuum. A new VACUUM FULL command does old-style vacuum by locking the table and shrinking the on-disk copy of the table. ~~ On Friday 31 January 2003 10:13 pm, Wei Weng wrote: > Since which version PostgreSQL is able to do Vacuum Analyze even in the > middle of a transaction, namely, insert, delete, update? > > > Thanks > > > Wei -- Regds Mallah Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)26152172 (221) (L) 9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(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: [SQL] Controlling access to Sequences
On Saturday, February 1, 2003, at 03:43 PM, Tom Lane wrote: Matthew Horoschun <[EMAIL PROTECTED]> writes: Should I just avoid SEQUENCES altogether and use the OIDs under normal circumstances and the MAX( id ) + 1 style thing when I need a human-usable number? I don't think so. MAX()+1 has more than enough problems of its own. The real bottom line here is that you should not allow untrustworthy users any sort of direct access to SQL commands of any kind. They should only be allowed access to an application that issues suitably restricted SQL commands on their behalf. While I agree that would solve the problem, that is not the kind of solution we're looking for. We're in the process of porting a custom application from MySQL to PostgreSQL. The main reason for moving is that we can push all the business logic into the database and out of the application layer. That includes security. From my little experience it seems PostgreSQL has a pretty powerful security system if you apply VIEWS, RULES, SCHEMAS, and GRANT carefully. Excluding SEQUENCES, I can see no reason why you cannot lock down PostgreSQL to the point where you could allow users virtually direct access to PostgreSQL. Of course, if anybody can suggest why this isn't the case, I'm more than willing to hear why! Also, can anybody suggest in exactly what kind of situation MAX() + 1 will fail or cause problems? Excluding the performance hit (which I'm probably willing to live with). Thanks Matthew. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Controlling access to Sequences
On Sat, 1 Feb 2003, Matthew Horoschun wrote: > > On Saturday, February 1, 2003, at 03:43 PM, Tom Lane wrote: > > > Matthew Horoschun <[EMAIL PROTECTED]> writes: > >> Should I just avoid SEQUENCES altogether and use the OIDs under normal > >> circumstances and the MAX( id ) + 1 style thing when I need a > >> human-usable number? > > > > I don't think so. MAX()+1 has more than enough problems of its own. > > > > The real bottom line here is that you should not allow untrustworthy > > users any sort of direct access to SQL commands of any kind. They > > should only be allowed access to an application that issues suitably > > restricted SQL commands on their behalf. > > While I agree that would solve the problem, that is not the kind of > solution we're looking for. We're in the process of porting a custom > application from MySQL to PostgreSQL. The main reason for moving is > that we can push all the business logic into the database and out of > the application layer. That includes security. You can do some of the protections through security definer functions probably. > From my little experience it seems PostgreSQL has a pretty powerful > security system if you apply VIEWS, RULES, SCHEMAS, and GRANT > carefully. Excluding SEQUENCES, I can see no reason why you cannot lock > down PostgreSQL to the point where you could allow users virtually > direct access to PostgreSQL. > > Of course, if anybody can suggest why this isn't the case, I'm more > than willing to hear why! Well, there're always bugs. With direct access, it's not too hard to make a plan that returns trillions of rows or one that goes through a huge number of sort steps (each using up some amount of memory). There's still a fair level of trust going on. > Also, can anybody suggest in exactly what kind of situation MAX() + 1 > will fail or cause problems? Excluding the performance hit (which I'm > probably willing to live with). Concurrency problems. You pretty much need to either be willing to have unique key violations and retries or locking down to prevent two transactions from getting the same max()+1 value. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Controlling access to Sequences
On Fri, Jan 31, 2003 at 23:47:27 +1100, Matthew Horoschun <[EMAIL PROTECTED]> wrote: > Hi All, > > I'm implementing a system where I need to be reasonably careful about > security. One thing that worries me is SEQUENCES. > > My understanding is that I need to GRANT the UPDATE privilege on the > SEQUENCE if I want a user to be able to to use nextval() on it. The > trouble is, if they can do a nextval() they can also do a setval() > which would allow them to set the sequence to any value they felt like > and potentially cause problems for other user's use of the SEQUENCE. > > I've considered forcing the user to use the nextval() by using a RULE > on a VIEW, but it appear as though the nextval() function still runs as > the logged-in user not the owner of the VIEW. > > Is there any effective method for controlling access to a SEQUENCE? or > should I do something like in the view: You can limit access to nextval by only allowing access through a user defined function that runs as the definer. If you only want them to use the function when doing an insert into a specific table, then I am not sure if you can do this easily. At worst you could write a function that does the insert. Note that there is a real issue with letting people have access to setval over nextval. nextval can only increase the value of the sequence, while setval can decrease it (in additon to being able to increase it). In some cases decreasing a sequence might cause a lot more problems then a large increase would. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
