Re: [HACKERS] Feature suggestion: Database-Security-Modules (DSM)
2017-03-28 9:51 GMT+02:00 Jan Kechel : > -BEGIN PGP SIGNED MESSAGE- > Hash: RIPEMD160 > > > Hi PostgreSQL Hackers, > > > I'm developing software using PostgreSQL for several years and want to > propose a feature to improve security of (badly written?) Web-Apps. > > I call it 'Database Security Modules', derived from > Linux-Security-Modules (aka SELinux, AppArmor, Tomoyo). > > Of course such a feature might already exist, or is in your opinion > not a task for PostgreSQL at all. In that case please simply inform me > about my mistake ;) > > do you know SQL_firewall? http://pgsnaga.blogspot.cz/2015/08/postgresql-sql-firewall.html Regards Pavel
[HACKERS] Feature suggestion: Database-Security-Modules (DSM)
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Hi PostgreSQL Hackers, I'm developing software using PostgreSQL for several years and want to propose a feature to improve security of (badly written?) Web-Apps. I call it 'Database Security Modules', derived from Linux-Security-Modules (aka SELinux, AppArmor, Tomoyo). Of course such a feature might already exist, or is in your opinion not a task for PostgreSQL at all. In that case please simply inform me about my mistake ;) What it's about: It's to prevent SQL-Injection and/or unpriviliged data-retrievel once the webserver has been hacked and/or the Application-Server security measures have ceased. How it works: Users or Roles get attached Access-Rules to form a DSM-List. Each Access-Rule specifies a specific query-plan, that hereby is granted to be executed. Why it helps: A simple SQL-Injection could append to query sth. like ' or 1 = 1; select * from table_with_secret_columns' to retrieve data it should not have access to. But, this always (?? not sure, is this true? does it help at all?) changes the query-plans. Now we have to queries, but each doesn't have a valid Access-Rule as the first adds a 1=1 and thus probably changes completely and the second one never had a Rule. Both queries will be rejected and thus render the SQL-Injection useless. Further ideas: Later, this could be enhanced with additional information given to the DSM, like 'current user id', and some Access-Rules could scope queries based on this additional information (like only allowing a specific Query-Plan with the parameter user_id equal to the given id). Another addition could be rate-limiting. Convenience: DSMs should have different modes like 'off', 'training' (logging query-plans to copy into DSM-Lists), 'warn' (logging but not failing violations) and of course 'enforce'. Please let me know what you think and if you'd be interested to see a first basic patch implementing this. best regards, Jan Kechel -BEGIN PGP SIGNATURE- Version: GnuPG v2 iQIcBAEBAwAGBQJY2hYdAAoJEDdxXoVRJZEA0qoQAITW+Q0EUGsFouvR5VckYR3S xQfL8ecMZEqYeAGwYYBUhK00DF/Wqa8GDyBq8BGbicQjmx6WFpeZYTqx6LF/twar EAewuEueOLWs29sOt6Mn+pCATUZIzTIbF+h7QvqTucFK3kjrCe1qwD06ErVPExQ+ 9NPgNZBxaivlwtuQk5xi495XyI3BVdN3zzaS6zfgQC1jXv7+tpy07TLZDGs8ckOv sziPhwEDiWD81qz8TsH+YromiKu9pT/dMiQyxagm5G/BSTJq4/QveI94EzQh5LLk AZiSbWg+JOhaJKh5YSgOfBlLfyDZFFdOtgqXHSrX8aRG4KPkXmsNWWLk2XRswsIr SIga9CFda8f0Ou78MBAOQRI0baK76O5ycy+aYLawLM2z1A/sozQS+pqaK+xAQtjZ VnNa0t99b89rAgozRrySfktjPeqQliiU4LYy/7Fy918io8kdxAm5j2zaTXdjxTto Fz+OOgg03PjbGHtH9yJOpE6afcIOOwk1SFfAfusJqZXnlhEsASVoQVsz5sCvpCk6 xJ8LeYeRaucdbTp7BxFNz9p7klJlpGz64J4hjFUD/EeQMCf8KmOmHLy+WN7aCps+ 2SncRLONv6UhuSbxhKFqfLgxBevF3pBXuCZLqSTwjNUipSqW4ITT3MhXWmM7DaKH DZTfp6b3ZNMaW8xjkt3j =a5v8 -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Feature suggestion: ON CONFLICT DO NOTHING RETURNING which returns existing row data
> On 5 Oct 2016, at 8:11 PM, Pantelis Theodosiou wrote: > > This can be solved by chaining modifying CTEs. > > Something like this (not tested) that can work with multiple rows inserted: Thanks for the suggestion, but it was actually slower than our current implementation, I believe due to always looking up t1’s id in that join rather than only doing it when we didn’t get an id back from the insert. My hope with this feature suggestion / request was that we wouldn’t have to do that subsequent lookup at all, as pg would just give it back to us. Maybe it would be a win if we were inserting multiple rows, but this code is actually in a trigger on a dummy table that we COPY data in to - thus it can’t be rewritten as a rule or a multi-row insert like that. Thanks Tom -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Feature suggestion: ON CONFLICT DO NOTHING RETURNING which returns existing row data
This can be solved by chaining modifying CTEs. Something like this (not tested) that can work with multiple rows inserted: WITH vals (bk1, bk2, other_t1_columns, other_t2_columns) AS ( VALUES (bk1val, bk2val, other_t1_values, other_t2_values), (bk1val, bk2val, other_t1_values, other_t2_values) ), ins_t1 AS ( INSERT INTO t1 (bk1, bk2, other columns) SELECT bk1, bk2, other_t1_columns FROM vals ON CONFLICT (bk1val, bk2val) DO NOTHING RETURNING id, bk1, bk2 ) INSERT INTO t2 (t1_id, other_t2_columns) SELECT COALESCE(t1.id, ins_t1,id), val.bk1, val.bk2, val.other_t2_columns FROM vals LEFT JOIN ins_t1 ON (vals.bk1, vals.bk2) = (ins_t1.bk1, ins_t1.bk2) LEFT JOIN t1 ON (vals.bk1, vals.bk2) = (t1.bk1, t1.bk2) ; On Wed, Oct 5, 2016 at 1:53 AM, Tom Dunstan wrote: > Hi all > > We recently moved to using 9.5 and were hoping to use the new upsert > functionality, but unfortunately it doesn’t quite do what we need. > > Our setup is something like this: > > CREATE TABLE t1 ( > id BIGSERIAL NOT NULL PRIMARY KEY, > bk1 INT, > bk2 UUID > — other columns > ); > CREATE UNIQUE INDEX t1_bk ON t1 (bk1, bk2); > > CREATE TABLE t2 ( > t1_id BIGINT NOT NULL REFERENCES t1 > — other stuff > ); > > Data comes in as inserts of one tuple each of t1 and t2. We expect inserts > to t1 to be heavily duplicated. That is, for stuff coming in we expect a > large number of rows to have duplicate (bk1, bk2), and we wish to discard > those, but not discard the t2 tuple - those should always be inserted and > reference the correct t1 record. > > So we currently have an insertion function that does this: > > BEGIN > INSERT INTO t1 (bk1, bk2, other columns) > VALUES (bk1val, bk2val, other values) > RETURNING id > INTO t1_id; > EXCEPTION WHEN unique_violation THEN > SELECT id > FROM t1 > WHERE bk1 = bk1val AND bk2 = bk2val > INTO t1_id; > END; > > INSERT INTO t2(t1_id, other columns) VALUES(t1_id, other values); > > We were hoping that we’d be able to do something like this: > > INSERT INTO t1 (bk1, bk2, other columns) > VALUES (bk1val, bk2val, other values) > ON CONFLICT (bk1val, bk2val) DO NOTHING > RETURNING id > INTO t1_id; > INSERT INTO t2(t1_id, other columns) VALUES(t1_id, other values); > > But unfortunately it seems that the RETURNING clause returns null when > there’s a conflict, rather than the existing row’s value. > > I understand that there is ambiguity if there were multiple rows that were > in conflict. I think this sort of functionality really only makes sense > where the conflict target is a unique constraint, so IMO it would make > sense to only support returning columns in that case. > > I imagine that this would be possible to do more efficiently than the > subsequent query that we are currently doing given that postgres has > already found the rows in question, in the index at least. I have no idea > how hard it would actually be to implement though. FWIW my use-case would > be supported even if this only worked for indexes where the to-be-returned > columns were stored in the index using Anastasia’s covering + unique index > patch, when that lands. > > Thoughts? > > Tom > > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
[HACKERS] Feature suggestion: ON CONFLICT DO NOTHING RETURNING which returns existing row data
Hi all We recently moved to using 9.5 and were hoping to use the new upsert functionality, but unfortunately it doesn’t quite do what we need. Our setup is something like this: CREATE TABLE t1 ( id BIGSERIAL NOT NULL PRIMARY KEY, bk1 INT, bk2 UUID — other columns ); CREATE UNIQUE INDEX t1_bk ON t1 (bk1, bk2); CREATE TABLE t2 ( t1_id BIGINT NOT NULL REFERENCES t1 — other stuff ); Data comes in as inserts of one tuple each of t1 and t2. We expect inserts to t1 to be heavily duplicated. That is, for stuff coming in we expect a large number of rows to have duplicate (bk1, bk2), and we wish to discard those, but not discard the t2 tuple - those should always be inserted and reference the correct t1 record. So we currently have an insertion function that does this: BEGIN INSERT INTO t1 (bk1, bk2, other columns) VALUES (bk1val, bk2val, other values) RETURNING id INTO t1_id; EXCEPTION WHEN unique_violation THEN SELECT id FROM t1 WHERE bk1 = bk1val AND bk2 = bk2val INTO t1_id; END; INSERT INTO t2(t1_id, other columns) VALUES(t1_id, other values); We were hoping that we’d be able to do something like this: INSERT INTO t1 (bk1, bk2, other columns) VALUES (bk1val, bk2val, other values) ON CONFLICT (bk1val, bk2val) DO NOTHING RETURNING id INTO t1_id; INSERT INTO t2(t1_id, other columns) VALUES(t1_id, other values); But unfortunately it seems that the RETURNING clause returns null when there’s a conflict, rather than the existing row’s value. I understand that there is ambiguity if there were multiple rows that were in conflict. I think this sort of functionality really only makes sense where the conflict target is a unique constraint, so IMO it would make sense to only support returning columns in that case. I imagine that this would be possible to do more efficiently than the subsequent query that we are currently doing given that postgres has already found the rows in question, in the index at least. I have no idea how hard it would actually be to implement though. FWIW my use-case would be supported even if this only worked for indexes where the to-be-returned columns were stored in the index using Anastasia’s covering + unique index patch, when that lands. Thoughts? Tom -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Feature Suggestion: PL/Js
Peter Eisentraut writes: > Start here: > http://developer.postgresql.org/pgdocs/postgres/plhandler.html > I notice that this has not been updated for the new inline handlers, but > that shouldn't stop you. Actually, that chapter is so old it didn't get updated for language validators either :-(. I added some text, and changed the link in create_language.sgml so hopefully we'll notice it next time. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Feature Suggestion: PL/Js
Kiswono Prayogo escribió: > i guess v8 still the best javascript interpreter > http://waynepan.com/2008/09/02/v8-tracemonkey-squirrelfish-ie8-benchmarks/ > nitro from apple safari would be the second best.. > http://www.computerworld.com/s/article/9128638/Safari_4_rivals_Google_Chrome_in_JavaScript_race?intsrc=news_ts_head > but possibly that benchmark is not really valid because it also > benchmark the rendering.. Performance is not the only useful measure. Embeddability in Postgres is very important, and it being C++ is not a good starting point. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Feature Suggestion: PL/Js
On Thu, 2009-10-08 at 07:06 +0700, Kiswono Prayogo wrote: > > Said slides are available here: > > http://www.pgcon.org/2009/schedule/events/159.en.html > > > > I hope they can be useful. > > > > -- > > Joshua Tolley / eggyknap > > ok i will read it, thanks ^^ And video: http://www.vimeo.com/3728119 Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering If the world pushes look it in the eye and GRR. Then push back harder. - Salamander -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Feature Suggestion: PL/Js
> Said slides are available here: > http://www.pgcon.org/2009/schedule/events/159.en.html > > I hope they can be useful. > > -- > Joshua Tolley / eggyknap ok i will read it, thanks ^^ On Wed, Oct 7, 2009 at 11:18 PM, David E. Wheeler wrote: > On Oct 7, 2009, at 7:55 AM, Andrew Dunstan wrote: > >>> BTW I've seen requests for PL/Js so I'm sure it'll be welcome. What >>> license is v8 under? >> >> It's a BSD license, but it's a C++ API. While it looks cool, I think >> SpiderMonkey is possibly a better bet. > > SquirrelFish? http://webkit.org/blog/189/announcing-squirrelfish/ > > Best, > > David > i guess v8 still the best javascript interpreter http://waynepan.com/2008/09/02/v8-tracemonkey-squirrelfish-ie8-benchmarks/ nitro from apple safari would be the second best.. http://www.computerworld.com/s/article/9128638/Safari_4_rivals_Google_Chrome_in_JavaScript_race?intsrc=news_ts_head but possibly that benchmark is not really valid because it also benchmark the rendering.. On Wed, Oct 7, 2009 at 11:38 PM, Marcelo Costa wrote: > > > On Wed, Oct 7, 2009 at 5:00 AM, Kiswono Prayogo wrote: >> >> by using latest v8 engine from google, is it possible to build PL/Js >> just like other PL in Postgre? such as PL/PHP >> what should i learn if i want to build PL/Js? >> thanks in advance. >> >> regards, >> Kis >> GB >> > > You also can see this link: > > http://xen.samason.me.uk/~sam/repos/pljs/README > > -- > Marcelo Costa > www.marcelocosta.net > - > “You can't always get what want”, > > Doctor House in apology to Mike Jagger > hmm.. someone has built it first, i'll try it in the moment ^^ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Feature Suggestion: PL/Js
On Wed, Oct 07, 2009 at 11:29:15AM -0400, Alvaro Herrera wrote: > Joshua Tolley escribió: > > On Wed, Oct 07, 2009 at 10:22:02AM -0400, Alvaro Herrera wrote: > > > Kiswono Prayogo escribió: > > > > by using latest v8 engine from google, is it possible to build PL/Js > > > > just like other PL in Postgre? such as PL/PHP > > > > what should i learn if i want to build PL/Js? > > > > > > I think Josh Tolley has some slides on how we built PL/LOLCODE that > > > could prove useful. > > Huh, I didn't mean "we built" but "he built"! I didn't feel like you were stealing credit. I certainly couldn't have built it, such as it is, without support from -hackers. :) -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature
Re: [HACKERS] Feature Suggestion: PL/Js
On Wed, Oct 7, 2009 at 5:00 AM, Kiswono Prayogo wrote: > by using latest v8 engine from google, is it possible to build PL/Js > just like other PL in Postgre? such as PL/PHP > what should i learn if i want to build PL/Js? > thanks in advance. > > regards, > Kis > GB > > You also can see this link: http://xen.samason.me.uk/~sam/repos/pljs/README -- Marcelo Costa www.marcelocosta.net - “You can't always get what want”, Doctor House in apology to Mike Jagger
Re: [HACKERS] Feature Suggestion: PL/Js
On Oct 7, 2009, at 7:55 AM, Andrew Dunstan wrote: BTW I've seen requests for PL/Js so I'm sure it'll be welcome. What license is v8 under? It's a BSD license, but it's a C++ API. While it looks cool, I think SpiderMonkey is possibly a better bet. SquirrelFish? http://webkit.org/blog/189/announcing-squirrelfish/ Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Feature Suggestion: PL/Js
> > I think Josh Tolley has some slides on how we built PL/LOLCODE that > could prove useful. > > BTW I've seen requests for PL/Js so I'm sure it'll be welcome. What > license is v8 under? > > the new BSD License http://code.google.com/p/v8/ -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - %s is too gigantic of an industry to bend to the whims of reality
Re: [HACKERS] Feature Suggestion: PL/Js
Joshua Tolley escribió: > On Wed, Oct 07, 2009 at 10:22:02AM -0400, Alvaro Herrera wrote: > > Kiswono Prayogo escribió: > > > by using latest v8 engine from google, is it possible to build PL/Js > > > just like other PL in Postgre? such as PL/PHP > > > what should i learn if i want to build PL/Js? > > > > I think Josh Tolley has some slides on how we built PL/LOLCODE that > > could prove useful. Huh, I didn't mean "we built" but "he built"! -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Feature Suggestion: PL/Js
On Wed, Oct 07, 2009 at 10:22:02AM -0400, Alvaro Herrera wrote: > Kiswono Prayogo escribió: > > by using latest v8 engine from google, is it possible to build PL/Js > > just like other PL in Postgre? such as PL/PHP > > what should i learn if i want to build PL/Js? > > I think Josh Tolley has some slides on how we built PL/LOLCODE that > could prove useful. Said slides are available here: http://www.pgcon.org/2009/schedule/events/159.en.html I hope they can be useful. -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature
Re: [HACKERS] Feature Suggestion: PL/Js
Alvaro Herrera wrote: Kiswono Prayogo escribió: by using latest v8 engine from google, is it possible to build PL/Js just like other PL in Postgre? such as PL/PHP what should i learn if i want to build PL/Js? I think Josh Tolley has some slides on how we built PL/LOLCODE that could prove useful. BTW I've seen requests for PL/Js so I'm sure it'll be welcome. What license is v8 under? It's a BSD license, but it's a C++ API. While it looks cool, I think SpiderMonkey is possibly a better bet. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Feature Suggestion: PL/Js
Kiswono Prayogo escribió: > by using latest v8 engine from google, is it possible to build PL/Js > just like other PL in Postgre? such as PL/PHP > what should i learn if i want to build PL/Js? I think Josh Tolley has some slides on how we built PL/LOLCODE that could prove useful. BTW I've seen requests for PL/Js so I'm sure it'll be welcome. What license is v8 under? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Feature Suggestion: PL/Js
On Wed, 2009-10-07 at 15:00 +0700, Kiswono Prayogo wrote: > by using latest v8 engine from google, is it possible to build PL/Js > just like other PL in Postgre? such as PL/PHP > what should i learn if i want to build PL/Js? Start here: http://developer.postgresql.org/pgdocs/postgres/plhandler.html I notice that this has not been updated for the new inline handlers, but that shouldn't stop you. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Feature Suggestion: PL/Js
by using latest v8 engine from google, is it possible to build PL/Js just like other PL in Postgre? such as PL/PHP what should i learn if i want to build PL/Js? thanks in advance. regards, Kis GB -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] feature suggestion
Well. Ok. Then I'll just do it myself. Just thought it would be good for thousands of users. As I said, it was just a suggestion. I surely aint the only one who ever thought about it. Thanks anyway. 2007/7/31, Josh Berkus <[EMAIL PROTECTED]>: > > Rafael, > > > This is just a PLUS. I just don't see any problem by doing this. > > Even knowing that this is not Standard SQL-Syntax, I just see this as a > > benefit feature. > > Our project has a policy of upholding the SQL standard whereever possible. > For that reason, we don't approve non-standard syntax just for reasons of > accessibility. Any non-standard syntax we approve needs to add > significant extra functionality to the DBMS, not just convenience, and > certainly not because MySQL does it. > > "Standards are important" is one of the themes of PostgreSQL which > differentiates us from MySQL. > > > Another reason is that we have more people migrating from MySQL to > > Postgre than any other database server. People don't migrate to Postgre > > from Oracle. Hardly from MS SQL Server. > > You're mistaken. I think we get more migrations from Oracle than from > MySQL. And quite a few from DB2 and Informix. > > -- > --Josh > > Josh Berkus > PostgreSQL @ Sun > San Francisco > -- Atenciosamente, Rafael Azevedo .: Diretor :: WEBPRO SOLUÇÕES DIGITAIS :: Telefone: 51 3266.3446 :: Celular: 51 9243.9893 :: http://www.webpro.com.br :: Email: [EMAIL PROTECTED] ::: :: Conheça o MAILMAN, Solução em E-mail Marketing :: http://www.mailman.com.br/
Re: [HACKERS] feature suggestion
On 8/1/07, Rafael Azevedo <[EMAIL PROTECTED]> wrote: > Imagine that you have about 30 fields. > Ok, then your first SQL is done. > Now, you just have to add 10 more fields. > Its very easy to get lost. If we have this implementation, you could just > add > > Field31 = 'text', > Field32 = 'text' I have to admit this syntax has a lot of advantages over the insert...values statement, especially in dynamic sql situations. That being said, more and more I just write queries insert..select which would be an awkward fit. mysql compatibility is usually pretty poor justification of a feature (they have a million ways to do everything) and things have to stand on general merit. It is really quite unfortunate the way certain aspects of the sql standard evolved (indirectly causing these types of issues) but that is a topic for another day :) merlin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] feature suggestion
Rafael, > This is just a PLUS. I just don't see any problem by doing this. > Even knowing that this is not Standard SQL-Syntax, I just see this as a > benefit feature. Our project has a policy of upholding the SQL standard whereever possible. For that reason, we don't approve non-standard syntax just for reasons of accessibility. Any non-standard syntax we approve needs to add significant extra functionality to the DBMS, not just convenience, and certainly not because MySQL does it. "Standards are important" is one of the themes of PostgreSQL which differentiates us from MySQL. > Another reason is that we have more people migrating from MySQL to > Postgre than any other database server. People don't migrate to Postgre > from Oracle. Hardly from MS SQL Server. You're mistaken. I think we get more migrations from Oracle than from MySQL. And quite a few from DB2 and Informix. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(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
Re: [HACKERS] feature suggestion
Imagine that you have about 30 fields. Ok, then your first SQL is done. Now, you just have to add 10 more fields. Its very easy to get lost. If we have this implementation, you could just add Field31 = 'text', Field32 = 'text' ... wherever you want. This is just a PLUS. I just don't see any problem by doing this. Even knowing that this is not Standard SQL-Syntax, I just see this as a benefit feature. Another reason is that we have more people migrating from MySQL to Postgre than any other database server. People don't migrate to Postgre from Oracle. Hardly from MS SQL Server. It just makes easier to migrate users from other db servers. And this is sure not hard to implement. Today its easier to migrate to PostgreSQL from MySQL than from PostgreSQL to MySQL. In few words I have given you more reason to add this feature than not to add it. Think about it. 2007/7/31, Rafael Azevedo <[EMAIL PROTECTED]>: > > Yes it is. And it makes easier to migrate from MySQL servers to > PostgreSQL. > Today its easier to migrate to MySQL from PostgreSQL than from PostgreSQL > to MySQL. > > > 2007/7/31, Bruce Momjian <[EMAIL PROTECTED]>: > > > > Gregory Stark wrote: > > > "Rafael Azevedo" <[EMAIL PROTECTED] > writes: > > > > > > > Unstead of having to type all the insert syntax, using ("column") > > names, you > > > > could do the same as MySQL does. > > > > for example: > > > > > > > > INSERT INTO Table SET > > > > "Field1" = 'text', > > > > "Field2" = 'text'; > > > > > > > > So it would make it easier and faster to develop applications using > > Postgre. > > > > > > I'm a bit mystified here. What exactly about this syntax is easier or > > faster? > > > You still have to list all the column names. It looks like it would > > require > > > just as much typing as the regular syntax, no? > > > > > > Or is it that you get to reuse the same string you use for doing an > > update? > > > > As far as I can see, the _feature_ is matching MySQL optional > > non-standard syntax. > > > > -- > > Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us > > EnterpriseDB http://www.enterprisedb.com > > > > + If your life is a hard drive, Christ can be your backup. + > > >
Re: [HACKERS] feature suggestion
Gregory Stark wrote: > "Rafael Azevedo" <[EMAIL PROTECTED]> writes: > > > Unstead of having to type all the insert syntax, using ("column") names, you > > could do the same as MySQL does. > > for example: > > > > INSERT INTO Table SET > > "Field1" = 'text', > > "Field2" = 'text'; > > > > So it would make it easier and faster to develop applications using Postgre. > > I'm a bit mystified here. What exactly about this syntax is easier or faster? > You still have to list all the column names. It looks like it would require > just as much typing as the regular syntax, no? > > Or is it that you get to reuse the same string you use for doing an update? As far as I can see, the _feature_ is matching MySQL optional non-standard syntax. -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] feature suggestion
"Rafael Azevedo" <[EMAIL PROTECTED]> writes: > Unstead of having to type all the insert syntax, using ("column") names, you > could do the same as MySQL does. > for example: > > INSERT INTO Table SET > "Field1" = 'text', > "Field2" = 'text'; > > So it would make it easier and faster to develop applications using Postgre. I'm a bit mystified here. What exactly about this syntax is easier or faster? You still have to list all the column names. It looks like it would require just as much typing as the regular syntax, no? Or is it that you get to reuse the same string you use for doing an update? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] feature suggestion
Rafael Azevedo wrote: > People, > > I'd like to suggest you guys to implement a new feature. > Actually is an alias for a existent feature. > > Unstead of having to type all the insert syntax, using ("column") names, you > could do the same as MySQL does. > for example: > > INSERT INTO Table SET > "Field1" = 'text', > "Field2" = 'text'; > > So it would make it easier and faster to develop applications using Postgre. There is an SQL standard way to do this and we have no intention of extending that. If you want standard code, use the standard syntax. MySQL, if it supports this, is just encouraging you to write non-standard SQL. You can complain to them. -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] feature suggestion
People, I'd like to suggest you guys to implement a new feature. Actually is an alias for a existent feature. Unstead of having to type all the insert syntax, using ("column") names, you could do the same as MySQL does. for example: INSERT INTO Table SET "Field1" = 'text', "Field2" = 'text'; So it would make it easier and faster to develop applications using Postgre. Thanks. Rafael
[HACKERS] Feature Suggestion/Index tuning wizard that automatically decide on indexes
I don't think that this has been up for discussion yet but I think it would be good concept/feature that someone might want to take up or shoot down. However some may have already. I am not sure where it falls PGAdmin, psql, optimizer, gborg... all. Recommended Feature: Index Tuning Wizard Description: The Index Tuning Wizard simplifies the task of identifying which indexes to create in a table and also optionally generates scripts to create them by analyzing a user-supplied workload. Various modes are offered for tuning both indexes and indexed views. Rational: Why this would be a killer feature... because it can save time and improve the performance of the database based real data. Sure you could say that the DBA should be able to do this but it really is a good job for a machine because it analyzes real production data workload queries and statistics quickly. Not to mention not everyone has a DBA on staff and the databases are managed by developers. Comments: MSSQL Server has an excellent model for this. It has the ability to take the transaction logs and statistics and analyze them and recommend indexes and create them. Various URLS Description of MS SQL's Feature http://www.sql-server-performance.com/index_tuning_wizard_tips.asp MS Feature White Paper http://www.microsoft.com/technet/treeview/default.asp?url=/TechNet/prodt echnol/sql/maintain/optimize/tunesql.asp A discussion blurb regarding it http://weblogs.flamefew.net/bayard/archives/000406.html ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Feature suggestion: Postgresql binding to one IP?
Hi Lincoln, Not sure why you would want to run multiple instances, since you can run multiple dbs if you want to maintain separate environments but if you really need to do this, the postmaster has some options which control ip/port binds: [pritchma@blade pritchma]$ /usr/local/pgsql/bin/postmaster --help /usr/local/pgsql/bin/postmaster is the PostgreSQL server. Usage: /usr/local/pgsql/bin/postmaster [options...] Options: -B NBUFFERS number of shared buffers (default 64) -c NAME=VALUE set run-time parameter -d 1-5 debugging level -D DATADIR database directory -F turn fsync off -h HOSTNAME host name or IP address to listen on -i enable TCP/IP connections -k DIRECTORYUnix-domain socket location -N MAX-CONNECT maximum number of allowed connections (1..1024, default 32) -o OPTIONS pass 'OPTIONS' to each backend server -p PORT port number to listen on (default 5432) -S silent mode (start in background without logging output) Developer options: -n do not reinitialize shared memory after abnormal exit -s send SIGSTOP to all backend servers if one dies I run postgres on a box with two interfaces, and I only want it to bind to a single one: # start postgres nohup > /dev/null su -c '/usr/local/pgsql/bin/postmaster -h 10.4.0.1 -i -D /usr/local/pgsql/data > /usr/local/pgsql/log/server.log 2>&1' postgres & Cheers, Mark Pritchard ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Feature suggestion: Postgresql binding to one
Lincoln Yeoh <[EMAIL PROTECTED]> writes: > I'm using a redhat style postgresql init and somehow postgresql seems to > ignore the postgresql.conf file. That seems unlikely, assuming you are running a PG version recent enough to have a postgresql.conf file (ie 7.1 or better). What exactly are you putting into postgresql.conf? Also, take a look at the postmaster log to see if it's issuing any complaints. I believe a syntax error anywhere in the conf file will cause the whole file to be ignored ... > What's the postmaster.opts file for? It's to log the command-line options you gave to the postmaster. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Feature suggestion: Postgresql binding to one
At 11:16 PM 03-10-2001 -0400, Tom Lane wrote: >Lincoln Yeoh <[EMAIL PROTECTED]> writes: >> Is it possible for Postgresql to bind to one IP address? > >See 'virtual_host' GUC parameter. > > regards, tom lane Thanks! I'm using a redhat style postgresql init and somehow postgresql seems to ignore the postgresql.conf file. What's the postmaster.opts file for? Cheerio, Link. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Feature suggestion: Postgresql binding to one IP?
Lincoln Yeoh <[EMAIL PROTECTED]> writes: > Is it possible for Postgresql to bind to one IP address? See 'virtual_host' GUC parameter. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Feature suggestion: Postgresql binding to one IP?
Hi people, Is it possible for Postgresql to bind to one IP address? I'm trying to run multiple postgresql installations on one server. The unix socket could be named accordingly: Postgresql config bound to a particular port and all IPs. .s.PGSQL. Postgresql config bound to a particular port and IP. .s.PGSQL.. Any other suggestions/comments on running multiple instances of postgresql are welcomed. An less desirable alternative is to keep binding to all IP, use different ports and name the ports, but specifying the port by name in -p doesn't work. Cheerio, Link. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html