[BUGS] BUG #5330: No CREATE SYNONYM command
The following bug has been logged online: Bug reference: 5330 Logged by: zhuge Email address: zhuge_x...@yahoo.com PostgreSQL version: 8.4.2 Operating system: Linux Description:No CREATE SYNONYM command Details: I want to port from Oracle to PostgreS. But Postgres has no the command, CREATE SYNONYM, which exists in Oracle. I have read the information, http://archives.postgresql.org/pgsql-patches/2006-03/msg00085.php, and download the file. However, I DO NOT know how to use the create synonym command in Postgres. How can I to use that command? Please help me ! Thank you so much. zhuge -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5329: postmaster fails to start because of an erroneous pg_hba.conf, but doesn't output any error
On Wed, Feb 17, 2010 at 2:15 AM, Tom Lane wrote: > We most certainly do print something --- in your example I get this: > > LOG: invalid authentication method "127.0.0.1" > CONTEXT: line 81 of configuration file "/home/postgres/data/pg_hba.conf" > FATAL: could not load pg_hba.conf > > I suspect you are using a configuration that redirects the postmaster's > log to /dev/null, or some equally unhelpful place :-(. > > FWIW, this message comes out after the logging settings in > postgresql.conf have been adopted, so you should look to those to see > where the message actually went. Errors in postgresql.conf itself > typically get reported on postmaster's stderr. You are right, I wasn't looking at the right place (I was a user of Postgres a very long time ago). Sorry for this unnecessary bug report, and thanks to you and to Euler who answered as well. Best regards, Olivier Jeannet. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5331: Integer overflow in tmie counter
The following bug has been logged online: Bug reference: 5331 Logged by: Kajetan Abt Email address: kdan...@gmx.ch PostgreSQL version: 8.3 Operating system: Windows Server 2008 Description:Integer overflow in tmie counter Details: Amusingly, the counter on the lower right of the SQL-input window sometimes shows negative numbers counting up, probably some sort over overflow. No harm done though. Cheers -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5332: Installation weirdness
The following bug has been logged online: Bug reference: 5332 Logged by: Sivaguru Sankaridurg Email address: uc_rege...@yahoo.com PostgreSQL version: 8.4.1 Operating system: FreeBSD 6.3 Description:Installation weirdness Details: I am seeing some install script weirdness when installing postgres (build and install from sources) on a FreeBSD 6.3 machine. Pls. see install output between "Try#1" and "Try#2" below. In Try#1, when I configure the prefix to be "/tmp/pgsql", and subsequently do a "gmake install", the install script creates the "internal", "server" and "informix" directories right under "include". However, in Try#2, when I configure the prefix to be "/tmp/psql" (NOT pgsql, but psql...or anything else) and subsequently do a "gmake install", the install script creates the "internal", "server" and "informix" directories under "include/postgres". The bug is that the "postgres" directory was NOT created under "include" in Try#1, however the behavior was different in Try#2. Try #1 - # ./configure --prefix=/tmp/pgsql --with-openssl --with-libxml --enable-thread-safety # gmake install # find /tmp/pgsql/include/ -type d -maxdepth 1 /tmp/pgsql/include/ /tmp/pgsql/include/libpq /tmp/pgsql/include/internal /tmp/pgsql/include/server /tmp/pgsql/include/informix Try #2 -- # ./configure --prefix=/tmp/psql --with-openssl --with-libxml --enable-thread-safety # gmake install # find /tmp/psql/include/ -type d -maxdepth 1 /tmp/psql/include/ /tmp/psql/include/libpq /tmp/psql/include/postgresql # # find /tmp/psql/include/ -type d -maxdepth 2 /tmp/psql/include/ /tmp/psql/include/libpq /tmp/psql/include/postgresql /tmp/psql/include/postgresql/internal /tmp/psql/include/postgresql/server /tmp/psql/include/postgresql/informix --- -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5330: No CREATE SYNONYM command
On Wed, Feb 17, 2010 at 05:41:45AM +, zhuge wrote: > I want to port from Oracle to PostgreS. But Postgres has no the command, > CREATE SYNONYM, > which exists in Oracle. I have read the information, > http://archives.postgresql.org/pgsql-patches/2006-03/msg00085.php, and > download the file. However, I DO NOT know how to use the create synonym > command in Postgres. How can I to use that command? Please help me ! PostgreSQL doesn't support Oracle's concept of synonyms. The message you read is from a patch that eventually was rejected. How you might replicate Oracle's synonyms in PostgreSQL depends on your specific use case. You might consider emailing pgsql-general with details about what precisely you use synonyms for, to get help on your porting project. -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature
Re: [BUGS] BUG #5331: Integer overflow in tmie counter
Kajetan Abt escreveu: > Amusingly, the counter on the lower right of the SQL-input window sometimes > shows negative numbers counting up, probably some sort over overflow. No > harm done though. > If you don't provide more details (test case) it's hard to say if it's a bug or not. -- Euler Taveira de Oliveira http://www.timbira.com/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5332: Installation weirdness
"Sivaguru Sankaridurg" writes: > In Try#1, when I configure the prefix to be "/tmp/pgsql", and subsequently > do a "gmake install", the install script creates the "internal", "server" > and "informix" directories right under "include". > However, in Try#2, when I configure the prefix to be "/tmp/psql" (NOT pgsql, > but psql...or anything else) and subsequently do a "gmake install", the > install script creates the "internal", "server" and "informix" directories > under "include/postgres". This is expected. As per the comment in src/Makefile.global: # Installation directories # # These are set by the equivalent --xxxdir configure options. We # append "postgresql" to some of them, if the string does not already # contain "pgsql" or "postgres", in order to avoid directory clutter. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5332: Installation weirdness
Hi Tom, Ah! I see. Thank you for your quick response. Best Regards Siva From: Tom Lane To: Sivaguru Sankaridurg Cc: pgsql-bugs@postgresql.org Sent: Wed, February 17, 2010 9:12:36 PM Subject: Re: [BUGS] BUG #5332: Installation weirdness "Sivaguru Sankaridurg" writes: > In Try#1, when I configure the prefix to be "/tmp/pgsql", and subsequently > do a "gmake install", the install script creates the "internal", "server" > and "informix" directories right under "include". > However, in Try#2, when I configure the prefix to be "/tmp/psql" (NOT pgsql, > but psql...or anything else) and subsequently do a "gmake install", the > install script creates the "internal", "server" and "informix" directories > under "include/postgres". This is expected. As per the comment in src/Makefile.global: # Installation directories # # These are set by the equivalent --xxxdir configure options. We # append "postgresql" to some of them, if the string does not already # contain "pgsql" or "postgres", in order to avoid directory clutter. regards, tom lane
Re: [BUGS] BUG #5331: Integer overflow in tmie counter
On Wed, Feb 17, 2010 at 5:59 AM, Euler Taveira de Oliveira wrote: > Kajetan Abt escreveu: >> Amusingly, the counter on the lower right of the SQL-input window sometimes >> shows negative numbers counting up, probably some sort over overflow. No >> harm done though. >> > If you don't provide more details (test case) it's hard to say if it's a bug > or not. Is that a pg-Admin bug report? If so, consider sending it to the email lists of that project. Best Wishes, Chris Travers -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] planner regression in 8.4 (from 8.1)
I have a query which performs much better on 8.1.19 than on 8.4.2, unless I add
"offset 0" to the subqueries. I believe this is due to miscalculating the
expected row count of nest loop joins. I cannot give you my data, but I can
give you the query and the plans. Let me know if anything else would be helpful.
The query as I used it on 8.1:
SELECT SUM(machines.quota_purchased), license_type_id FROM sm_pro_keys,
machines
WHERE NOT deleted AND sm_pro_keys.machine_id = machines.id AND
machines.quota_purchased > 0 AND machines.user_id IN
( SELECT id FROM users WHERE NOT deleted AND user_group_id IN
( SELECT id FROM user_groups WHERE pro_partner_id IN
( SELECT id FROM pro_partners WHERE tree_sortkey BETWEEN
'00011000010011011010' AND
tree_right('00011000010011011010') )
) ) GROUP BY license_type_id;
The modified query (the difference is the last line):
SELECT SUM(machines.quota_purchased), license_type_id FROM sm_pro_keys,
machines
WHERE NOT deleted AND sm_pro_keys.machine_id = machines.id AND
machines.quota_purchased > 0 AND machines.user_id IN
( SELECT id FROM users WHERE NOT deleted AND user_group_id IN
( SELECT id FROM user_groups WHERE pro_partner_id IN
( SELECT id FROM pro_partners WHERE tree_sortkey BETWEEN
'00011000010011011010' AND
tree_right('00011000010011011010') )
) offset 0) GROUP BY license_type_id;
On 8.1, the plan looks like this:
HashAggregate (cost=23040.78..23040.79 rows=1 width=12) (actual
time=36.642..36.643 rows=1 loops=1)
-> Nested Loop (cost=20911.79..23040.77 rows=1 width=12) (actual
time=36.615..36.629 rows=1 loops=1)
-> Nested Loop (cost=20911.79..23036.81 rows=1 width=12) (actual
time=36.272..36.285 rows=1 loops=1)
-> HashAggregate (cost=20911.79..20911.91 rows=12 width=4)
(actual time=36.227..36.227 rows=1 loops=1)
-> Nested Loop (cost=10.44..20911.76 rows=12 width=4)
(actual time=36.221..36.222 rows=1 loops=1)
-> HashAggregate (cost=10.44..10.45 rows=1
width=4) (actual time=36.073..36.073 rows=1 loops=1)
-> Nested Loop (cost=4.21..10.44 rows=1
width=4) (actual time=36.058..36.065 rows=1 loops=1)
-> HashAggregate (cost=4.21..4.22
rows=1 width=4) (actual time=0.141..0.142 rows=1 loops=1)
-> Index Scan using
pro_partners_tree_sortkey_idx on pro_partners (cost=0.00..4.20 rows=1 width=4)
(actual time=0.132..0.133 rows=1 loops=1)
Index Cond: ((tree_sortkey
>= B'00011000010011011010'::bit varying) AND
(tree_sortkey <=
B'00011000010011011010'::bit
varying))
-> Index Scan using
user_groups_pro_partner_id_idx on user_groups (cost=0.00..6.19 rows=3 width=8)
(actual time=35.913..35.916 rows=1 loops=1)
Index Cond:
(user_groups.pro_partner_id = "outer".id)
-> Index Scan using users_user_groups_idx on users
(cost=0.00..20800.07 rows=8099 width=8) (actual time=0.142..0.143 rows=1
loops=1)
Index Cond: (users.user_group_id = "outer".id)
Filter: (NOT deleted)
-> Index Scan using machines_sid_un on machines
(cost=0.00..177.01 rows=5 width=16) (actual time=0.042..0.055 rows=1 loops=1)
Index Cond: (machines.user_id = "outer".id)
Filter: (quota_purchased > 0)
-> Index Scan using sm_pro_keys_machine_id_idx on sm_pro_keys
(cost=0.00..3.95 rows=1 width=8) (actual time=0.339..0.340 rows=1 loops=1)
Index Cond: (sm_pro_keys.machine_id = "outer".id)
Total runtime: 36.794 ms
(21 rows)
On 8.4, the unmodified query looks like this:
HashAggregate (cost=193503.37..193503.82 rows=36 width=12) (actual
time=3389.899..3389.900 rows=1 loops=1)
-> Hash Semi Join (cost=178202.84..193475.12 rows=5649 width=12) (actual
time=1612.034..3389.861 rows=1 loops=1)
Hash Cond: (machines.user_id = users.id)
-> Merge Join (cost=142309.11..148916.54 rows=5649 width=16) (actual
time=1466.097..3317.794 rows=168536 loops=1)
Merge Cond: (sm_pro_keys.machine_id = machines.id)
-> Index Scan using sm_pro_keys_machine_id_idx on sm_pro_keys
(cost=0.00..97619.86 rows=3086901 width=8) (actual time=0.059..1359.843
rows=3004929 loops=1)
-> Sort (cost=142290.93..142535.59 rows=97866 width=16)
(actual time=1466.024..1502.852 rows=168970 loops=1)
Sort Key: machines.id
Sort Method: quicksort Memory: 14062kB
-> Seq Scan on machines (cost=0.00.
Re: [BUGS] BUG #5327: postgresql gives checksum error when upgrading 8.2.6 binaries to 8.2.14 in windows.
I've got it working after building with Visual Studio. Thanks. But there is still a problem when building some .dll files. The version I'm using is 2008. When building libecpg there are errors like, Error 1 error LNK2019: unresolved external symbol _PGTYPESdate_from_asc referenced in function _ECPGget_data data.obj libecpg I see PGTYPESdate_from_asc in src/interface/ecpg/pgtypeslib/timestamp.c why isn't this picked up although I add it to source file list. There are simillar errors in, conversion procs collection of projects. Can you please help me figure this out. On Tue, Feb 16, 2010 at 10:29 PM, Bruce Momjian wrote: > > The short answer is that you should not be changing any build > configurations if changing from one minor release to another. Are you > sure you are using the same build setup? > > --- > > janandith wrote: > > > > The following bug has been logged online: > > > > Bug reference: 5327 > > Logged by: janandith > > Email address: janand...@gmail.com > > PostgreSQL version: 8.2.14 > > Operating system: windows 2003 > > Description:postgresql gives checksum error when upgrading 8.2.6 > > binaries to 8.2.14 in windows. > > Details: > > > > After upgrading postgresql binaries from 8.2.6 to 8.2.14 in windows 2003 > I > > cannot start the database using postgresql.exe because there is a > checksum > > error. > > > > What I'm doing is building postgresql 8.2.14 from source code and > replacing > > the binaries. > > > > In windows I'm building using MSYS and replacing existing .exe files. > > > > After the replacement Linux binaries are working without errors. But the > > windows binaries reads the Date/time type storage: and Locale wrong. It > > should be be floating-point and C locale but read as 64-bit integers and > > Locale as @. > > > > > > Following are the results of pg_controldata. > > > > > > Linux > > > > pg_control version number:822 > > Catalog version number: 200611241 > > Database system identifier: 5219167662867643742 > > Database cluster state: in production > > pg_control last modified: Fri 12 Feb 2010 03:47:00 PM IST > > Current log file ID: 0 > > Next log file segment:63 > > Latest checkpoint location: 0/3EA20688 > > Prior checkpoint location:0/3E9F1084 > > Latest checkpoint's REDO location:0/3EA20688 > > Latest checkpoint's UNDO location:0/0 > > Latest checkpoint's TimeLineID: 1 > > Latest checkpoint's NextXID: 0/547350 > > Latest checkpoint's NextOID: 26232 > > Latest checkpoint's NextMultiXactId: 1 > > Latest checkpoint's NextMultiOffset: 0 > > Time of latest checkpoint:Fri 12 Feb 2010 03:47:00 PM IST > > Minimum recovery ending location: 0/0 > > Maximum data alignment: 4 > > Database block size: 8192 > > Blocks per segment of large relation: 131072 > > WAL block size: 8192 > > Bytes per WAL segment:16777216 > > Maximum length of identifiers:64 > > Maximum columns in an index: 32 > > Date/time type storage: floating-point numbers > > Maximum length of locale name:128 > > LC_COLLATE: C > > LC_CTYPE: C > > > > > > Windows > > > > WARNING: Calculated CRC checksum does not match value stored in file. > > Either the file is corrupt, or it has a different layout than this > program > > is expecting. The results below are untrustworthy. > > > > pg_control version number:822 > > Catalog version number: 200611241 > > Database system identifier: 5247545044375645930 > > Database cluster state: shut down > > pg_control last modified: 01.01.1970 01:00:00 > > Current log file ID: 1265884073 > > Next log file segment:0 > > Latest checkpoint location: 0/30 > > Prior checkpoint location:0/2F7CA1A8 > > Latest checkpoint's REDO location:0/2F6F4E60 > > Latest checkpoint's UNDO location:0/2F7CA1A8 > > Latest checkpoint's TimeLineID: 0 > > Latest checkpoint's NextXID: 0/1 > > Latest checkpoint's NextOID: 0 > > Latest checkpoint's NextMultiXactId: 173924 > > Latest checkpoint's NextMultiOffset: 17725 > > Time of latest checkpoint:01.01.1970 01:00:01 > > Minimum recovery ending location: 0/4B73DBA9 > > Maximum data alignment: 0 > > Database block size: 8 > > Blocks per segment of large relation: 0 > > WAL block size: 0 > > Bytes per WAL segment:1093850759 > > Maximum length of identifiers:8192 > > Maximum columns in an index: 131072 > > Date/time type storage: 64-bit integers > > Maximum length of loc
