Re: [GENERAL] Sort by foreign date column
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-. When I do coversion to char in SELECT: TO_CHAR(chdate,'DD-MM-') 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-') 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-') FROM mytable ORDER BY EXTRACT(EPOCH from chdate); Hope that helps, Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] Question about ODBC!!
Hi, I try to use odbc on Digital unix platform . I installed iodbc as well and run the configuration with --enable-odbc option (pgsql v7.1.2), What shall I do for my driver manager (iodbc in this case), I know that I should write a .odbc.ini file for configuration, is that all? Because I have some problem to do connection to DB?! Please let me know, if there is something else I should do. If you have tried odbc installation, please let me know which steps you have followed! Thanks in advance, Fariba ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Printable report generation
For example, say I've got a postgresql database of client information (names, addresses, etc.), and I want to use this information to generate envelopes for mailouts. As far as my understanding goes, such functionality is not provided by postgresql itself. I have the same problem but I need a guide on JDBC serialization. I would like to put a button on a JSP that will save the result of a query to local disk as a tab delimited file. That will permit me to open it in AppleWorks for mailing label printing. Any pointers greatly appreciated Cheers Tony Grant -- RedHat Linux on Sony Vaio C1XD/S http://www.animaproductions.com/linux2.html Macromedia UltraDev with PostgreSQL http://www.animaproductions.com/ultra.html ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Postgresql backend error
I have PHP 4.06, PostgreSQL 7.1.2 on sparc-sun-solaris2.7, compiled by GCC 2.8.1, and Apache on a Solaris server. Most of the time all works as expected. Occasionally I get an database error: Database error There was a database error when accessing Database zwg: pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally before or while processing the request. (while evaluating: SELECT serializedobject FROM objects WHERE name='Zurich') [This error page is generated by my application; it displays the error reported by PostgreSQL and the SQL that caused the error]. serializedobject is a VARCHAR(1). The table consists of just one row at the moment. The query should always succeed (since that row has its name field equal to Zurich). The error is not caused by my SQL syntax, since this same code executes without a problem at other times. I don't know why I sometimes get this error, while most of the time it works fine. How do I start tracking down the cause? What circumstances should I be looking at to see what triggers it? Nigel Gilbert ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Sort by foreign date column
and how about this: SELECT TO_CHAR(chdate, 'DD-MM-') FROM mytable ORDER BY chdate::date; From: Mike Mascari [EMAIL PROTECTED] To: Andrey Y. Mosienko [EMAIL PROTECTED] CC: Stephan Szabo [EMAIL PROTECTED], Postgres [EMAIL PROTECTED] 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-. When I do coversion to char in SELECT: TO_CHAR(chdate,'DD-MM-') 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-') 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-') FROM mytable ORDER BY EXTRACT(EPOCH from chdate); Hope that helps, Mike Mascari [EMAIL PROTECTED] ---(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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Sort by foreign date column
omid omoomi wrote: and how about this: SELECT TO_CHAR(chdate, 'DD-MM-') 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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] Postgres hangs during VACUUM (autocommit = false)
Mark Coffman [EMAIL PROTECTED] writes: Yeah, I want autocommit off because there is a perl script that runs between the BEGIN and END blocks, and that script uses transactions. I just add the $dbh-commit(); to the END block to try to FORCE it to not be idle in transaction. I am having no luck. What I suspect is that autocommit off causes the DBD driver to send a fresh BEGIN immediately after the COMMIT. You might be better off with autocommit on which I think suppresses any automatic issuance of BEGIN/COMMIT. Then you'd need to issue BEGIN and COMMIT explicitly to turn your module into a transaction block. However, this theory doesn't completely explain your problem, because if there were a COMMIT and BEGIN coming out of the client, it'd show as idle in transaction afterwards, but it'd not be holding any locks and so couldn't block VACUUM. So there's something else going on. This is why I wanted to see a query log. (Easiest way to get one is to start postmaster with -d2 switch.) regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Postgres hangs during VACUUM (autocommit = false)
Mark Coffman \(Epilogue Programmer\) [EMAIL PROTECTED] writes:
Thanks for the reply. I use Perl and DBI to make my database conneciton.
In the
BEGIN {}
part of the module, I check for the persistant connection, and make one if
none is present, autocommit off
in the
END {}
block, I do a $dbh-commit();
Hmm. I wonder whether autocommit off does what you think it does;
it may be that you want the exact opposite.
You might want to turn on query logging and see just what the clients
are sending.
regards, tom lane
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Postgres hangs during VACUUM (autocommit = false)
Thanks for the reply. I use Perl and DBI to make my database conneciton.
In the
BEGIN {}
part of the module, I check for the persistant connection, and make one if
none is present, autocommit off
in the
END {}
block, I do a $dbh-commit();
They all still have idle in transaction
:(
- Original Message -
From: Tom Lane [EMAIL PROTECTED]
To: Mark Coffman [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Tuesday, August 21, 2001 6:28 PM
Subject: Re: [GENERAL] Postgres hangs during VACUUM (autocommit = false)
Mark Coffman [EMAIL PROTECTED] writes:
is there any way for me to schedule a VACUUM
that won't hang?
Fix your client-side code to not sit idle forever with uncommitted
transactions. That's a bad practice independently of whether it
gives VACUUM problems. The clients presumably think that whatever
they've done so far is committed ... but guess what, it's not.
I cannot autocommit because I need transactions.
Fine, but that means that you have the responsibility to commit.
Evidently you're not doing so.
FWIW, 7.2's default VACUUM will be more forgiving, but that doesn't
make your clients' behavior a good idea.
regards, tom lane
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Re: Postgres hangs during VACUUM (autocommit = false)
At 8/21/01 3:51 PM, Mark Coffman wrote: I am having trouble with setting up a scheduled VACUUM. Since VACUUM needs an exclusive lock, I can never get it to run. I am using persistant connections from a web server and use a single module to make the connection. I cannot autocommit because I need transactions. So a ps gives several similar processes: postgres 24298 0.6 1.1 6548 2964 ?S14:00 0:15 postgres: httpd epilogue [local] idle in transaction postgres 24345 0.7 1.1 6496 2984 ?S14:01 0:15 postgres: httpd epilogue [local] idle in transaction postgres 24391 0.9 1.1 6488 2900 ?S14:02 0:19 postgres: httpd epilogue [local] idle in transaction I've tried doing a $dbh-commit() ; but that does no good. I read all the posts I could find on this, but is there any way for me to schedule a VACUUM that won't hang? Where are you trying your $dbh-commit()? You need to put it at the end of the transactions from your Web server, even if the Web server only did a select and didn't modify anything. I had the same problem you're having, and found I wasn't using the commit after I did a select. Adding $dbh-commit() as the last thing the persistent Web client does, in all cases, fixed it. -- Robert L Mathews, Tiger Technologies ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Comparing fixed precision to floating
I am using numeric(p,s) fields in a database schema. Using queries that contain a comparison like ... where numericField = 456.789 will generate an error Unable to identify an operator '=' for types 'numeric' and 'float8' You will have to retype this query using an explicit cast and if i explicitly cast the 456.789 (456.789::numeric) it does in fact work. But how do we get around this error when using JDBC? Shouldn't =(numeric, float8) be a standard operator in postgresql? My query is a dynamically prepared statement in java where many of the constant values are user supplied and poped into the statement via pstmt.setObject(pos, valueObj, type) in the case of a numeric field the type parameter is Types.NUMERIC and the valueObj parameter is a java.math.BigDecimal. (java.math.BigDecimal is the only way I know of to represent fixed precision and scale number in java). And of course this will blow with the previous error. I do have a work around which is to force the user supplied constant (a BigDecimal) to a string and user pstmt.setString(...). Effectively this create a clause of the form ... where numericField = '456.789' but it postgres will automatically cast the right hand side to a numeric I would have expected it to be able to cast a float8 constant to a numeric as well. If there is good reason why this can't be done, could someone explain what I am missing. Else could we put a =(numeric, float8) operator on the todo list? Thanks, in advance for any help. Mike. === Mike Finn Tactical Executive Systems [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Postgres hangs during VACUUM (autocommit = false)
On Tue, Aug 21, 2001 at 07:19:42PM -0400, Tom Lane wrote: What I suspect is that autocommit off causes the DBD driver to send a fresh BEGIN immediately after the COMMIT. You might be better off with autocommit on which I think suppresses any automatic issuance of BEGIN/COMMIT. Then you'd need to issue BEGIN and COMMIT explicitly to turn your module into a transaction block. $ perldoc DBD::Pg - snip . . According to the DBI specification the default for AutoCommit is TRUE. In this mode, any change to the database becomes valid immediately. Any 'begin', 'commit' or 'rollback' statement will be rejected. If AutoCommit is switched-off, immediately a transaction will be started by issuing a 'begin' statement. Any 'commit' or 'rollback' will start a new transaction. A disconnect will issue a 'rollback' statement. - Suggestion to the original poster: don't use persistent connections then or else temporarily stop the front ends. Vacuuming locks the tables anyhow and they won't be able to access them during vacuuming ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Sort by foreign date column
Tom Lane wrote: On Tue, 21 Aug 2001, Andrey Y. Mosienko wrote: But in Russia we have the next date format: DD-MM-. 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/ 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: [EMAIL PROTECTED], [EMAIL PROTECTED] ICQ: 28073807 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Sort by foreign date column
Andrey Y. Mosienko [EMAIL PROTECTED] writes: If DD/MM/ 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
