Re: [BUGS] BUG #1637: age() function is giving different results
Andrew - Supernews <[EMAIL PROTECTED]> writes: > I did some analysis for this one when it was mentioned just now in the irc > chan. I can reproduce on 7.4.x as follows: > test=> set timezone to 'America/Buenos_Aires'; > SET > test=> select age(date '2005-05-05', date '1964-05-05'); > age > --- > 40 years 11 mons 30 days 23:00:00 > (1 row) Not for me --- I get "41 years" for that case. Since 7.4 depends on the OS' timezone code, this is presumably OS-dependent. I'm using Fedora Core 3, which has ... hmm ... $ rpm -qf /usr/share/zoneinfo/America/Buenos_Aires tzdata-2005f-1.fc3 ... a pretty recent zoneinfo package. What's yours? 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])
[BUGS] Wrong Query Plan
Below if the query plan that postgres is generating. The troubling part is the
sequential scan on fund_data table. This table has close to million records. It
started doing this from yesterday.
We have added lot of data in allocation_data & transfer_data tables.
If I have just sub query 1 or sub query 2 then it is doing a index scan on
fund_data table but as soon as I add the union it is doing a sequential scan.
EXPLAIN SELECT fund_data.fund_id FROM fund_data WHERE fund_data.fund_id IN
((SELECT allocation_data.fund_id FROM allocation_data, allocation_lists WHERE
allocation_lists.allocation_id = allocation_data.allocation_id AND
allocation_lists.account_id=23338) UNION (SELECT transfer_data.target_fund_id as
fund_id FROM transfer_data WHERE transfer_data.account_id=23338));
QUERY
PLAN
-
Hash Join (cost=97.73..157055.63 rows=138696 width=4)
Hash Cond: ("outer".fund_id = "inner".fund_id)
-> Seq Scan on fund_data (cost=0.00..123670.96 rows=6379996 width=4)
-> Hash (cost=97.49..97.49 rows=98 width=4)
-> HashAggregate (cost=97.49..97.49 rows=98 width=4)
-> Subquery Scan "IN_subquery" (cost=95.77..97.24 rows=98
width=4)
-> Unique (cost=95.77..96.26 rows=98 width=4)
-> Sort (cost=95.77..96.02 rows=98 width=4)
Sort Key: fund_id
-> Append (cost=0.00..92.53 rows=98 width=4)
-> Subquery Scan "*SELECT* 1"
(cost=0.00..45.69 rows=27 width=4)
-> Nested Loop (cost=0.00..45.42
rows=27 width=4)
-> Index Scan using
m_all_lists_account_id_idx on allocation_lists (cost=0.00..11.01 rows=7
width=4)
Index Cond: (account_id
= 23338)
-> Index Scan using
m_all_data_all_list_id_idx on allocation_data (cost=0.00..4.84 rows=6 width=8)
Index Cond:
("outer".allocation_id = allocation_data.allocation_id)
-> Subquery Scan "*SELECT* 2"
(cost=0.00..46.84 rows=71 width=4)
-> Index Scan using
m_trans_data_account_id_idx on transfer_data (cost=0.00..46.13 rows=71 width=4)
Index Cond: (account_id =
23338)
(19 rows)
Thanks,
-Prasanth.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[BUGS] Win1250 database under linux
Hi. I have ecountered strange behaviour with postgres 8 under linux so I am reporting this as a bug. I haven't compiled postgres myself because it is on hosted server but it is an options if it would solve this problem. The problem i'm having seems to be known but i don't know if there is a workaround. Functions upper() and lower() don't change value for Croatian national characters under linux (others probably as well, but i havent tested). Operator ILIKE doesn't work correctly either. I have tried to reporeduce bug under windows version of postgres by transfering database via backup/restore but the windows version is working correctly. I had to write some pretty ugly query logic as a temporary workaround for searching national characters... Damir __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[BUGS] BUG #1638: ODBC driver problem
The following bug has been logged online: Bug reference: 1638 Logged by: Vig Sandor Email address: [EMAIL PROTECTED] PostgreSQL version: ANY ( now: 8.0) Operating system: ANY (XP, W2k, Linux...) Description:ODBC driver problem Details: Hi, I wrote an application (in Cbuilder 5, using BDE) the was just working fine with the ODBC driver in "psqlodbc-07_03_0200.zip". With the latest ODBC drivers the program crashes after sending the first query. (connect is successfull) It raises a null pointer exception. After downgrading the ODBC driver, it's all OK. I tested it with different servers, clients, psql server versions, at home, at work, etc.. but it was always the same. Please help. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] BUG #1637: age() function is giving different results
On 2005-04-29, Tom Lane <[EMAIL PROTECTED]> wrote: > Andrew - Supernews <[EMAIL PROTECTED]> writes: >> I did some analysis for this one when it was mentioned just now in the irc >> chan. I can reproduce on 7.4.x as follows: > >> test=> set timezone to 'America/Buenos_Aires'; >> SET >> test=> select age(date '2005-05-05', date '1964-05-05'); >> age >> --- >> 40 years 11 mons 30 days 23:00:00 >> (1 row) > > Not for me --- I get "41 years" for that case. Since 7.4 depends on the > OS' timezone code, this is presumably OS-dependent. I'm using Fedora > Core 3, which has ... hmm ... > > $ rpm -qf /usr/share/zoneinfo/America/Buenos_Aires > tzdata-2005f-1.fc3 > > ... a pretty recent zoneinfo package. What's yours? I tested on a couple of FreeBSD 4.10 (approximately) builds, from May and September last year. The original poster was on Linux I believe. Looking at the definition of America/Buenos_Aires, which is exactly the same in my FreeBSD sources and in the zoneinfo source supplied in pg 8.0.1, it looks as though Argentina did indeed change timezones in 1969, so the actual elapsed time between '2005-05-05 00:00:00' and '1964-05-05 00:00:00' in that timezone is in fact 14974 days 23 hours. Looking more closely, the significant thing seems to be that neither of my 7.4 servers is on 7.4.7, and this seems to have been fixed there (in response to bug 1331). -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #1638: ODBC driver problem
I believe the ODBC package is managed by a different group. Bugs can be viewed and added here: http://gborg.postgresql.org/project/psqlodbc/bugs/buglist.php Mike On Fri, Apr 29, 2005 at 04:31:02PM +0100, Vig Sandor wrote: > > The following bug has been logged online: > > Bug reference: 1638 > Logged by: Vig Sandor > Email address: [EMAIL PROTECTED] > PostgreSQL version: ANY ( now: 8.0) > Operating system: ANY (XP, W2k, Linux...) > Description:ODBC driver problem > Details: > > Hi, > > I wrote an application (in Cbuilder 5, using BDE) > the was just working fine with the ODBC driver in > "psqlodbc-07_03_0200.zip". With the latest ODBC drivers > the program crashes after sending the first query. > (connect is successfull) It raises a null pointer > exception. After downgrading the ODBC driver, it's > all OK. I tested it with different servers, clients, > psql server versions, at home, at work, etc.. but > it was always the same. > > Please help. > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[BUGS] looks like apple fixed /etc/rc
I just updated to 10.3.9 and of course the /etc/rc was
dinged. I opened the /etc/rc and found a change..
# System tuning
sysctl -w kern.maxvnodes=$(echo $(sysctl -n
hw.physmem) '33554432 / 512 * 1024 +p'|dc)
if [ -f /etc/sysctl-macosxserver.conf ]; then
awk '{ if (!index($1, "#") && index($1, "="))
print $1 }' < /etc/sysctl-macosxserver.conf | while
read
do
sysctl -w ${REPLY}
done
fi
if [ -f /etc/sysctl.conf ]; then
awk '{ if (!index($1, "#") && index($1, "="))
print $1 }' < /etc/sysctl.conf | while read
do
sysctl -w ${REPLY}
done
fi
sysctl -w kern.sysv.shmmax=4194304
sysctl -w kern.sysv.shmmin=1
sysctl -w kern.sysv.shmmni=32
sysctl -w kern.sysv.shmseg=8
sysctl -w kern.sysv.shmall=1024
It appears that apple is checking for etc/sysctl.conf
before setting the sysctl values. so I created a
etc/sysctl.conf with this inside:
kern.sysv.shmmax=33554432
kern.sysv.shmmin=1
kern.sysv.shmmni=32
kern.sysv.shmseg=8
kern.sysv.shmall=65536
I did not alter /etc/rc. postgresql starts up just
fine. I hope this implys that the issue is resolved..
Ted
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] Win1250 database under linux
Damir Bijuklic <[EMAIL PROTECTED]> writes: > The problem i'm having seems to be known but i don't > know if there is a workaround. Functions upper() and > lower() don't change value for Croatian national > characters under linux (others probably as well, but i > havent tested). Operator ILIKE doesn't work correctly > either. Sounds like you are using the wrong locale and/or encoding settings. Since you have not said what you are using, it's hard to offer anything more specific than that. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #1637: age() function is giving different results
Andrew - Supernews <[EMAIL PROTECTED]> writes: > Looking more closely, the significant thing seems to be that neither of my > 7.4 servers is on 7.4.7, and this seems to have been fixed there (in response > to bug 1331). 1332 you mean. Yeah, I was testing on 7.4.7, so this is almost certainly a duplicate of 1332. 2004-12-01 14:57 tgl * src/backend/utils/adt/timestamp.c (REL7_4_STABLE): Fix timestamptz_age() to do calculation in local timezone not GMT, per bug 1332. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] looks like apple fixed /etc/rc
That's interesting. On my powerbook, I had to comment out the sysctl -w
kern.sysv.shmmax line in /etc/rc. It appears that you can shrink shmmax
but not expand it. This meant that it would properly set it based on
/etc/syscttl.conf, but that (large) setting would then be over-written
by the subsiquent sysctl -w.
On Fri, Apr 29, 2005 at 09:39:55AM -0700, Theodore Petrosky wrote:
> I just updated to 10.3.9 and of course the /etc/rc was
> dinged. I opened the /etc/rc and found a change..
>
> # System tuning
> sysctl -w kern.maxvnodes=$(echo $(sysctl -n
> hw.physmem) '33554432 / 512 * 1024 +p'|dc)
> if [ -f /etc/sysctl-macosxserver.conf ]; then
> awk '{ if (!index($1, "#") && index($1, "="))
> print $1 }' < /etc/sysctl-macosxserver.conf | while
> read
> do
> sysctl -w ${REPLY}
> done
> fi
>
> if [ -f /etc/sysctl.conf ]; then
> awk '{ if (!index($1, "#") && index($1, "="))
> print $1 }' < /etc/sysctl.conf | while read
> do
> sysctl -w ${REPLY}
> done
> fi
>
> sysctl -w kern.sysv.shmmax=4194304
> sysctl -w kern.sysv.shmmin=1
> sysctl -w kern.sysv.shmmni=32
> sysctl -w kern.sysv.shmseg=8
> sysctl -w kern.sysv.shmall=1024
>
> It appears that apple is checking for etc/sysctl.conf
> before setting the sysctl values. so I created a
> etc/sysctl.conf with this inside:
>
> kern.sysv.shmmax=33554432
> kern.sysv.shmmin=1
> kern.sysv.shmmni=32
> kern.sysv.shmseg=8
> kern.sysv.shmall=65536
>
> I did not alter /etc/rc. postgresql starts up just
> fine. I hope this implys that the issue is resolved..
>
> Ted
>
> __
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
>
--
Jim C. Nasby, Database Consultant [EMAIL PROTECTED]
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"
---(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
[BUGS] BUG #1639: Problema re-instalacion
The following bug has been logged online: Bug reference: 1639 Logged by: PABLO Email address: [EMAIL PROTECTED] PostgreSQL version: 8.02 Operating system: windows 2000 Description:Problema re-instalacion Details: Hola : Tuve que reinstalar posgresql 8.02. Primero lo desintale, luego, elimine las carpetas que quedaban, reinicie la maquina. Puse a instalar de nuevo postgresql 8.02 y en la parte del usuario siempre me pone que existe, cualquiera que le pongo. ¿Como puedo solucionar eso?, ¿queda registrado en algun lado el usuario que tuve?. Por favor, necesito la respuesta rapido. muchas gracias ---(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
[BUGS] BUG #1640: Using JDBC, multiple statements do not return results if select follows insert,delete or update
The following bug has been logged online:
Bug reference: 1640
Logged by: Kevin Self
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.0.2
Operating system: Windows XP
Description:Using JDBC, multiple statements do not return results if
select follows insert,delete or update
Details:
When attempting to perform a multi-statement command through the JDBC
Statement.execute() or the Statement.executeQuery() function, the database
does not return any result set if the select statement follows an insert,
update or delete. This ability is required for atomic record inserts for
auto-generated keys that must be returned to the caller, since postgreSQL
does not support RETURN_GENERATED_KEYS as an option, and calling the
sequence ahead of time to obtain the value is not possible.
Pre-conditions:
Assuming a database called 'test'
Assuming a user called 'test' with a password 'test'.
create a table as follows:
create table test (a serial primary key,b int);
Example:
public static void main(String[] args) throws Exception
{
try
{
Class.forName("org.postgresql.Driver");
Connection lc =
DriverManager.getConnection("jdbc:postgresql://localhost/test","test","test"
);
PreparedStatement stmt = lc.prepareStatement("insert into test(b)
values(100);select currval('test_a_seq');");
if(!stmt.execute()){System.out.println("NO RESULTS!");}
else{System.out.println("RESULTS!");}
lc.close();
}
catch(Exception e)
{
System.out.println(e.toString());
}
}
Expectd Results:
RESULTS!
Actual results:
NO RESULTS!
Notes:
This used to work in the previous version of PostgreSQL (7.x).
---(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: [BUGS] BUG #1639: Problema re-instalacion
On Fri, Apr 29, 2005 at 07:47:18PM +0100, PABLO wrote: > Bug reference: 1639 > Logged by: PABLO > Email address: [EMAIL PROTECTED] > PostgreSQL version: 8.02 > Operating system: windows 2000 > Description:Problema re-instalacion > Details: I've replied on private redirecting him to pgsql-es-ayuda. No need to hire the translator. -- Alvaro Herrera (<[EMAIL PROTECTED]>) "In a specialized industrial society, it would be a disaster to have kids running around loose." (Paul Graham) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] looks like apple fixed /etc/rc
Are they still running on the default PostgreSQL port number for their
remote administration tool?
---
Jim C. Nasby wrote:
> That's interesting. On my powerbook, I had to comment out the sysctl -w
> kern.sysv.shmmax line in /etc/rc. It appears that you can shrink shmmax
> but not expand it. This meant that it would properly set it based on
> /etc/syscttl.conf, but that (large) setting would then be over-written
> by the subsiquent sysctl -w.
>
> On Fri, Apr 29, 2005 at 09:39:55AM -0700, Theodore Petrosky wrote:
> > I just updated to 10.3.9 and of course the /etc/rc was
> > dinged. I opened the /etc/rc and found a change..
> >
> > # System tuning
> > sysctl -w kern.maxvnodes=$(echo $(sysctl -n
> > hw.physmem) '33554432 / 512 * 1024 +p'|dc)
> > if [ -f /etc/sysctl-macosxserver.conf ]; then
> > awk '{ if (!index($1, "#") && index($1, "="))
> > print $1 }' < /etc/sysctl-macosxserver.conf | while
> > read
> > do
> > sysctl -w ${REPLY}
> > done
> > fi
> >
> > if [ -f /etc/sysctl.conf ]; then
> > awk '{ if (!index($1, "#") && index($1, "="))
> > print $1 }' < /etc/sysctl.conf | while read
> > do
> > sysctl -w ${REPLY}
> > done
> > fi
> >
> > sysctl -w kern.sysv.shmmax=4194304
> > sysctl -w kern.sysv.shmmin=1
> > sysctl -w kern.sysv.shmmni=32
> > sysctl -w kern.sysv.shmseg=8
> > sysctl -w kern.sysv.shmall=1024
> >
> > It appears that apple is checking for etc/sysctl.conf
> > before setting the sysctl values. so I created a
> > etc/sysctl.conf with this inside:
> >
> > kern.sysv.shmmax=33554432
> > kern.sysv.shmmin=1
> > kern.sysv.shmmni=32
> > kern.sysv.shmseg=8
> > kern.sysv.shmall=65536
> >
> > I did not alter /etc/rc. postgresql starts up just
> > fine. I hope this implys that the issue is resolved..
> >
> > Ted
> >
> > __
> > Do You Yahoo!?
> > Tired of spam? Yahoo! Mail has the best spam protection around
> > http://mail.yahoo.com
> >
> > ---(end of broadcast)---
> > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
> >
>
> --
> Jim C. Nasby, Database Consultant [EMAIL PROTECTED]
> Give your computer some brain candy! www.distributed.net Team #1828
>
> Windows: "Where do you want to go today?"
> Linux: "Where do you want to go tomorrow?"
> FreeBSD: "Are you guys coming, or what?"
>
> ---(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
>
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(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: [BUGS] BUG #1640: Using JDBC, multiple statements do not return
On Fri, 29 Apr 2005, Kevin Self wrote:
> Bug reference: 1640
> PostgreSQL version: 8.0.2
> Description:Using JDBC, multiple statements do not return results if
> select follows insert,delete or update
>
> When attempting to perform a multi-statement command through the JDBC
> Statement.execute() or the Statement.executeQuery() function, the database
> does not return any result set if the select statement follows an insert,
> update or delete. This ability is required for atomic record inserts for
> auto-generated keys that must be returned to the caller, since postgreSQL
> does not support RETURN_GENERATED_KEYS as an option, and calling the
> sequence ahead of time to obtain the value is not possible.
There is no "atomicity" from issuing them in the same query. This is
guaranteed by the sequence for the entire session.
If you do what to do this it should be written as:
Statement.execute("INSERT ...; SELECT currval('myseq')");
Statement.getMoreResults();
ResultSet rs = Statement.getResultSet();
The javadoc for execute() says it should return "true if the first result
is a ResultSet object" the first result is an update count, not a
ResultSet. You need to move to the next result, which is actually a
ResultSet.
Kris Jurka
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [BUGS] looks like apple fixed /etc/rc
Is that tool on OSX Server? I don't have a copy, though I could probably find out from a friend. On Fri, Apr 29, 2005 at 11:57:54PM -0400, Bruce Momjian wrote: > > Are they still running on the default PostgreSQL port number for their > remote administration tool? -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
