Re: PG + PHP, was Re: Zend survey result about dbms...

Look at this:
http://www.zend.com/images/survey/14.gif

I belive that there is only one reason why most of people are using
MySQL - it has native, very easy to install version for windows.

"Marek Lewczuk" <newsy(at)lewczuk(dot)com> writes:
> Look at this:
> http://www.zend.com/images/survey/14.gif
> I belive that there is only one reason why most of people are using
> MySQL - it has native, very easy to install version for windows.

No, that survey is specific to people using PHP, and the reason for the
skew is that PHP (used to) ship with built-in support for MySQL --- and
no other database. I don't think you can draw any conclusions about
which OS they are running on.

Now that MySQL AB has put a stake through the heart of that connection
by GPL'ing their client libraries, we might start to get a little better
traction with PHP users...

regards, tom lane

On Friday 19 September 2003 16:18, Tom Lane wrote:
>
> Now that MySQL AB has put a stake through the heart of that connection
> by GPL'ing their client libraries, we might start to get a little better
> traction with PHP users...

Speaking as a PG+PHP user, the new plphp procedural language should be very
useful. I fully intend to be writing a code generator that can produce
validation functions to be run in PG and in the app. About time I stopped
writing two sets of validation code. I'll stick it on gborg if no-one gets
there before me.

One thing I think would be useful is another pseudo-var in PG, something like
APP_SESSION_VAR which can be set and then used in PG queries. This would make
it much easier to write queries like:

CREATE VIEW my_project_list AS
SELECT * FROM project_list WHERE owner=APP_SESSION_VAR

At the moment you can do this sort of thing easily enough for straightforward
queries, but views/function bodies are a PITA.

Tom - if I offered to produce a patch for something like this - either a var
or a function pair (get_app_session_var(), set_app_session_var(varchar))
would it be likely to be accepted? I've probably got some free time towards
the end of the year.

--
Richard Huxton
Archonet Ltd

Richard Huxton <dev(at)archonet(dot)com> writes:
> One thing I think would be useful is another pseudo-var in PG,
> something like APP_SESSION_VAR which can be set and then used in PG
> queries.

> Tom - if I offered to produce a patch for something like this - either a var
> or a function pair (get_app_session_var(), set_app_session_var(varchar))
> would it be likely to be accepted?

It'd depend a lot on the details of what you propose, I think. True
variables seem like they'd be rather invasive, not to mention possibly
error-prone (is "foo" a variable or a column reference?). However you
could do something pretty self-contained in the form of a couple of
functions. I'd suggest they support more than just one variable, btw.
How about "set_session_variable(varname, varvalue)" and
"get_session_variable(varname)"?

I should think we'd at least accept that as a contrib module; whether it
would become mainstream would probably depend on the level of interest.

regards, tom lane

Tom Lane wrote:

> Richard Huxton <dev(at)archonet(dot)com> writes:
>
>>One thing I think would be useful is another pseudo-var in PG,
>>something like APP_SESSION_VAR which can be set and then used in PG
>>queries.
>
>
>>Tom - if I offered to produce a patch for something like this - either a var
>>or a function pair (get_app_session_var(), set_app_session_var(varchar))
>>would it be likely to be accepted?
>
>
> It'd depend a lot on the details of what you propose, I think. True
> variables seem like they'd be rather invasive, not to mention possibly
> error-prone (is "foo" a variable or a column reference?). However you
> could do something pretty self-contained in the form of a couple of
> functions. I'd suggest they support more than just one variable, btw.
> How about "set_session_variable(varname, varvalue)" and
> "get_session_variable(varname)"?
>
> I should think we'd at least accept that as a contrib module; whether it
> would become mainstream would probably depend on the level of interest.

In the past, one hack would be to use setenv() and getenv() of the
backend to implement these functions. What about a contrib module that:

1) At set_session_variable(), a getenv() on the key
(PG_APP_SESSION_VAR) is made to see if memory has already been
allocated for a private variable map. If so, add the variable to the
map, else allocate a new map and set it's address using setenv().

2) At get_session_variable() a getenv() on the key
(PG_APP_SESSION_VAR) is made to see if a map exists. If so, it looks
into the map for the value of the name specified and returns it.

3) At get reset_session_variables() a getenv() on the key
(PG_APP_SESSION_VAR) is made to see if a map exists. If so, it is emptied.

This way, no change to any internal postgres code is required, the
memory allocated to the session-specific variables gets released at
backend closing, etc.

Would something like that be acceptable?

Mike Mascari
mascarm(at)mascari(dot)com

Mike Mascari <mascarm(at)mascari(dot)com> writes:
> In the past, one hack would be to use setenv() and getenv() of the
> backend to implement these functions. What about a contrib module that:

Uh, what exactly does it buy you to involve an environment variable
in this process? I think it just adds fragility. (For example,
exposing setenv to the user creates the risk that he'll overwrite
something of importance, like PATH.)

regards, tom lane

Tom Lane wrote:

> Mike Mascari <mascarm(at)mascari(dot)com> writes:
>
>>In the past, one hack would be to use setenv() and getenv() of the
>>backend to implement these functions. What about a contrib module that:
>
>
> Uh, what exactly does it buy you to involve an environment variable
> in this process? I think it just adds fragility. (For example,
> exposing setenv to the user creates the risk that he'll overwrite
> something of importance, like PATH.)

Actually, I meant that setenv() and getenv() would only be used to
store the memory address of a privately manipulated variable map. I
did not mean that it should actually be used to store and retrieve the
variables themselves. If there is already a way to palloc() memory
using a key that lives for the lifetime of a backend, then that's
obviously the way to go. I was proceeding under the assumption that
there wasn't.

Mike Mascari
mascarm(at)mascari(dot)com

Mike Mascari wrote:

> Tom Lane wrote:

>>Uh, what exactly does it buy you to involve an environment variable
>>in this process? I think it just adds fragility. (For example,
>>exposing setenv to the user creates the risk that he'll overwrite
>>something of importance, like PATH.)
>
> Actually, I meant that setenv() and getenv() would only be used to
> store the memory address of a privately manipulated variable map. I
> did not mean that it should actually be used to store and retrieve the
> variables themselves. If there is already a way to palloc() memory
> using a key that lives for the lifetime of a backend, then that's
> obviously the way to go. I was proceeding under the assumption that
> there wasn't.

It's been a long time since I've used global variables. Sorry...

Mike Mascari
mascarm(at)mascari(dot)com

Mike Mascari <mascarm(at)mascari(dot)com> writes:
> Actually, I meant that setenv() and getenv() would only be used to
> store the memory address of a privately manipulated variable map. I
> did not mean that it should actually be used to store and retrieve the
> variables themselves. If there is already a way to palloc() memory
> using a key that lives for the lifetime of a backend, then that's
> obviously the way to go.

Plain old static pointer variable would do fine ... look for instance at
the remote-connections table in contrib/dblink/dblink.c.

regards, tom lane