Re: [GENERAL] majordomo unmaintained, postmaster emails ignored?
Let me start this email by saying thank you to whoever fixed the problem. I found a bunch of "Welcome to..." / "Results from delayed command" message pairs in my mail this morning, and a batch of messages from each of sql, performance, and hackers mailing lists. # [EMAIL PROTECTED] / 2006-02-28 20:07:09 -0400: > %mj_shell -p who pgsql-general | grep -i sigpipe.cz > [EMAIL PROTECTED] > [EMAIL PROTECTED] > > In fact, he's been registered since Jul '05: Yes, that was the date I first received "must be approved by maintainers" messages. However, I didn't receive any email to that address until Monday when I retried the same subscribe-set pgsql-general noprefix [EMAIL PROTECTED] command, only this time I sent it from the [EMAIL PROTECTED] address, not [EMAIL PROTECTED] A few minutes later, I had majordomo's welcome to and the traffic started flowing in. My other subscriptions (sql, hackers, performance) were in limbo until this morning (CET). > Address: [EMAIL PROTECTED] > Address is valid. > Address Mailbox: [EMAIL PROTECTED] > Registered as > [EMAIL PROTECTED] > Registered onWed Jul 27 06:44:37 2005 > Data last changed on Mon Feb 27 09:20:21 2006 > Subscribed to1 lists > > pgsql-general: > Subscribed as [EMAIL PROTECTED] > Subscribed on Mon Feb 27 09:20:21 2006 > Last changed on Mon Feb 27 09:20:21 2006 > Receiving each message as it is posted > Subscriber flags: > prefix That's strange, as you can see above, the command I sent was "subscribe-set $list noprefix", yet the listing shows prefix. I can confirm that messages I receive from all the lists have munged subjects, although I used subscribe-set noprefix in all subscriptions. > So I'm not 100% certain *what* the problem is :( majordomo claimed I was subscribed, but didn't send me the traffic. # [EMAIL PROTECTED] / 2006-03-01 04:07:13 -: > Marc, I was able to view that weird whitespace message by going to > "Subcribers", entering "neuhauser" in the search box, and then "clicking"[1] > on the troublesome name in question. > Greg Sabino Mullane [EMAIL PROTECTED] That's a completely different bug, the web interface doesn't escape the +, which is then interpreted as urlencoded space. You might want to make sure that the web interface protects the addresses it uses as request parameters by encoding / decoding them according to RFC1738. Otherwise, malicious users might be able to use specially crafted email addresses as trojan horses. Again, thanks for approving the subscriptions. -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Triggers question
Hi All!I want to setup a trigger capable to return more than one record.Example (table users contains 10 records):CREATE FUNCTION get_users() RETURNS SOME_TYPE AS 'BEGIN return (SELECT * FROM users);' LANGUAGE 'plpgsql';I can't figure out the correct Postgres type for SOME_TYPE (see above example).How can I write such trigger? I mean, what is the correct prototype of this trigger function?Thanks in advance/ycrux
Re: [GENERAL] Triggers question
On Wed, Mar 01, 2006 at 02:22:15PM +0100, [EMAIL PROTECTED] wrote: > I want to setup a trigger capable to return more than one record. Your example doesn't show anything related to triggers so I think you mean "function" instead of "trigger." If the function can return more than one row then it's a "set-returning" function. > Example (table users contains 10 records): > CREATE FUNCTION get_users() RETURNS > SOME_TYPE AS ' > BEGIN >return (SELECT * FROM users); > ' LANGUAGE 'plpgsql'; > I can't figure out the correct Postgres type for SOME_TYPE (see above > example). This example's return type would be "SETOF users". This particular function would be simpler in SQL than in PL/pgSQL: CREATE FUNCTION get_users() RETURNS SETOF users AS ' SELECT * FROM users; ' LANGUAGE sql STABLE; Here's the PL/pgSQL version: CREATE FUNCTION get_users() RETURNS SETOF users AS ' DECLARE row users%ROWTYPE; BEGIN FOR row IN SELECT * FROM users LOOP RETURN NEXT row; END LOOP; RETURN; END; ' LANGUAGE plpgsql STABLE; You'd call the function as: SELECT * FROM get_users(); For more information see "SQL Functions Returning Sets" (for SQL) and "Control Structures" (for PL/pgSQL) in the documentation. Here are links to the documentation for 8.1, but use the documentation for whatever version you're running: http://www.postgresql.org/docs/8.1/interactive/xfunc-sql.html#AEN31646 http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] [SQL] regarding grant option
AKHILESH GUPTA wrote: > here i have to grant permissions to that user individually for each and > every table by using: > :->> grant ALL ON to ; > GRANT > and all the permissions are granted to that user for that particular table. Yes. If you are annoyed by having to type too many commands, you can write a little shell script to do it for you. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] [SQL] regarding grant option
thank you very much sir for your valuable suggestion,but i am talking about direct database query...!On 3/1/06, Alvaro Herrera < [EMAIL PROTECTED]> wrote:AKHILESH GUPTA wrote:> here i have to grant permissions to that user individually for each and > every table by using:> :->> grant ALL ON to ;> GRANT> and all the permissions are granted to that user for that particular table.Yes. If you are annoyed by having to type too many commands, you can write a little shell script to do it for you.--Alvaro Herrerahttp://www.CommandPrompt.com/PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Thanks & Regards,AkhileshDAV Institute of ManagementFaridabad(Haryana)GSM:-(+919891606064) (+911744293789)"FAILURES CAN BE FORGIVEN BUT AIMING LOW IS A CRIME"
Re: [GENERAL] [SQL] regarding grant option
AKHILESH GUPTA wrote: > thank you very much sir for your valuable suggestion, > but i am talking about direct database query...! There is none that can help you here, short of making a function in PL/pgSQL or other language ... > On 3/1/06, Alvaro Herrera <[EMAIL PROTECTED]> wrote: > > > > AKHILESH GUPTA wrote: > > > > > here i have to grant permissions to that user individually for each and > > > every table by using: > > > :->> grant ALL ON to ; > > > GRANT > > > and all the permissions are granted to that user for that particular > > table. > > > > Yes. If you are annoyed by having to type too many commands, you can > > write a little shell script to do it for you. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 1: 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: [GENERAL] Size comparison between a Composite type and an
I made some tests with three different types: numeric, text and a specialized type written in c. The tests were made with 20 digit codes. The specialized type was a struct defined as: typdef struct mycode { char c1; char c2; int32 c3; int32 c4; } mycode The sizeof(mycode) returns 12 bytes so i assume this as storage size of mycode type. The mycode type was created with the following sql command: CREATE TYPE mycode ( internallength = 12, input = mycode_in, output = mycode_out, alignment = int ); The text data type has a storage size of 20 bytes + 4 bytes overhead = 24 bytes. The numeric data type has a storage size of 10 bytes + 8 bytes overhead = 18 bytes. I made three tables of one column using the three different data types and checked the size in bytes of the three tables. The results were not as expected. I was expecting these theoretical results: mycode: 1.000.000 of records => 12.000.000 bytes numeric: 1.000.000 of records => 18.000.000 bytes text: 1.000.000 of records => 24.000.000 bytes That is the final size of the table with the text data type to be the double of mycode type. The real results were: mycode: 1.000.000 of records => 65.159.168 bytes numeric: 1.000.000 of records => 74.895.702 bytes text: 1.000.000 of records => 77.340.672 bytes The "text" table is only 16% larger than mycode one (I was expecting 100%!). Any idea? Thank you, Denis Douglas McNaught wrote: Denis Gasparin <[EMAIL PROTECTED]> writes: If the composite data type has 4 bytes overhead, I save 4 bytes for each number... that is important because I must store many many numbers. Yes, if size is a big issue you might be better off with a specialized type. -Doug ---(end of broadcast)--- TIP 1: 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: [GENERAL] Size comparison between a Composite type and an
[EMAIL PROTECTED] writes: > I made three tables of one column using the three different data types > and checked the size in bytes of the three tables. > The results were not as expected. You forgot to consider per-row overhead, including alignment padding. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Looking for a fix to index bloat
[EMAIL PROTECTED] wrote onTuesday, February 28, 2006 7:13 AM> We are suffering from the same issue that is described in this email thread > http://archives.postgresql.org/pgsql-general/2005-07/msg00486.php. > > I don't know if this is the appropriate place to make this request, so if> not, please forgive me. However, in our particular case, we don't have enough> disk space nor money to allow the indexes to grow to a steady state. This isn't what you asked for, but if you can afford a modest amount of downtime you could always drop/recreate the indexes. -Roger
Re: [GENERAL] Size comparison between a Composite type and an
On Wed, Mar 01, 2006 at 05:24:03PM +0100, [EMAIL PROTECTED] wrote: > I made some tests with three different types: > > numeric, text and a specialized type written in c. > > The tests were made with 20 digit codes. > The results were not as expected. > > I was expecting these theoretical results: > mycode: 1.000.000 of records => 12.000.000 bytes > numeric: 1.000.000 of records => 18.000.000 bytes > text: 1.000.000 of records => 24.000.000 bytes > > That is the final size of the table with the text data type to be the > double of mycode type. > > The real results were: > mycode: 1.000.000 of records => 65.159.168 bytes > numeric: 1.000.000 of records => 74.895.702 bytes > text: 1.000.000 of records => 77.340.672 bytes > > The "text" table is only 16% larger than mycode one (I was expecting 100%!). You're missing the per-tuple overhead which is approximatly 28 bytes. Once you take alignment into account, it's not surprising the results are closer than you expected. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. signature.asc Description: Digital signature
[GENERAL] [OFFTOPIC] Typo3 + Postgresql anyone?
Hi My company wants to run Typo3, and I'd like it to run against postgresql instead of mysql (Don't want to have to administer a mysql database ;-). I've googled a bit now, and it seems that I need ADODB for php + some Typo3 extension. Does anyone know of a howto that explains what software I'll need for that, and where to get it? Does anyone here have experience with Typo3+Postgresql that he might want to share? Thanks for your answers, and sorry for the slightly off-topic post ;-) greetings, Florian Pflug smime.p7s Description: S/MIME Cryptographic Signature
[GENERAL] a web framework for postgresql?
Hi, Most of the web applications I work on are nothing more than front-ends to postgresql. I have used Perl (CGI), Java, C# and am now looking at Django. Each generation of frameworks lessens the pain of donig web-apps, but it still seems redundant. Does any one know of a framework where the database server (or a web/app server integrated with a DB server) serves web pages? The database contains the data-model; names of tables, names of attributes, their types, foreign key relationships among tables...that's A LOT of information. Sql query, views or stored procs could serve as 'reports' served off the data. Perhaps the only thing that needs to be outside a database is something that describes how the data is to be displayed (CSS). There could be some java/c#/python/ruby/whatever engine which takes all the information provided in the database and generate html/xhtml, default css, javascript validation, etcbut all that should be invisible to the user. Any one know of such a framework? (I'm asking this in pgsql because such a framework will have to be fairly closely linked to a database...and I mainly use pgsql). ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] a web framework for postgresql?
On 01.03.2006, at 19:39 Uhr, falcon wrote: Any one know of such a framework? (I'm asking this in pgsql because such a framework will have to be fairly closely linked to a database...and I mainly use pgsql). Hmm. No. I don't think you can have this combined with what is often called "business logic". A lot of stuff is described in the db model, but that's only one thing. What about process flows, permissions, login handling, session handling, design, performance optimization and so on? I use WebObjects (http://www.apple.com/webobjects) as my main development environment and I found that every application is unique in a good percentage of its features. For "administration" application the included DirectToWeb-Framwork in combination with a powerful community based framework ("ProjectWonder") is a VERY powerful tool when it comes to rapid development. It saves me days and weeks of work - I just couldn't do my job anymore without it and developing applications in a "standard way". But it has a very steep learning curve and is sometimes hard to handle and often frustration - until you learn how not to fight the tool. Frontend applications like online shops, portals and so on are then developed in a more conventional way with components and handwritten xhtml/css. If you have a Mac for development it's worth a look. Deployment can go anywhere (in theory - we have only used Linux and Mac OS X Server so far) as long as a Java runtime is installed. cug -- PharmaLine, Essen, GERMANY Software and Database Development smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] a web framework for postgresql?
falcon wrote: Hi, Most of the web applications I work on are nothing more than front-ends to postgresql. I have used Perl (CGI), Java, C# and am now looking at Django. Each generation of frameworks lessens the pain of donig web-apps, but it still seems redundant. Does any one know of a framework where the database server (or a web/app server integrated with a DB server) serves web pages? The database contains the data-model; names of tables, names of attributes, their types, foreign key relationships among tables...that's A LOT of information. Sql query, views or stored procs could serve as 'reports' served off the data. Perhaps the only thing that needs to be outside a database is something that describes how the data is to be displayed (CSS). There could be some java/c#/python/ruby/whatever engine which takes all the information provided in the database and generate html/xhtml, default css, javascript validation, etcbut all that should be invisible to the user. Any one know of such a framework? (I'm asking this in pgsql because such a framework will have to be fairly closely linked to a database...and I mainly use pgsql). I don't know of one - I think I'd be a little uneasy with that kind of model to be honest on several levels. Complex web applications involve a lot of complex logic, which tends to be very processor intensive. It's good to be able to scale such sites by adding more processing engines and so partitioning the logic from the database is a good idea. We use (and wrote) Whitebeam which goes some of the way to providing a framework. It cuts down the amount of database knowledge you need by having a defined schema that represents data in a very flexible way (although you are free to develop SQL level applications if you want to). Whitebeam uses Postgres to model arbitrary things and the relationships between those things. The things can be CMS web pages or product catalogues. JavaScript (server side) is then used to add the semantic meaning to those things for each application. The Whitebeam abstraction is higher than SQL (for example it represents arbitrarily deep directories of information and provides mechanisms for searching those) while at the same time being flexible enough to allow a wide range of data-driven applications (CMS, discussion forums, surveys, document repositories etc). The programme files that drive the application are still stored in the standard file system. You could store those in the database as well if you wanted to but you'd be adding more contention for a central resource. To get the best performance you have to have a pragmatic approach to building your system. Most applications also of course have to use other services such as sendmail, spell checkers, image tools. The datbase is only one part of the solution, although admittedly one you have to get right! Pete -- Whitebeam : (http://www.whitebeam.org) YellowHawk : (http://www.yellowhawk.co.uk) ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Special offer with a possible dontation to the project
As a special promotion we are offering PG Lightning Admin for the blow out price of 5 dollars per copy. Our goal is to reach 1000 sales by the first day of spring, and 1 dollar per sale will go to the Postgresql project, but only if we reach the 1000 unit mark. To get this great product for 5 bucks, use the coupon code winterblowout when ordering. If we make it to the 1000 unit mark by the first day of spring the Postgresql project will get a cool 1000 dollar donation. For more information on PGLA and to order see: http://www.amsoftwaredesign.com Feel free to spread the word on this. Thanks, Tony Caduto AM Software Design ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] SELinux strangeness with 8.1.2 and 8.1.3
Hi, I know this isn't directly a postgres issue, but it might help a few other users, so here goes. I did an upgrade on my Fedora Core 4 system, and postgres (8.1.2 from the postgres packages, not FC packages) stopped working because of permission issues when trying to create postmaster.pid in the /var/lib/pgsql/data directory. My system has SELinux active, and it used to work until now. So I upgraded using the latest 8.1.3 release (I built it from SRPMs as an x86_64 binary wasn't available), hoping it will help. It didn't. I researched it a bit, and tried a few things, and discovered that the problem is in the init script at /etc/init.d/postgres users runuser instead of su on SELinux enabled systems. But for some reason it won't work this way. I manually reveted it to use su and it works fine. It anyone knows of a better fix - please let me know. Thanks, Guy. --- Family management on postgres+rails: http://www.famundo.com - coming soon! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Special offer with a possible dontation to the project
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Tony Caduto > Sent: 01 March 2006 19:44 > Cc: pgsql-general@postgresql.org > Subject: [GENERAL] Special offer with a possible dontation to > the project > > Our goal is to reach 1000 sales by the first day of spring, You might want to name a date for that - the first day of spring is just ending here :-) Regards, Dave. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Looking for a fix to index bloat
On Tue, Feb 28, 2006 at 10:13:14AM -0500, [EMAIL PROTECTED] wrote: > We are suffering from the same issue that is described in this email thread > http://archives.postgresql.org/pgsql-general/2005-07/msg00486.php. > > > I don't know if this is the appropriate place to make this request, so if > not, please forgive me. However, in our particular case, we don't have > enough disk space nor money to allow the indexes to grow to a steady state. > We are willing to pay someone to implement the suggested fix described in the > above thread on version 8.0.3. If interested, please respond to this email > with your contact information and a rough idea of how much it would cost and > how long it would take to get the job done. Have you tried vacuuming more frequently? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Special offer with a possible dontation to the
On Wed, 2006-03-01 at 13:44 -0600, Tony Caduto wrote: > As a special promotion we are offering PG Lightning Admin for the blow > out price of 5 dollars per copy. > > Our goal is to reach 1000 sales by the first day of spring, and 1 > dollar per sale will go to the Postgresql project, but only if we reach > the 1000 unit mark. > > To get this great product for 5 bucks, use the coupon code > winterblowout when ordering. > > If we make it to the 1000 unit mark by the first day of spring the > Postgresql project will get a cool 1000 dollar donation. > > For more information on PGLA and to order see: > > http://www.amsoftwaredesign.com > > Feel free to spread the word on this. > > I know I've said it before, but I would without a shadow of a doubt buy this if it ran natively under Linux. :( I've tried it under Wine and it was just too slow and unstable to be useful. Pity... -- Russ ---(end of broadcast)--- TIP 1: 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: [GENERAL] a web framework for postgresql?
Pete, I agree with you about websites containing lots of complex logic. It is an interesting excercise to extract out this logic from the day to day business of web app development. In any case, as I think more about various if/else claues or business specific computations, I get even more convinced that either many of our daily routines can be abstracted away, or partitioned (I guess the way the MVC model keeps people like me from putting everything together in a complex mess). It is interesting that much of the software packages specifically marketed for 'business logic' (various rules engines) are, at their most fundamental, not very different from relational DBs. I am also fairly certain that whole programming languages (their semantics and their syntax) can be defined within relational DBs. So why not attempt to use databases as more than just data stores? P.S. I actually don't think the kind of framework I am looking for exists (oracle has html db, but I haven't been able to study it yet). And I should add that I realize theoretical Relational Dbs are different from modern implementations, but we still have a base to work on. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Special offer with a possible dontation to the project
Sorry, First day of spring for this promotion = March 21st ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Special offer with a possible dontation to the
On Wed, 2006-03-01 at 13:54, Russ Brown wrote: > > I know I've said it before, but I would without a shadow of a doubt buy > this if it ran natively under Linux. :( > > I've tried it under Wine and it was just too slow and unstable to be > useful. > > Pity... what_he_said++; ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Going crazy comparing bytea columns
Hi, I am new to Postgresql, so pls be patient. I am using npgsql with C# to insert a bytea value into a column which will serve as an encrypted password. This works well. However, when I retrieve the value, it is different. In other words, "select pwd from table where pwd like @pwd" does not work. Is it not possible to compare bytea columns? ...get dbConn... NpgsqlCommand cmd = new NpgsqlCommand("select pwd from table where usr = @usr and pwd like @pwd",dbConn); cmd.Parameters.Add(new NpgsqlParameter("usr",DbType.String); cmd.Parameters.Add(new NpgsqlParameter("pwd",NpgsqlTypes.NpgsqlDbType.Bytea); cmd.prepare() ... set param values, call executereader... Thanks, JM Upgrade your account today for increased storage; mail forwarding or POP enabled e-mail with automatic virus scanning. Visit our member benefits page at https://members.canada.com/benefits.aspx for more information. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] a web framework for postgresql?
falcon schrieb: > Pete, ... > So why not attempt to use databases as more than just data stores? > > P.S. I actually don't think the kind of framework I am looking for > exists (oracle has html db, but I haven't been able to study it yet). > And I should add that I realize theoretical Relational Dbs are > different from modern implementations, but we still have a base to work > on. Well, strip the relational part from the db and you already have it. We call it Zope :-) (http://www.zope.org ) Regards Tino ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Going crazy comparing bytea columns
[EMAIL PROTECTED] writes: > I am using npgsql with C# to insert a bytea value into a > column which will serve as an encrypted password. This works > well. However, when I retrieve the value, it is different. > In other words, "select pwd from table where pwd like @pwd" > does not work. Why are you using LIKE when you apparently want simple equality? ISTM that LIKE opens up a whole can of worms with possible appearance of the wildcards (% and _, not to mention \) in the string. Another likely source of trouble is that you're not dealing with escaping of non-ASCII byte values the same way when you insert the password as when you try to look it up. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Going crazy comparing bytea columns
On Wed, Mar 01, 2006 at 10:40:39AM -0800, [EMAIL PROTECTED] wrote: > I am using npgsql with C# to insert a bytea value into a > column which will serve as an encrypted password. This works > well. However, when I retrieve the value, it is different. Different how? Without knowing more I'd wonder it's a matter of escaped vs. non-escaped bytea values. > In other words, "select pwd from table where pwd like @pwd" > does not work. > > Is it not possible to compare bytea columns? I can't help with npgsql but it's certainly possible to compare bytea columns. Incidentally, I don't know if this could be causing any problems, but why are you using LIKE instead of "="? > NpgsqlCommand cmd = new NpgsqlCommand("select pwd from table > where usr = @usr and pwd like @pwd",dbConn); What's the output of the following query? SELECT pwd, @pwd FROM table WHERE usr = @usr That should return the value in the database (pwd) and the value you're providing (@pwd). Let's see how they differ. -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] [OFFTOPIC] Typo3 + Postgresql anyone?
On 3/1/06, Florian G. Pflug <[EMAIL PROTECTED]> wrote: > My company wants to run Typo3, and I'd like it to run against postgresql > instead of mysql (Don't want to have to administer a mysql database ;-). > I've googled a bit now, and it seems that I need ADODB for php + some > Typo3 extension. > > Does anyone know of a howto that explains what > software I'll need for that, and where to get it? > > Does anyone here have experience with Typo3+Postgresql that he might > want to share? Typo uses migrations, so it should be database agnostic. -- Greg Donald Zend Certified Engineer MySQL Core Certification http://destiney.com/ ---(end of broadcast)--- TIP 1: 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: [GENERAL] [SQL] regarding grant option
Though, it is pretty easy to do something like: select 'GRANT ALL ON ' || table_name || ' TO public;' from information_schema.tables where table_schema='blah'; You can feed the output of that to psql, ei: psql -qc "select 'GRANT ALL ON ' || table_name || ' TO public;' from information_schema.tables where table_schema='blah'" | psql On Wed, Mar 01, 2006 at 12:00:16PM -0300, Alvaro Herrera wrote: > AKHILESH GUPTA wrote: > > thank you very much sir for your valuable suggestion, > > but i am talking about direct database query...! > > There is none that can help you here, short of making a function in > PL/pgSQL or other language ... > > > On 3/1/06, Alvaro Herrera <[EMAIL PROTECTED]> wrote: > > > > > > AKHILESH GUPTA wrote: > > > > > > > here i have to grant permissions to that user individually for each and > > > > every table by using: > > > > :->> grant ALL ON to ; > > > > GRANT > > > > and all the permissions are granted to that user for that particular > > > table. > > > > > > Yes. If you are annoyed by having to type too many commands, you can > > > write a little shell script to do it for you. > > -- > Alvaro Herrerahttp://www.CommandPrompt.com/ > The PostgreSQL Company - Command Prompt, Inc. > > ---(end of broadcast)--- > TIP 9: In versions below 8.0, the planner will ignore your desire to >choose an index scan if your joining column's datatypes do not >match > -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 1: 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
[GENERAL] Need a GNU SQL CLI tool for Win32 with ODBC support.
I love Linux, any tool you need it has it. Just try to find the most basic of tools for Windows, what a joke. I need an Open Source SQL command line tool for Windows that will let me script queries to a database over an ODBC connection. It must use ODBC because it may or may not be PostgreSQL. Some times it will but the rest of the time I have no idea what is on the other end. Most crap I am finding is shareware and everything is GUI. Some stuff is Open Source but requires Java, not good but still not CLI. Has anyone seen such a tool? Royce Souther www.SiliconTao.com Let Open Source help your business move beyond. For security this message is digitally authenticated by GnuPG. signature.asc Description: This is a digitally signed message part
[GENERAL] SET TRANSACTION on pl/pgSQL function
Hi to all, I have a little problem, I am working with postgres 8.1.2 and I am creating some store procedure, I would like to handle inside to them the commit and rollback functionality, but If I entry commit command I am not able to exceute the funciotn anymore. PLease could someone help me to understand by some example how to do? Thank you very much Bye Gianni Tiscali ADSL 4 Mega Flat Naviga senza limiti con l'unica Adsl a 4 Mega di velocità a soli 19,95 € al mese! Attivala subito e hai GRATIS 2 MESI e l'ATTIVAZIONE. http://abbonati.tiscali.it/banner/middlepagetracking.html?c=webmailadsl&r=http://abbonati.tiscali.it/adsl/sa/4flat_tc/&a=webmail&z=webmail&t=14 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] [OFFTOPIC] Typo3 + Postgresql anyone?
On Mar 2, 2006, at 6:39 , Greg Donald wrote: Typo uses migrations, so it should be database agnostic. I believe Florian is referring to Typo3, a CMS http://www.typo3.com/ Not Typo, a blogging app http://www.typosphere.org/ Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Need a GNU SQL CLI tool for Win32 with ODBC support.
Roy Souther wrote: I love Linux, any tool you need it has it. Just try to find the most basic of tools for Windows, what a joke. I need an Open Source SQL command line tool for Windows that will let me script queries to a database over an ODBC connection. It must use ODBC because it may or may not be PostgreSQL. Some times it will but the rest of the time I have no idea what is on the other end. Most crap I am finding is shareware and everything is GUI. Some stuff is Open Source but requires Java, not good but still not CLI. It should be possible to use something simple like VBS to script these. Should be simple. And you could open source it :-) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] SET TRANSACTION on pl/pgSQL function
On Thu, Mar 02, 2006 at 12:34:25AM +0100, [EMAIL PROTECTED] wrote: > I have a little problem, I am working with postgres 8.1.2 and I am > creating some store procedure, I would like to handle inside to them > the commit and rollback functionality, but If I entry commit command I > am not able to exceute the funciotn anymore. PostgreSQL functions are executed in the context of an outer transaction so they can't issue COMMIT or ROLLBACK statements. However, in 8.0 and later, several languages (e.g., PL/pgSQL) support error trapping, effectively providing savepoint and rollback to savepoint capability. http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING One way around this restriction would be for the function to use dblink to make another connection to the database; the server-side function then becomes a database client. The function could execute transaction-starting and -ending commands over that connection. -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Need a GNU SQL CLI tool for Win32 with ODBC support.
try activestate perl, I have used it a number of times on windows XP/2000 for CLI stuff (data loading). -- Original Message --- From: Roy Souther <[EMAIL PROTECTED]> To: pgsql-general@postgresql.org Sent: Wed, 01 Mar 2006 16:00:21 -0700 Subject: [GENERAL] Need a GNU SQL CLI tool for Win32 with ODBC support. > I love Linux, any tool you need it has it. Just try to find the most > basic of tools for Windows, what a joke. > > I need an Open Source SQL command line tool for Windows that will let me > script queries to a database over an ODBC connection. It must use ODBC > because it may or may not be PostgreSQL. Some times it will but the rest > of the time I have no idea what is on the other end. > > Most crap I am finding is shareware and everything is GUI. Some stuff is > Open Source but requires Java, not good but still not CLI. > > Has anyone seen such a tool? > > Royce Souther > www.SiliconTao.com > Let Open Source help your business move beyond. > > For security this message is digitally authenticated by GnuPG. --- End of Original Message --- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Temporary data storage in PL/PGSQL functions
I have PL/PGSQL functions that require temporary tables for storing intermediate query output. I used to create a temporary table with a unique name in each function call, but that led to "out of shared memory" errors and bloating of system catalogs, because temp tables are only actually dropped at the end of a transaction, as Tom pointed out. So I tried creating non-temporary tables specifically for use in functions, but the result was that concurrency was not possible as each transaction using the function would retain a lock on the table. I then considered creating temporary tables once per session or transaction. Considering that a connection pool might be used, I had to make my temp tables "ON COMMIT DROP". I still have the following worries: - not very friendly (must call a 'init' function at beginning of each transaction) - must wrap all queries in EXECUTE statements - ON COMMIT DROP may not be portable to other DBMS (not too important here) - may also lead to system catalog bloating over time. Overall I find it rather troublesome to use temporary storage tables in functions. Maybe I am missing something. Any tips? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Need a GNU SQL CLI tool for Win32 with ODBC support.
On Mar 1, 2006, at 6:00 PM, Roy Souther wrote: I love Linux, any tool you need it has it. Just try to find the most basic of tools for Windows, what a joke. I need an Open Source SQL command line tool for Windows that will let me script queries to a database over an ODBC connection. It must use ODBC because it may or may not be PostgreSQL. Some times it will but the rest of the time I have no idea what is on the other end. Most crap I am finding is shareware and everything is GUI. Some stuff is Open Source but requires Java, not good but still not CLI. Has anyone seen such a tool? PHP is pretty easy to setup on Windows and appears to have good ODBC support: http://www.php.net/manual/en/ref.uodbc.php John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] a web framework for postgresql?
On Wednesday 01 March 2006 14:54, falcon wrote: > P.S. I actually don't think the kind of framework I am looking for > exists (oracle has html db, but I haven't been able to study it yet). > And I should add that I realize theoretical Relational Dbs are > different from modern implementations, but we still have a base to work > on. If your up to doing experimentation, you could see about combining one of the pl langs and tie them into one of the various web application frameworks. I'm thinking you could try making plruby functions that use rails, or possibly rigg something up in plpython with django or turbogears. It's all very handwavy, but since you can use gems inside of plruby, who knows... you'd have to figure out how to actually server web requests back and forth from within the db... really it doesnt seem worth it to me, but you might be able to get something together. -- Robert Treat Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Need a GNU SQL CLI tool for Win32 with ODBC support.
activestate perl works a treat and crimson editor (http://www.crimsoneditor.com/) as an editor. If you want a gui database manager with sql scripting try aqua data : http://www.aquafold.com/ Cheers Noel Roy Souther wrote: I love Linux, any tool you need it has it. Just try to find the most basic of tools for Windows, what a joke. I need an Open Source SQL command line tool for Windows that will let me script queries to a database over an ODBC connection. It must use ODBC because it may or may not be PostgreSQL. Some times it will but the rest of the time I have no idea what is on the other end. Most crap I am finding is shareware and everything is GUI. Some stuff is Open Source but requires Java, not good but still not CLI. Has anyone seen such a tool? Royce Souther www.SiliconTao.com Let Open Source help your business move beyond. For security this message is digitally authenticated by GnuPG. begin:vcard fn:Noel Faux n:Faux;Noel org:Monash University;Biochemistry and Molecular Biology adr:;;;Clayton;Vic;3800;Australia email;internet:[EMAIL PROTECTED] tel;work:+61 03 9905 1418 url:http://vbc.med.monash.edu.au/~fauxn version:2.1 end:vcard ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] a web framework for postgresql?
Robert, As soon as I have some time, I'll be checking out pl-python in more detail. Tino, I don't know anything about zope, I'll have a look see :) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] SELinux strangeness with 8.1.2 and 8.1.3
"Just Someone" <[EMAIL PROTECTED]> writes: > I researched it a bit, and tried a few things, and discovered that the > problem is in the init script at /etc/init.d/postgres users runuser > instead of su on SELinux enabled systems. But for some reason it won't > work this way. I manually reveted it to use su and it works fine. I don't think I believe this ... not least because the runuser-for-su substitution has been in there for a long time. It doesn't explain a breakage during an FC4 update. Can you provide a reasonably self-contained demonstration of the problem you saw? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] SELinux strangeness with 8.1.2 and 8.1.3
Hi Tom, I looked into another system I have and after updating FC4 to the latest and installing the latest from the PGDG srpms, I didn't have this problem. Tomorrow I'm going to do a similar test on another server that I have to install Postgres on. I will report back with what I find on it. But on this machine the change to su solved the issue. Some more clues that might help you see if there's a real problem, is that the /var/lib/pgsql/data/postmaster.pid file is created with the a SELinux context that's different from the rest. It is created with system_u:object_r:file_t while the rest of the files are created with root:object_r:postgresql_db_t. And the postmaster (when using runuser) fails on accessing it according to the audit log. The file is created but it's empty. So the failure is when trying to write the pid and the rest of the info to it. When I run with su, it is be able to access it just fine. I retested now just to make sure I wasn't seeing things. Some more info about the system: * FC4 fully updated * Postgres 8.1.3 built from the PGDG SRPMs * Dual Opteron * 4GB RAM * /var/lib/pgsql/data on a RAID10 with xfs on top * WAL on a different RAID on a partition only for itself with ext2 * SELinux in targeted policy mode Bye, Guy. On 3/1/06, Tom Lane <[EMAIL PROTECTED]> wrote: > "Just Someone" <[EMAIL PROTECTED]> writes: > > I researched it a bit, and tried a few things, and discovered that the > > problem is in the init script at /etc/init.d/postgres users runuser > > instead of su on SELinux enabled systems. But for some reason it won't > > work this way. I manually reveted it to use su and it works fine. > > I don't think I believe this ... not least because the runuser-for-su > substitution has been in there for a long time. It doesn't explain > a breakage during an FC4 update. > > Can you provide a reasonably self-contained demonstration of the problem > you saw? > > regards, tom lane > -- Bye, Guy Family management on rails: http://www.famundo.com - coming soon! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org