Horton

Thread

  1. locking and web interfaces

    Dado Feigenblatt <dado@wildbrain.com> — 2001-10-23T17:08:23Z

    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.

  2. Re: locking and web interfaces

    Aasmund Midttun Godal <postgresql@envisity.com> — 2001-10-23T22:12:57Z

    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 locking so 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 is connected 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, any client 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

  3. Re: locking and web interfaces

    Dado Feigenblatt <dado@wildbrain.com> — 2001-10-24T01:58:57Z

    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 were locking 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 again is connected to the database server, and storing a cookie or similar to connect to that particular named pipe - Highly unrecommended.
>
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.

  4. Re: locking and web interfaces

    Alvaro Herrera <alvherre@atentus.com> — 2001-10-24T14:10:22Z

    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)

  5. plperl and table/record access

    Gordan Bobic <gordan@bobich.net> — 2001-10-24T16:14:05Z

    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

  6. Re: plperl and table/record access

    Alex Pilosov <alex@pilosoft.com> — 2001-10-24T17:25:11Z

    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)
> 
>

  7. Re: plperl and table/record access

    Gordan Bobic <gordan@bobich.net> — 2001-10-24T21:37:17Z

    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)