Thread: Sort by foreign date column
I have table with date type column: chdate date; SELECT chdate from mytable; chdate 1999-01-02 But in Russia we have the next date format: DD-MM-YYYY. When I do coversion to char in SELECT: TO_CHAR(chdate,'DD-MM-YYYY') everything is ok, but sort by this column executes as sort for char type. How can I display my native date format and do right sorting by this column? -- with respection Andrey Feofilactovich. e-mail: feo@ttn.ru, feo@feo.org.ru ICQ: 28073807
On Tue, 21 Aug 2001, Andrey Y. Mosienko wrote: > > I have table with date type column: > > chdate date; > > SELECT chdate from mytable; > > chdate > 1999-01-02 > > But in Russia we have the next date format: DD-MM-YYYY. > > When I do coversion to char in SELECT: > TO_CHAR(chdate,'DD-MM-YYYY') everything is ok, but sort by this column > executes as sort for char type. > > How can I display my native date format and do right sorting by this column? Wouldn't select to_char(chdate, 'DD-MM-YYYY') from mytable order by chdate; work?
Stephan Szabo wrote: > > On Tue, 21 Aug 2001, Andrey Y. Mosienko wrote: > > > > > I have table with date type column: > > > > chdate date; > > > > SELECT chdate from mytable; > > > > chdate > > 1999-01-02 > > > > But in Russia we have the next date format: DD-MM-YYYY. > > > > When I do coversion to char in SELECT: > > TO_CHAR(chdate,'DD-MM-YYYY') everything is ok, but sort by this column > > executes as sort for char type. > > > > How can I display my native date format and do right sorting by this column? > > Wouldn't > select to_char(chdate, 'DD-MM-YYYY') from mytable order by chdate; > work? Works. But sorting performs as for CHAR TYPE! -- with respection Andrey Feofilactovich. e-mail: feo@ttn.ru, feo@feo.org.ru ICQ: 28073807
"Andrey Y. Mosienko" wrote: > > Stephan Szabo wrote: > > > > On Tue, 21 Aug 2001, Andrey Y. Mosienko wrote: > > > > > > > > I have table with date type column: > > > > > > chdate date; > > > > > > SELECT chdate from mytable; > > > > > > chdate > > > 1999-01-02 > > > > > > But in Russia we have the next date format: DD-MM-YYYY. > > > > > > When I do coversion to char in SELECT: > > > TO_CHAR(chdate,'DD-MM-YYYY') everything is ok, but sort by this column > > > executes as sort for char type. > > > > > > How can I display my native date format and do right sorting by this column? > > > > Wouldn't > > select to_char(chdate, 'DD-MM-YYYY') from mytable order by chdate; > > work? > > Works. But sorting performs as for CHAR TYPE! I don't know why the sorting isn't functioning correctly for the 'date' data type (locale issue?), but this should definitely work: SELECT TO_CHAR(chdate, 'DD-MM-YYYY') FROM mytable ORDER BY EXTRACT(EPOCH from chdate); Hope that helps, Mike Mascari mascarm@mascari.com
and how about this: SELECT TO_CHAR(chdate, 'DD-MM-YYYY') FROM mytable ORDER BY chdate::date; >From: Mike Mascari <mascarm@mascari.com> >To: "Andrey Y. Mosienko" <feo@ttn.ru> >CC: Stephan Szabo <sszabo@megazone23.bigpanda.com>, Postgres ><pgsql-general@postgresql.org> >Subject: Re: [GENERAL] Sort by foreign date column >Date: Tue, 21 Aug 2001 03:16:55 -0400 > >"Andrey Y. Mosienko" wrote: > > > > Stephan Szabo wrote: > > > > > > On Tue, 21 Aug 2001, Andrey Y. Mosienko wrote: > > > > > > > > > > > I have table with date type column: > > > > > > > > chdate date; > > > > > > > > SELECT chdate from mytable; > > > > > > > > chdate > > > > 1999-01-02 > > > > > > > > But in Russia we have the next date format: DD-MM-YYYY. > > > > > > > > When I do coversion to char in SELECT: > > > > TO_CHAR(chdate,'DD-MM-YYYY') everything is ok, but sort by this >column > > > > executes as sort for char type. > > > > > > > > How can I display my native date format and do right sorting by this >column? > > > > > > Wouldn't > > > select to_char(chdate, 'DD-MM-YYYY') from mytable order by chdate; > > > work? > > > > Works. But sorting performs as for CHAR TYPE! > >I don't know why the sorting isn't functioning correctly for the >'date' data type (locale issue?), but this should definitely work: > >SELECT TO_CHAR(chdate, 'DD-MM-YYYY') >FROM mytable >ORDER BY EXTRACT(EPOCH from chdate); > >Hope that helps, > >Mike Mascari >mascarm@mascari.com > >---------------------------(end of broadcast)--------------------------- >TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/users-lounge/docs/faq.html _________________________________________________________________ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
omid omoomi wrote:
>
> and how about this:
>
> SELECT TO_CHAR(chdate, 'DD-MM-YYYY')
> FROM mytable
> ORDER BY chdate::date;
Yes, but Andrey says that the chdate field is declared as a date:
> > > > On Tue, 21 Aug 2001, Andrey Y. Mosienko wrote:
> > > >
> > > > >
> > > > > I have table with date type column:
^^^^
> > > > >
> > > > > chdate date;
so if that is the case, something is broken.
Mike Mascari
mascarm@mascari.com
On Tue, 21 Aug 2001, Andrey Y. Mosienko wrote:
> Stephan Szabo wrote:
> >
> > On Tue, 21 Aug 2001, Andrey Y. Mosienko wrote:
> >
> > >
> > > I have table with date type column:
> > >
> > > chdate date;
> > >
> > > SELECT chdate from mytable;
> > >
> > > chdate
> > > 1999-01-02
> > >
> > > But in Russia we have the next date format: DD-MM-YYYY.
> > >
> > > When I do coversion to char in SELECT:
> > > TO_CHAR(chdate,'DD-MM-YYYY') everything is ok, but sort by this column
> > > executes as sort for char type.
> > >
> > > How can I display my native date format and do right sorting by this column?
> >
> > Wouldn't
> > select to_char(chdate, 'DD-MM-YYYY') from mytable order by chdate;
> > work?
>
> Works. But sorting performs as for CHAR TYPE!
What version are you using? For me it orders by date.
sszabo=> create table datetest (d date);
CREATE
sszabo=> insert into datetest values ('12/11/2001');
INSERT 798850 1
sszabo=> insert into datetest values ('11/12/2001');
INSERT 798851 1
sszabo=> select * from datetest order by d;
d
------------
2001-11-12
2001-12-11
(2 rows)
sszabo=> select TO_CHAR(d, 'DD-MM-YYYY') from datetest order by d asc;
to_char
------------
12-11-2001
11-12-2001
(2 rows)
which appears to me to be date ordered not char ordered.
> On Tue, 21 Aug 2001, Andrey Y. Mosienko wrote:
> But in Russia we have the next date format: DD-MM-YYYY.
Just setting DateStyle to 'SQL' would get you approximately what you
want:
regression=# set DateStyle TO SQL;
SET VARIABLE
regression=# select now();
now
----------------------------
21/08/2001 10:07:04.00 EDT
(1 row)
regression=# select now()::date;
?column?
------------
21/08/2001
(1 row)
If DD/MM/YYYY is not close enough for you, maybe the right answer is to
add another DateStyle.
regards, tom lane
Tom Lane wrote: > > > On Tue, 21 Aug 2001, Andrey Y. Mosienko wrote: > > But in Russia we have the next date format: DD-MM-YYYY. > > Just setting DateStyle to 'SQL' would get you approximately what you > want: > > regression=# set DateStyle TO SQL; > SET VARIABLE > regression=# select now(); > now > ---------------------------- > 21/08/2001 10:07:04.00 EDT > (1 row) > > regression=# select now()::date; > ?column? > ------------ > 21/08/2001 > (1 row) > > If DD/MM/YYYY is not close enough for you, maybe the right answer is to > add another DateStyle. Sorry for disturbing, but how can I do this or where is it in documentation? -- with respection Andrey Feofilactovich. e-mail: feo@ttn.ru, feo@feo.org.ru ICQ: 28073807
"Andrey Y. Mosienko" <feo@ttn.ru> writes:
>> If DD/MM/YYYY is not close enough for you, maybe the right answer is to
>> add another DateStyle.
> Sorry for disturbing, but how can I do this or where is it in documentation?
It's not documented; you'd have to dig into the code and see how the
existing datestyles are done.
regards, tom lane
Hi all, I have a problem related with the maximum query length ERR: query is too long. Maximum length is 16382 I'm using PostgreSQL 6.5.3 and python Is this limit in the newer releases of PostgreSQL too? Thanks for any help Jose Soares
> Hi all, > > I have a problem related with the maximum query length > ERR: query is too long. Maximum length is 16382 > I'm using PostgreSQL 6.5.3 and python > Is this limit in the newer releases of PostgreSQL too? > Thanks for any help All those limits are gone. Upgrade to 7.1.3. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Hi all, I'm trying SELECT FOR UPDATE but I have a problem with locks. example: I have two users accessing to the same records, say... user1: DELECT * FROM table WHERE field=value FOR UPDATE --------------------------------------------------------------------------- user2: DELECT * FROM table WHERE field=value FOR UPDATE at this point user2 can't do nothing except waiting for user1 unlock recors. My question is: - Is it possible to know in advance if the records of a given query are locked, to prevent to be locked for ever? Thank for any help, Jose Soares
No. ----- Original Message ----- From: "jose" <jose@sferacarta.com> To: "Postgres" <pgsql-general@postgresql.org> Sent: Wednesday, August 22, 2001 3:51 AM Subject: [GENERAL] maximum query length > Hi all, > > I have a problem related with the maximum query length > ERR: query is too long. Maximum length is 16382 > I'm using PostgreSQL 6.5.3 and python > Is this limit in the newer releases of PostgreSQL too? > Thanks for any help > > Jose Soares > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
jose wrote:
> Hi all,
>
> I'm trying SELECT FOR UPDATE
> but I have a problem with locks.
>
> example:
> I have two users accessing to the same records, say...
>
> user1:
> DELECT * FROM table WHERE field=value FOR UPDATE
> ---------------------------------------------------------------------------
>
> user2:
> DELECT * FROM table WHERE field=value FOR UPDATE
> at this point user2 can't do nothing except waiting for user1 unlock recors.
>
> My question is:
>
> - Is it possible to know in advance if the records of a given query are
> locked, to prevent to be locked for ever?
Well, first of all the scientists are still unsure if this
universe will exist forever or not. So even if you find a way
to let your system survive the sun becoming a red giant and
so on, there's still uncertainty if it'll take forever or
not.
Anyway, the answer is no. Standard SQL doesn't have any
mechanism to check wether a given row is locked or to force a
query to fail with an error in the case a required lock isn't
available immediately.
But the question itself tells that you're about to implement
a major design error in your application. Holding database
locks during user interaction IS A BAD THING. Never, never
ever do it that way. And anybody telling you something
different is an overpaid idiot.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
On Wed, Aug 22, 2001 at 09:01:10PM -0400, Jan Wieck wrote:
> jose wrote:
> > - Is it possible to know in advance if the records of a given query are
> > locked, to prevent to be locked for ever?
> Anyway, the answer is no. Standard SQL doesn't have any
> mechanism to check wether a given row is locked or to force a
> query to fail with an error in the case a required lock isn't
> available immediately.
>
> But the question itself tells that you're about to implement
> a major design error in your application. Holding database
> locks during user interaction IS A BAD THING. Never, never
> ever do it that way. And anybody telling you something
> different is an overpaid idiot.
"never ever do it THAT way." okay.
what way SHOULD we do it?
--
Khan said that revenge is a dish best served cold. I think
sometimes it's best served hot, chunky, and foaming.
- P.J.Lee ('79-'80)
will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
Jan Wieck wrote: > But the question itself tells that you're about to implement > a major design error in your application. Holding database > locks during user interaction IS A BAD THING. Never, never > ever do it that way. And anybody telling you something > different is an overpaid idiot. I can see arguments to support this view, but consider this classic scenario: User1: Read data into an interactive program User1: Start to make changes User2: Read data into an interactive program User2: Start to make changes User1: Save changes User2: Save changes With no locks, both users will have the same original data, but User1's changes will not be seen by User2 and will therefore be lost. Alternatively, if transactions are used, User2's changes will be rolled back and lost. Therefore it is necessary to use SELECT FOR UPDATE with isolation level READ COMMITTED so that User2 will see and not overwrite User1's simultaneous changes. One way out is to do SELECT when reading and a SELECT FOR UPDATE just before saving; if the row has changed, the user is warned and must redo his changes -- but this could lead to the loss of a lot of editing. I have used a compromise in my programming: read with SELECT, then reread with SELECT FOR UPDATE on the first change. This reduces the risk of locking, though it still leaves the possibility open. This could be refined by having the application time out if it is left untouched for too long (user gets a phone call, forgets he has a record open and goes to lunch). Can you suggest a better way of handling this problem? It would need to balance better the risk of locking against the risk of losing interactive editing. It would be nice to have a lock timeout, for example: SET TIMEOUT ON LOCK TO 5 with the default being a long enough time for it not to timeout on normal transient locks. Then SELECT FOR UPDATE would timeout after the set period and return an error so that the application could regain control. However, I don't know how feasible this is. My ideal would be for SELECT FOR UPDATE to timeout with a message: "table t primary key k locked by backend with PID ppppp" (using oid if there is no primary key). -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "For God hath not appointed us to wrath, but to obtain salvation by our Lord Jesus Christ, Who died for us, that, whether we wake or sleep, we should live together with him." I Thessalonians 5:9,10
> > But the question itself tells that you're about to implement > > a major design error in your application. Holding database > > locks during user interaction IS A BAD THING. Never, never > > ever do it that way. And anybody telling you something > > different is an overpaid idiot. > > I can see arguments to support this view, but consider this classic > scenario: > > User1: Read data into an interactive program > User1: Start to make changes > User2: Read data into an interactive program > User2: Start to make changes > User1: Save changes > User2: Save changes > > With no locks, both users will have the same original data, but User1's > changes will not be seen by User2 and will therefore be lost. This is an unavoidable consequence of any multi-user system. If two (or more) users can modify the same record, they have to understand that someone else may be modifying that record. This shouldn't be a problem though because UPDATE statements only affect the fields that the user wants to update. User2 will only overwrite changes made by User1 if they both wanted those changes made. There are a few instances where changes might affect how User2 might edit the record, but the only ones I can think of are cumulative changes (for instance, an account balance). Those *do* need to be updated in some sort of critical section, such as that provided by SELECT FOR UPDATE, but I don't think they require attention from User2 unless some special situation occurs (the account would now be overdrawn). > Alternatively, if transactions are used, User2's changes will be > rolled back and lost. Why are they lost? The client should check to see if the transaction succeeds. If not, then it handles the situation in whatever manner makes the best sense. I would think handling an error on UPDATE is much more graceful for both the users and the system than locking the record while a user (who could simply step away from his computer for more coffee) holds the lock indefinitely. > One way out is to do SELECT when reading and a SELECT FOR UPDATE just > before saving; if the row has changed, the user is warned and must > redo his changes -- but this could lead to the loss of a lot of editing. Also, a lengthy WHERE clause in the UPDATE can do the same. In other words, don't just use the primary key but all the fields. If the record doesn't EXACTLY match the WHERE statement, it can't be updated. But a failure to update doesn't mean a loss to editing. It is the client's responsibility to keep that data for as long as the user wants it. Let the client say "Record couldn't be updated, some information has changed. Here is a summary of the changes: xxx. Press Ok to continue, Cancel to modify your changes." > My ideal would be for SELECT FOR UPDATE to timeout with a message: > "table t primary key k locked by backend with PID ppppp" > (using oid if there is no primary key). Personally as a user I'd rather handle the conflict resolution than be locked out of records entirely "Another user is modifying this record. Please twiddle your thumbs and try again in a few minutes" Ugh. I can only imagine how badly my users would badmouth me if they got a message like that above... Greg
I prefer the way Notes (for example) handles it. All records/documents/views are in read-only mode until the user indicates they actually want to edit. They then exclusively lock that record for editing, with optional timeouts (in case their workstation crashes or whatever). This turns out to work well in many situations where you realise the number of times you want to edit compared to the number of times you want to view, is quite small. Stops users having to worry whether anyone else is editing the record at the same time - the system simply won't let them - no loss of data. Andrew
Oliver Elphick wrote:
> Jan Wieck wrote:
> > But the question itself tells that you're about to implement
> > a major design error in your application. Holding database
> > locks during user interaction IS A BAD THING. Never, never
> > ever do it that way. And anybody telling you something
> > different is an overpaid idiot.
>
> I can see arguments to support this view, but consider this classic
> scenario:
>
> User1: Read data into an interactive program
> User1: Start to make changes
> User2: Read data into an interactive program
> User2: Start to make changes
> User1: Save changes
> User2: Save changes
All ERP systems I know deal with that issue by inserting and
deleting some advisory lock information in another table.
Let's say you want to change customers 4711 address. Before
letting you do so on the edit screen, the application tries
to insert "CUST.4711" into a central lock table. Now this
thing has a unique index on that field, so if someone else is
already editing 4711, it'll fail and the application can tell
you so and won't let you do the same.
AFAIK it's the only way to deal with that problem. Think
about scaling as well. No enterprise class software has a DB
connection per interactive user. They all have some sort of
DB-middletear-presentation model where many users share a few
DB connections.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
On Thu, Aug 23, 2001 at 10:09:19AM -0400, Jan Wieck wrote:
> Oliver Elphick wrote:
> > I can see arguments to support this view, but consider this classic
> > scenario:
> >
> > User1: Read data into an interactive program
> > User1: Start to make changes
> > User2: Read data into an interactive program
> > User2: Start to make changes
> > User1: Save changes
> > User2: Save changes
Consider replacing "Save changes" with:
User1: Lock record, compare original with current record, save if same, unlock
User2: Lock record, compare original with current record, notice difference, abort.
So, yes, 3 buffers: One for original record, one for modified record, one
to hold record for comparison (during lock).
mrc
--
Mike Castle dalgoda@ix.netcom.com www.netcom.com/~dalgoda/
We are all of us living in the shadow of Manhattan. -- Watchmen
fatal ("You are in a maze of twisty compiler features, all different"); -- gcc
Mike Castle wrote:
>On Thu, Aug 23, 2001 at 10:09:19AM -0400, Jan Wieck wrote:
>> Oliver Elphick wrote:
>> > I can see arguments to support this view, but consider this classic
>> > scenario:
>> >
>> > User1: Read data into an interactive program
>> > User1: Start to make changes
>> > User2: Read data into an interactive program
>> > User2: Start to make changes
>> > User1: Save changes
>> > User2: Save changes
>
>Consider replacing "Save changes" with:
>
>User1: Lock record, compare original with current record, save if same, unlo
>ck
>User2: Lock record, compare original with current record, notice difference,
> abort.
Yes, but if User2 has done substantial editing changes to a field (after all
we could store whole books in a SQL field now), his changes will be rejected
and the program will have to throw them away or else try to integrate them
with the new field contents - in either case there is substantial wasted
effort.
I prefer Jan's solution: on first attempt to change, acquire a user-level
lock by creating a lock record; if you can't get the lock, don't allow
any change.
However, it would be convenient if the database would do this for me. I
still don't understand why people think it undesirable for it to do so, since
it is a problem universal to multi-user databases and the effort is
therefore more economically spent at the database rather than at the
application level.
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"For God hath not appointed us to wrath, but to obtain
salvation by our Lord Jesus Christ, Who died for us,
that, whether we wake or sleep, we should live
together with him."
I Thessalonians 5:9,10
A 'document' may consist of several records from several tables, a relationship the DBMS doesn't understand. It also introduces alot of complexity. You may need a way to expire locks after a certain amount of time, or allow some users to override other users' locks. In a system I'm working on, I have a facility for both, and the expiration code works in two steps. At creation, the lock is 'mandatory' and can only be overridden by an admin user. After a day of inactivity against the lock, it becomes 'advisory', meaning that anyone can override it, but if it is not overidden it remains valid for the owner for one week, after which time it is removed altogether. Stuff like that is, IMHO, far beyond the scope of an SQL server. Glen Parker glenebob@nwlink.com > I prefer Jan's solution: on first attempt to change, acquire a user-level > lock by creating a lock record; if you can't get the lock, don't allow > any change. > > However, it would be convenient if the database would do this for me. I > still don't understand why people think it undesirable for it to > do so, since > it is a problem universal to multi-user databases and the effort is > therefore more economically spent at the database rather than at the > application level.
Oliver Elphick wrote:
> However, it would be convenient if the database would do this for me. I
> still don't understand why people think it undesirable for it to do so, since
> it is a problem universal to multi-user databases and the effort is
> therefore more economically spent at the database rather than at the
> application level.
Because you'd need a dedicated DB connection per logged in
user (think about that in big ERP systems with 10,000+ logged
in users - would like to see that DB server). Any web server
technology has or is seeking for database connection pooling
these days, and they don't do it just because all these
multi-threading issues are such a fun to resolve.
Just because it'd be convenient for application programmers
doesn't make something a good solution.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
Jan Wieck wrote: >Oliver Elphick wrote: > >>Jan Wieck wrote: >> > But the question itself tells that you're about to implement >> > a major design error in your application. Holding database >> > locks during user interaction IS A BAD THING. Never, never >> > ever do it that way. And anybody telling you something >> > different is an overpaid idiot. >> >>I can see arguments to support this view, but consider this classic >>scenario: >> >>User1: Read data into an interactive program >>User1: Start to make changes >>User2: Read data into an interactive program >>User2: Start to make changes >>User1: Save changes >>User2: Save changes >> > > All ERP systems I know deal with that issue by inserting and > deleting some advisory lock information in another table. > Let's say you want to change customers 4711 address. Before > letting you do so on the edit screen, the application tries > to insert "CUST.4711" into a central lock table. Now this > thing has a unique index on that field, so if someone else is > already editing 4711, it'll fail and the application can tell > you so and won't let you do the same. > Unfortunatelly this aproach have a problem. What about if the backend or the application crashes in the middle of editing? This could also be done by adding a field in the record itself and set it every time you edit it and unset it after the edit time. In this case you need to update the record every time you read it :( This job should be done by the DB itself, perhaps this is the way it works right now! > AFAIK it's the only way to deal with that problem. Think > about scaling as well. No enterprise class software has a DB > connection per interactive user. They all have some sort of > DB-middletear-presentation model where many users share a few > DB connections. > Jose Soares
> > All ERP systems I know deal with that issue by inserting and > > deleting some advisory lock information in another table. > > Let's say you want to change customers 4711 address. Before > > letting you do so on the edit screen, the application tries > > to insert "CUST.4711" into a central lock table. Now this > > thing has a unique index on that field, so if someone else is > > already editing 4711, it'll fail and the application can tell > > you so and won't let you do the same. > > > Unfortunatelly this aproach have a problem. > What about if the backend or the application crashes in the middle of > editing? Hooray for persistent locks! :-) It's a good thing, or I like it anyway. Backend crashes don't stop a user from doing his/her work. They continue on, and if the post fails because the backend is down, just try again when the backend is back up, no loss of data. In case the application crashes, you just have to have a job run periodically to clean out stale locks (means locks must be timestamped which is one reason I don't like to add lock fields to the main data tables), and give someone a tool for removing abandoned locks if needed. Glen > This could also be done by adding a field in the record itself and set it > every time you edit it and unset it after the edit time. > In this case you need to update the record every time you read it :( > This job should be done by the DB itself, perhaps this is the way it > works right now! > > > AFAIK it's the only way to deal with that problem. Think > > about scaling as well. No enterprise class software has a DB > > connection per interactive user. They all have some sort of > > DB-middletear-presentation model where many users share a few > > DB connections. > >
jose wrote:
> Jan Wieck wrote:
>
> > All ERP systems I know deal with that issue by inserting and
> > deleting some advisory lock information in another table.
> > Let's say you want to change customers 4711 address. Before
> > letting you do so on the edit screen, the application tries
> > to insert "CUST.4711" into a central lock table. Now this
> > thing has a unique index on that field, so if someone else is
> > already editing 4711, it'll fail and the application can tell
> > you so and won't let you do the same.
> >
> Unfortunatelly this aproach have a problem.
> What about if the backend or the application crashes in the middle of
> editing?
>
> This could also be done by adding a field in the record itself and set it
> every time you edit it and unset it after the edit time.
> In this case you need to update the record every time you read it :(
> This job should be done by the DB itself, perhaps this is the way it
> works right now!
It is not the way it works now and if you want to pool
database connections it cannot be done by the database.
The advantage of having a central lock table where the key
contains the object type and primary key is, that
administrative functions (for resetting the user lock maybe?)
must not be touched if you add more object types. With the
correct structure you'll have the information who holds the
lock, since when and whatnot as well, to make the admin
happy. And you don't have to rely on non-SQL-standard
features!
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
> It seems to me that SELECT ... FOR UPDATE is not the way to go if > it is possible that the selected record may be held for any length > of time. But transactions are supposed to occur very quickly. > For instance, say you are storing web pages in the database, and you > want a number of developers to be able to get pages from the database > look at them, and possibly make changes and update the record. In this case, I would use webDAV, not a RDBMS, unless I needed relations between the documents that I couldn't get by simply using a directory structure. > So, the lock table would need a time-out field, and might also > include a queue of people waiting for the record. Yes, this is what confuses me most about Jan's post. The lock table functionally resides between the RDMBS and the application/user, and in doing so it defeats the purpose of an RDBMS. What should I use triggers, rules, and listen/notify for then? I guess LISTEN/NOTIFY could be used in conjunction with the lock table. > This is just how I was thinking of it, and again I think it will > depend on the application. At some point, you're not making a > database, you're creating a cvs. Though a hybrid could be good.... Exactly, your not making a database, and as for the hybrid... This is why XML is such overhyped shit right now, nobody likes/trusts RDBMS's.
I just finished reading Bruce M's book, so this thread confuses me, esp. Jan's posts. I take full heed of the need for application level user/thread management, but I was interested in using a parallel set-up in PG (however redundant that might be). Now that Jan has discounted "SELECT...FOR UPDATE," is the best alternative using a central locking table (perhaps in conjunction with LISTEN & NOTIFY)? Ironically, anyone who suggested using application level transactions would be torn apart at any of the places I've worked at--but that seems to be the gist of this thread. I cannot see a way to avoid deadlocks without an application level transaction component, since the central locking table idea would similarily lock the record forever if the first transaction failed to COMMIT or ROLLBACK. What is the saying: To the beginner, there are many options. To the wise, there are few.
Hi,
I found a strange behavior in PostgreSQL.
I created a table as user pippo then I created a view based upon this
tables,
latter someone drops user pippo and now when I do:
select from view PostgreSQL show me this:
pg_aclcheck: invalid user id #
I suppose it is because the user pippo the owner of this view, is any
more in the database
but if I do select from table it works fine.
Is this a bug?
Jose
>
>
>