Обсуждение: Plans for 9.1, Grouping Sets, disabling multiqueries, contrib module for string, plpgpsm, preload dictionaries

Поиск
Список
Период
Сортировка
Hello,

* Now I am working on migration of plpgpsm to plpgsql 9.0 base. I hope
so I understand SQL/PSM well so I am able to write production quality
implementation. If you like, I can integrate it to core. It can share
about 40-50% code with plpgpsm. The behave of plpgpsm is same as
plpgsql - without some plpgsql's historical issues (about FOUND, about
NULL and record type). SQL/PSM is litlle bit richer language. Now we
have not any wide used runtime so I don't thinking about rewriting.
Maybe we can rewrite these PL language for parrot or lua runtime in
future. But this step isn't necessary - people hasn't performance
problems with PL based on PL runtime.

* Month ago there was discussion about integration sprintf function to
core. I don't think it is good idea. sprintf implementation isn't
simple. I prefer more simple "format" function based on RAISE NOTICE
... behave and code. It is significantly shorter and simpler. But for
people who like sprintf we can prepare some contrib module with basic
string functions - sprintf, left, right, reverse. This module can be
based on cleaned pstcollection -
http://pgfoundry.org/frs/download.php/2556/pstcoll-100127.tar.gz.

* Last two months I spent some time with preparing workshops about SQL
injection. PostgreSQL has only one issue related to this topic. It
allows multi queries. With this feature any successful injection can
have much more destructive impact. Now we have a GUC per user. I know,
we cannot break multiqueries without breaking basic functionality. But
we can break multiple queries on top level for some selected users -
(web application roles). Then we are able to configure database for
"secure web access". >>It isn't protection against SQL injection<<.
But it can down up possible risk about successful SQL injection. +
downgrade rights on system tables for web application roles.

* I would to like solve problem with fulltext reported by Czech users.
I will try implement preloaded TSearch dictionaries.

* Still I thinking about GROUPING SETS feature.

Regards
Pavel Stehule


Pavel Stehule <pavel.stehule@gmail.com> writes:
> * Last two months I spent some time with preparing workshops about SQL
> injection. PostgreSQL has only one issue related to this topic. It
> allows multi queries. With this feature any successful injection can
> have much more destructive impact. Now we have a GUC per user. I know,
> we cannot break multiqueries without breaking basic functionality. But
> we can break multiple queries on top level for some selected users -
> (web application roles). Then we are able to configure database for
> "secure web access". >>It isn't protection against SQL injection<<.

This seems like a waste of effort.  It is already the case that multi
queries are forbidden when submitting through the extended query
protocol.  All that an app has to do is not use simple protocol ---
which, if it's trying to be secure, it's already not using because
it needs out-of-line parameters.

There's no need for yet another GUC.
        regards, tom lane


Pavel Stehule escribió:
> Hello,
> 
> * Now I am working on migration of plpgpsm to plpgsql 9.0 base. I hope
> so I understand SQL/PSM well so I am able to write production quality
> implementation. If you like, I can integrate it to core. It can share
> about 40-50% code with plpgpsm. The behave of plpgpsm is same as
> plpgsql - without some plpgsql's historical issues (about FOUND, about
> NULL and record type). SQL/PSM is litlle bit richer language. Now we
> have not any wide used runtime so I don't thinking about rewriting.
> Maybe we can rewrite these PL language for parrot or lua runtime in
> future. But this step isn't necessary - people hasn't performance
> problems with PL based on PL runtime.

How do you plan to go about code sharing?  I'm wondering if we're going
to have src/pl/common or something like that.  Since there's a huge
amount of common code it doesn't make any sense to keep it duplicate.

Also, AFAIR that was the main rejection point for the plpgpsm patch last
time around, so it would be good to discuss this thoroughly.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.


2010/2/22 Alvaro Herrera <alvherre@commandprompt.com>:
> Pavel Stehule escribió:
>> Hello,
>>
>> * Now I am working on migration of plpgpsm to plpgsql 9.0 base. I hope
>> so I understand SQL/PSM well so I am able to write production quality
>> implementation. If you like, I can integrate it to core. It can share
>> about 40-50% code with plpgpsm. The behave of plpgpsm is same as
>> plpgsql - without some plpgsql's historical issues (about FOUND, about
>> NULL and record type). SQL/PSM is litlle bit richer language. Now we
>> have not any wide used runtime so I don't thinking about rewriting.
>> Maybe we can rewrite these PL language for parrot or lua runtime in
>> future. But this step isn't necessary - people hasn't performance
>> problems with PL based on PL runtime.
>
> How do you plan to go about code sharing?  I'm wondering if we're going
> to have src/pl/common or something like that.  Since there's a huge
> amount of common code it doesn't make any sense to keep it duplicate.

sure - a there a a few parts - simple query diagnostic, namespace
support. Now I would to migrate to 9.0 and have initial version. This
version and regress tests can be used as "etalon". Next stage can be
code cleaning and migration to shared code.

Pavel










>
> Also, AFAIR that was the main rejection point for the plpgpsm patch last
> time around, so it would be good to discuss this thoroughly.
>
> --
> Alvaro Herrera                                http://www.CommandPrompt.com/
> The PostgreSQL Company - Command Prompt, Inc.
>


On sön, 2010-02-21 at 11:00 +0100, Pavel Stehule wrote:
> * Now I am working on migration of plpgpsm to plpgsql 9.0 base. I hope
> so I understand SQL/PSM well so I am able to write production quality
> implementation. If you like, I can integrate it to core. It can share
> about 40-50% code with plpgpsm. The behave of plpgpsm is same as
> plpgsql - without some plpgsql's historical issues (about FOUND, about
> NULL and record type). SQL/PSM is litlle bit richer language. Now we
> have not any wide used runtime so I don't thinking about rewriting.
> Maybe we can rewrite these PL language for parrot or lua runtime in
> future. But this step isn't necessary - people hasn't performance
> problems with PL based on PL runtime.

While having a "cleaner" variant of PL/pgSQL available might be
desirable for some (but compare discussion on plpython3), given that you
label this SQL/PSM, I suppose you are also working on this from a
standards-compliance perspective.  According to my reading, the part of
the SQL standard that is named SQL/PSM does not, however, describe a
procedural language in the PostgreSQL sense of the term.  It describes
server-side modules and an extension to the SQL language (that is, it is
activated by CREATE FUNCTION ... LANGUAGE SQL).  It remains to be
decided which parts of these are ultimately useful and desirable, but I
suggest that there be some discussion on the exact strategy in this area
first, lest we end up with a "plpgsql3".



2010/3/1 Peter Eisentraut <peter_e@gmx.net>:
> On sön, 2010-02-21 at 11:00 +0100, Pavel Stehule wrote:
>> * Now I am working on migration of plpgpsm to plpgsql 9.0 base. I hope
>> so I understand SQL/PSM well so I am able to write production quality
>> implementation. If you like, I can integrate it to core. It can share
>> about 40-50% code with plpgpsm. The behave of plpgpsm is same as
>> plpgsql - without some plpgsql's historical issues (about FOUND, about
>> NULL and record type). SQL/PSM is litlle bit richer language. Now we
>> have not any wide used runtime so I don't thinking about rewriting.
>> Maybe we can rewrite these PL language for parrot or lua runtime in
>> future. But this step isn't necessary - people hasn't performance
>> problems with PL based on PL runtime.
>
> While having a "cleaner" variant of PL/pgSQL available might be
> desirable for some (but compare discussion on plpython3), given that you
> label this SQL/PSM, I suppose you are also working on this from a
> standards-compliance perspective.  According to my reading, the part of
> the SQL standard that is named SQL/PSM does not, however, describe a
> procedural language in the PostgreSQL sense of the term.  It describes
> server-side modules and an extension to the SQL language (that is, it is
> activated by CREATE FUNCTION ... LANGUAGE SQL).  It remains to be
> decided which parts of these are ultimately useful and desirable, but I
> suggest that there be some discussion on the exact strategy in this area
> first, lest we end up with a "plpgsql3".
>

I invite any discussion. I hope so my implementation will be clean and
fast. Still I am learning this language and have to understand to
core. I afraid so implementation SQL/PSM will be little bit modified -
postgresql use little bit different concept of warnings, and mainly we
have not "procedures". So there will be some new limits. My goal is
creating some prototype now. I am sure so we can better integrate PL
to main parser - but it hasn't be a first step.

Pavel

>