Re: [GENERAL] Feature Idea: Statement Echo in DO$$
Hello 2013/7/3 David Johnston : > I am using a DO$$ $$ block to emulate something that admittedly may be > standard practice to accomplish using psql but for which I am using a less > capable UI. > > Anyway, the basic form is: > > DO $$ > DECLARE some_var varchar := 'value'; > BEGIN > > UPDATE . WHERE col = some_var; > UPDATE . WHERE col = some_var; > > RETURN; > END; > $$ > > Now because of the DO I have no idea how many records were affected for each > of the UPDATE statements. I am thinking that, either at the statement-level > or even somehow defined inside the DO, some way to have the system > automatically echo the usual "0 records updated" message out of the DO. > > i.e., UPDATE (VERBOSE) SET id = some_var; > > GET DIAGNOSTICS obviously works, and for a stored function is probably the > better option, but for a simple DO oriented script command it is quite > verbose. > > There is a prior discussion (somewhere) regarding using STRICT without > RETURNING in similar situations to easily define when only one (and only > one) record is expected to be affected. This thought falls into the same > usability category. > > Thoughts? I dislike this proposal syntax is common for DO and functions, and it is useless there. Probably we can enhance a messages in DEBUG level (or we can create a new level of debug notices for this purposes). I am afraid, there is no some workaround :( Regards Pavel Stehule > > David J. > > > > > > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/Feature-Idea-Statement-Echo-in-DO-tp5762454.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Feature Idea: Statement Echo in DO$$
I am using a DO$$ $$ block to emulate something that admittedly may be standard practice to accomplish using psql but for which I am using a less capable UI. Anyway, the basic form is: DO $$ DECLARE some_var varchar := 'value'; BEGIN UPDATE . WHERE col = some_var; UPDATE . WHERE col = some_var; RETURN; END; $$ Now because of the DO I have no idea how many records were affected for each of the UPDATE statements. I am thinking that, either at the statement-level or even somehow defined inside the DO, some way to have the system automatically echo the usual "0 records updated" message out of the DO. i.e., UPDATE (VERBOSE) SET id = some_var; GET DIAGNOSTICS obviously works, and for a stored function is probably the better option, but for a simple DO oriented script command it is quite verbose. There is a prior discussion (somewhere) regarding using STRICT without RETURNING in similar situations to easily define when only one (and only one) record is expected to be affected. This thought falls into the same usability category. Thoughts? David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Feature-Idea-Statement-Echo-in-DO-tp5762454.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] feature idea
hubert depesz lubaczewski wrote: hi would it be possible to allow (in psql) syntax like: \do = | grep ... or even: select * from table; | zcat - > /tmp/table.data.gz i.e. - adding | ... at the end of command to send its output via pipe to another program? usecase that i had today was pretty simple - check list of operators that are defined using "password" datatype. generally - i could: psql -c '\do' | grep -i password but simple way of doing it from shell would be so much nicer. we gave this feature already with \do - but i think it would be cool to be able to do it on statement level - in more natural (to unix veterans) way. what do you think? Maybe pgbash is what you are looking for. There were recent posts about it that can get to its home page. Looks like it will need some work to bring it up to current. http://archives.postgresql.org/pgsql-general/2008-09/msg01234.php klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] feature idea
hubert depesz lubaczewski <[EMAIL PROTECTED]> writes: > On Mon, Oct 06, 2008 at 11:45:44AM -0700, Steve Crawford wrote: >> What's wrong with: >> \o '| gzip -c - > foo.gz' >> \d >> \o > it's too long. and it requires disabling. i'd like something that would > work exactly like unix pipe - send input of one command to another. The fact that it's possible now means that the case for adding syntax for it is extremely weak. You'd need to convince people that this is so useful that we should go to the trouble of figuring out how to shoehorn it into the syntax of every backslash command (and in at least some cases that'd be pretty tricky). Given that it never got requested before, I think it'll be hard to show that. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] feature idea
On Mon, Oct 06, 2008 at 09:32:00PM +0200, Martijn van Oosterhout wrote: > Isn't this what \g does? only for queries. it doesn't work for psql builtins - like \do which i used in my original mail. best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: [EMAIL PROTECTED] / aim:depeszhdl / skype:depesz_hdl / gg:6749007 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] feature idea
On Mon, Oct 06, 2008 at 09:18:00PM +0200, hubert depesz lubaczewski wrote: > it's too long. and it requires disabling. i'd like something that would > work exactly like unix pipe - send input of one command to another. Isn't this what \g does? Have a nice day, -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] feature idea
On Mon, Oct 06, 2008 at 11:45:44AM -0700, Steve Crawford wrote: > What's wrong with: > \o '| gzip -c - > foo.gz' > \d > \o > I use: > \o '|lpr' > all the time. it's too long. and it requires disabling. i'd like something that would work exactly like unix pipe - send input of one command to another. depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: [EMAIL PROTECTED] / aim:depeszhdl / skype:depesz_hdl / gg:6749007 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] feature idea
hubert depesz lubaczewski wrote: hi would it be possible to allow (in psql) syntax like: \do = | grep ... or even: select * from table; | zcat - > /tmp/table.data.gz ... What's wrong with: \o '| gzip -c - > foo.gz' \d \o I use: \o '|lpr' all the time. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] feature idea
hi would it be possible to allow (in psql) syntax like: \do = | grep ... or even: select * from table; | zcat - > /tmp/table.data.gz i.e. - adding | ... at the end of command to send its output via pipe to another program? usecase that i had today was pretty simple - check list of operators that are defined using "password" datatype. generally - i could: psql -c '\do' | grep -i password but simple way of doing it from shell would be so much nicer. we gave this feature already with \do - but i think it would be cool to be able to do it on statement level - in more natural (to unix veterans) way. what do you think? best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: [EMAIL PROTECTED] / aim:depeszhdl / skype:depesz_hdl / gg:6749007 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Feature idea
Bill Moran wrote: > Not to start an argument, but you could reverse that logic and say "Do you want > to hurt the smart, ssl users by not including helpful functionality that could > be dangerous to uneducated non-ssl users?" > > IMHO, it really depends on the design philosophy that PostgreSQL follows. I'm > familiar with the strong push for stability, and I approve. But I'm not as > sure I have a feel for what developers think about this kind of thing. > > If you made it a compile-time option, or made it disabled by default and > requires a special setting in postgresql.conf to enable. Would that be secure? > Not really, as stupid users would still enable it without understanding, and > there's always the possibility that a some packager would build it with > dangerous settings and distribute it widely. > > (As a side note, I seem to remember a program that had a --shoot-my-own-foot > option to ./configure ... but I can't remember what it was ...) > > So, the question becomes one of design philosophy (at least, I'm basing this on > the concept that actual implementation would not be too hard, correct me if I'm > wrong) You are correct. The question is whether it is worth adding that level of complexity into the system --- in the past, we have decided it isn't. We have the $HOME/.pgpass file to store username/password combinations that is probably best, though it works only with libpq-based interfaces. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Feature idea
Bruce Momjian <[EMAIL PROTECTED]> wrote: > Bill Moran wrote: > > Bruce Momjian <[EMAIL PROTECTED]> wrote: > > > > > Chris Ochs wrote: > > > > > > > > What if SET SESSION AUTHORIZATION could also accept a password so that non > > > > superusers could switch to a different user? How difficult would this be? > > > > > > Well, the password would go over the wire unencrypted, causing a > > > security problem. > > > > Only if encrypted transport is not enabled. With encrypted transport, it would > > be as secure as anything else, right? > > > > Perhaps, it could only be available if transmission encryption is enabled? Then > > again, there's a certain amount of "only the user can shoot his own foot" that > > has to be accepted ... > > > > Just thinking out loud ... > > Yes, if you use SSH it is secure, but do we want clauses that are only > useful in SSH mode? Not to start an argument, but you could reverse that logic and say "Do you want to hurt the smart, ssl users by not including helpful functionality that could be dangerous to uneducated non-ssl users?" IMHO, it really depends on the design philosophy that PostgreSQL follows. I'm familiar with the strong push for stability, and I approve. But I'm not as sure I have a feel for what developers think about this kind of thing. If you made it a compile-time option, or made it disabled by default and requires a special setting in postgresql.conf to enable. Would that be secure? Not really, as stupid users would still enable it without understanding, and there's always the possibility that a some packager would build it with dangerous settings and distribute it widely. (As a side note, I seem to remember a program that had a --shoot-my-own-foot option to ./configure ... but I can't remember what it was ...) So, the question becomes one of design philosophy (at least, I'm basing this on the concept that actual implementation would not be too hard, correct me if I'm wrong) -- Bill Moran Potential Technologies http://www.potentialtech.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Feature idea
As much as I hate to say it, if it would be insecure when not using ssl, this is a feature that people would definitely use insecurely and one day it would be labeled as a 'security hole' in postgresql. Chris - Original Message - From: "Bill Moran" <[EMAIL PROTECTED]> To: "Bruce Momjian" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Tuesday, June 15, 2004 8:13 AM Subject: Re: [GENERAL] Feature idea > Bruce Momjian <[EMAIL PROTECTED]> wrote: > > > Chris Ochs wrote: > > > > > > What if SET SESSION AUTHORIZATION could also accept a password so that non > > > superusers could switch to a different user? How difficult would this be? > > > > Well, the password would go over the wire unencrypted, causing a > > security problem. > > Only if encrypted transport is not enabled. With encrypted transport, it would > be as secure as anything else, right? > > Perhaps, it could only be available if transmission encryption is enabled? Then > again, there's a certain amount of "only the user can shoot his own foot" that > has to be accepted ... > > Just thinking out loud ... > > -- > Bill Moran > Potential Technologies > http://www.potentialtech.com > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Fw: [GENERAL] Feature idea
You know I didn't even think about that, duh. I was thinking of using it inside a function where the password could be obtained on the database server itself somehow, avoiding the need to pass any passwords from the web application and simplifying a rather complicated permission structure we have where we have anonymous users that need insert rights on a whole bunch of different schema's. Chris > - Original Message - > From: "Bruce Momjian" <[EMAIL PROTECTED]> > To: "Chris Ochs" <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > Sent: Tuesday, June 15, 2004 8:02 AM > Subject: Re: [GENERAL] Feature idea > > > > Chris Ochs wrote: > > > > > > What if SET SESSION AUTHORIZATION could also accept a password so that > non > > > superusers could switch to a different user? How difficult would this > be? > > > > Well, the password would go over the wire unencrypted, causing a > > security problem. > > > > -- > > Bruce Momjian| http://candle.pha.pa.us > > [EMAIL PROTECTED] | (610) 359-1001 > > + If your life is a hard drive, | 13 Roberts Road > > + Christ can be your backup.| Newtown Square, Pennsylvania > 19073 > > > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Feature idea
Bill Moran wrote: > Bruce Momjian <[EMAIL PROTECTED]> wrote: > > > Chris Ochs wrote: > > > > > > What if SET SESSION AUTHORIZATION could also accept a password so that non > > > superusers could switch to a different user? How difficult would this be? > > > > Well, the password would go over the wire unencrypted, causing a > > security problem. > > Only if encrypted transport is not enabled. With encrypted transport, it would > be as secure as anything else, right? > > Perhaps, it could only be available if transmission encryption is enabled? Then > again, there's a certain amount of "only the user can shoot his own foot" that > has to be accepted ... > > Just thinking out loud ... Yes, if you use SSH it is secure, but do we want clauses that are only useful in SSH mode? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Feature idea
Bruce Momjian <[EMAIL PROTECTED]> wrote: > Chris Ochs wrote: > > > > What if SET SESSION AUTHORIZATION could also accept a password so that non > > superusers could switch to a different user? How difficult would this be? > > Well, the password would go over the wire unencrypted, causing a > security problem. Only if encrypted transport is not enabled. With encrypted transport, it would be as secure as anything else, right? Perhaps, it could only be available if transmission encryption is enabled? Then again, there's a certain amount of "only the user can shoot his own foot" that has to be accepted ... Just thinking out loud ... -- Bill Moran Potential Technologies http://www.potentialtech.com ---(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: [GENERAL] Feature idea
Chris Ochs wrote: > > What if SET SESSION AUTHORIZATION could also accept a password so that non > superusers could switch to a different user? How difficult would this be? Well, the password would go over the wire unencrypted, causing a security problem. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Feature idea
What if SET SESSION AUTHORIZATION could also accept a password so that non superusers could switch to a different user? How difficult would this be? One nice side benefit to this would be that you could effectively connect as many users with Apache::DBI under mod perl without having an open connection for every user. Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] feature idea – automatic up
anony wrote: Maybe this feature is already out there. I guess you could write triggers to do some of this. Often when designing a database I add a start_date and end_date column to the table. The start_date is when the record was created and the end_date is when the record expired. For UPDATES, I populate the end_date and INSERT a new record. This allows you to track changes though out time. You probably want to read up on "Temporal Databases" which is what you're talking about. I believe there is a book by C.J.Date on the topic as it relates to relational DBs. From a curiosity point of view, there apparently used to be a "time-travel" feature in (really) old versions of PG. MVCC effectively creates a new row for every update anyway, so you could "rewind" to previous transaction IDs to see the data then. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] feature idea automatic update tracking using date fields feedback pls
Maybe this feature is already out there. I guess you could write triggers to do some of this. Often when designing a database I add a start_date and end_date column to the table. The start_date is when the record was created and the end_date is when the record expired. For UPDATES, I populate the end_date and INSERT a new record. This allows you to track changes though out time. I do this so often I thought it would be a good idea for each table to have the option for automatic UPDATE tracking. When the UPDATE occurs the old record is automatically saved with the end_date populated. The old records would not be visible unless explicitly queried using date fields. Advantages Less Coding: Now I don't have update the old record's end_date and insert a new record for a simple update. A simple UPDATE will take care of it. Good Form: Keeping history of all the changes is important in many applications Easier Queries: If this feature was implemented, multiple tables joins could possibly be referenced by one condition in the WHERE clause, rather than multiple conditions for each table in the join. My .02 Feedback Please. ---(end of broadcast)--- TIP 8: explain analyze is your friend
