Thread: locking and web interfaces
Hi, I'm fairly new to this subject. How does locking work when accessing the database via web interfaces? Usually applications work in a browsing mode. When you click the edit button, the same data is presented in a form which can be submited for update. Is that necessary? If we don't consider the security problems of being in 'edit mode' all the time, do we need to keep switching between modes? And what I really want to know is that being web interfaces stateless, whatever builtin locking you implement it will drop as soon as the web server is done. Would persistent connections fix this? Do people use this mode switching to implement their own locks? Any pointers to literature on this? Thanks. PS: Should I send this to SQL? Whats the policy on cross-posting? -- Dado Feigenblatt Wild Brain, Inc. Technical Director (415) 216-2053 dado@wildbrain.com San Francisco, CA.
1. Could you perhaps edit individual rows instead of the whole table. 2. If you use persistant connections ala Apache::DBI you may not get back to the same child you talked to when you were lockingso somebody doing something else would get the locked connection. 3. You could make som sort of named connection jumping through hoops - hook up to a client via a named pipe which again isconnected to the database server, and storing a cookie or similar to connect to that particular named pipe - Highly unrecommended. 4. You could create a virtual locking table which says what table is locked by who, before allowing a new virtual lock, anyclient would check with that table. 5. Any kind of locking is often undesirable. remember postgresql supports mvcc!! Regards, Aasmund On Tue, 23 Oct 2001 10:08:23 -0700, Dado Feigenblatt <dado@wildbrain.com> wrote: > Hi, I'm fairly new to this subject. > > How does locking work when accessing the database via web interfaces? > Usually applications work in a browsing mode. When you click the edit > button, the same data is presented in a form which can be submited for > update. > Is that necessary? If we don't consider the security problems of being > in 'edit mode' all the time, do we need to keep switching between modes? > And what I really want to know is that being web interfaces stateless, > whatever builtin locking you implement it will drop as soon as the web > server is done. > Would persistent connections fix this? Do people use this mode switching > to implement their own locks? > > Any pointers to literature on this? > > Thanks. > > PS: Should I send this to SQL? Whats the policy on cross-posting? > > -- > Dado Feigenblatt Wild Brain, Inc. > Technical Director (415) 216-2053 > dado@wildbrain.com San Francisco, CA. > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster Aasmund Midttun Godal aasmund@godal.com - http://www.godal.com/ +47 40 45 20 46
Aasmund Midttun Godal wrote: >1. Could you perhaps edit individual rows instead of the whole table. > I'm not planning on locking tables, just rows. >2. If you use persistant connections ala Apache::DBI you may not get back to the same child you talked to when you werelocking so somebody doing something else would get the locked connection. > That is true. Subsequente accesses don't necessarily use the same connection. But the lock would still be in place for the same conection, uh? Does anybody know of a Apache/Python (mod_python?) that would allow me to do that? I mean create a permanent connection and reserve it for exclusive use? Does this kind of thing exist or is even allowed? >3. You could make som sort of named connection jumping through hoops - hook up to a client via a named pipe which againis connected to the database server, and storing a cookie or similar to connect to that particular named pipe - Highlyunrecommended. > I'm not going there. >4. You could create a virtual locking table which says what table is locked by who, before allowing a new virtual lock,any client would check with that table. > I thought about it, but I have never seen anything about implementing it. Are there any examples? >5. Any kind of locking is often undesirable. remember postgresql supports mvcc!! > Why? I thought locking was the only way to go in multi-user environments. Forgive my ignorance. What is mvcc? Thanks >Regards, > >Aasmund > >On Tue, 23 Oct 2001 10:08:23 -0700, Dado Feigenblatt <dado@wildbrain.com> wrote: > >>Hi, I'm fairly new to this subject. >> >>How does locking work when accessing the database via web interfaces? >>Usually applications work in a browsing mode. When you click the edit >>button, the same data is presented in a form which can be submited for >>update. >>Is that necessary? If we don't consider the security problems of being >>in 'edit mode' all the time, do we need to keep switching between modes? >>And what I really want to know is that being web interfaces stateless, >>whatever builtin locking you implement it will drop as soon as the web >>server is done. >>Would persistent connections fix this? Do people use this mode switching >>to implement their own locks? >> >>Any pointers to literature on this? >> >>Thanks. >> >>PS: Should I send this to SQL? Whats the policy on cross-posting? >> >>-- >>Dado Feigenblatt Wild Brain, Inc. >>Technical Director (415) 216-2053 >>dado@wildbrain.com San Francisco, CA. >> >> >> >>---------------------------(end of broadcast)--------------------------- >>TIP 4: Don't 'kill -9' the postmaster >> > >Aasmund Midttun Godal > >aasmund@godal.com - http://www.godal.com/ >+47 40 45 20 46 > > -- Dado Feigenblatt Wild Brain, Inc. Technical Director (415) 216-2053 dado@wildbrain.com San Francisco, CA.
On Tue, 23 Oct 2001, Dado Feigenblatt wrote: > Hi, I'm fairly new to this subject. > > How does locking work when accessing the database via web interfaces? > Usually applications work in a browsing mode. When you click the edit > button, the same data is presented in a form which can be submited for > update. There was some discussion not long ago on this list about this subject. IIRC there was almost one solution for every poster. Maybe you can take a look at the archives, http://fts.postgresql.org/db/mw/msg.html?mid=1031482 > Would persistent connections fix this? Do people use this mode switching > to implement their own locks? No, persistent connections do not help here. -- Alvaro Herrera (<alvherre[@]atentus.com>) "Ninguna manada de bestias tiene una voz tan horrible como la humana" (Orual)
Hi. How do I manipulate data in tables from within plperl? Can I, for example, issue SQL statements from within plperl functions? I haven't managed to find any documentation on this subject. Can anyone point me in the correct direction, please? I'm trying to write some trigger functions... I have seen an example with the plperl function that performs a calculation on each row of a table, but it is not explained anywhere how to insert records, or how to locate specific records without having to loop through the entire database. I presume that DBI will not work, given the need for setting up separate database connections, authentication, etc. Thanks. Gordan
See DBD::PgSPI on CPAN, or www.formenos.org/PgSPI
Example:
create function dbd_pgspi_test() returns varchar as '
use DBD::PgSPI;
my @ar=$pg_dbh->selectrow_array(''select current_user'');
return $ar[0];
' language 'plperlu';
Currently, it does not support triggers. (Well, it supports triggers, but
there's no way to access trigger arguments).
On Wed, 24 Oct 2001, Gordan Bobic wrote:
> Hi.
>
> How do I manipulate data in tables from within plperl? Can I, for example,
> issue SQL statements from within plperl functions? I haven't managed to find
> any documentation on this subject. Can anyone point me in the correct
> direction, please? I'm trying to write some trigger functions...
>
> I have seen an example with the plperl function that performs a calculation
> on each row of a table, but it is not explained anywhere how to insert
> records, or how to locate specific records without having to loop through the
> entire database. I presume that DBI will not work, given the need for setting
> up separate database connections, authentication, etc.
>
> Thanks.
>
> Gordan
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
Thank you for the reply.
I seem to be having some difficulty with this module.
1) It doesn't install in the default RPM setup where the main PostgreSQL root
directory doesn't exist as such. The Makefile.PL expects the include and
libpq directories to all be under one directory, which isn't the case. I have
worked around this by making a /usr/local/postgresql directory and linking
the required directories into it, and then exporting POSTGRES_HOME to this
directory.
However, I then get the following error upon installation:
gcc -c -I/usr/local/postgresql/include -I/usr/local/postgresql/backend
-I/usr/local/postgresql/interfaces/libpq
-I/usr/lib/perl5/site_perl/5.6.0/i386-linux/auto/DBI -fno-strict-aliasing -g
-DVERSION=\"0.01\" -DXS_VERSION=\"0.01\" -fPIC
-I/usr/lib/perl5/5.6.0/i386-linux/CORE PgSPI.c
In file included from
/usr/lib/perl5/site_perl/5.6.0/i386-linux/auto/DBI/DBIXS.h:19,
from PgSPI.h:21,
from PgSPI.xs:14:
/usr/lib/perl5/5.6.0/i386-linux/CORE/perl.h:3205: redefinition of `union
semun'
make: *** [PgSPI.o] Error 1
What am I doing wrong?
Regards.
Gordan
On Wednesday 24 Oct 2001 18:25, Alex Pilosov wrote:
> See DBD::PgSPI on CPAN, or www.formenos.org/PgSPI
>
> Example:
> create function dbd_pgspi_test() returns varchar as '
> use DBD::PgSPI;
> my @ar=$pg_dbh->selectrow_array(''select current_user'');
> return $ar[0];
> ' language 'plperlu';
>
> Currently, it does not support triggers. (Well, it supports triggers, but
> there's no way to access trigger arguments).
>
> On Wed, 24 Oct 2001, Gordan Bobic wrote:
> > Hi.
> >
> > How do I manipulate data in tables from within plperl? Can I, for
> > example, issue SQL statements from within plperl functions? I haven't
> > managed to find any documentation on this subject. Can anyone point me in
> > the correct direction, please? I'm trying to write some trigger
> > functions...
> >
> > I have seen an example with the plperl function that performs a
> > calculation on each row of a table, but it is not explained anywhere how
> > to insert records, or how to locate specific records without having to
> > loop through the entire database. I presume that DBI will not work, given
> > the need for setting up separate database connections, authentication,
> > etc.
> >
> > Thanks.
> >
> > Gordan
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)