Sort by foreign date column

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

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