Re: [GENERAL] Enumerating a row set
Hello, There is also a funny approach here with custom operators and variables that could be adapted to build a rownum functioanlity in functions (look for Vladimir) http://archives.postgresql.org/pgsql-hackers/2008-11/msg00048.php not very clean, but apparently fast Marc Mamin -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Guy Rouillier Sent: Friday, March 27, 2009 2:54 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Enumerating a row set On Thu, Mar 26, 2009 at 06:42:45PM -0400, George Sakkis wrote: Hi all, Is there a function similiar to Python's enumerate() [1] ? Searching the docs didn't reveal any relevant builtin but I hope it's doable in pgsql. I found this via Google: http://www.depesz.com/index.php/2007/08/17/rownum-anyone-cumulative-sum- in-one-query/ -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PITRTools: Bring up warm standby - unexpected pageaddr
Hi, I'm trying to setup a warm standby using PITRTools. Environment is CentOS 5.2 x86_64 and PG 8.3.6 from yum.pgsqlrpms.org. As far as I can tell everything seems to work as expected. After a successful base backup and issuing cmd_standby -S the standby goes into constant recovery mode and happily consumes WAL segments shipped form the master. The corresponding log looks like this: 2009-03-26 17:55:04 CET::@:[2901]: LOG: database system was interrupted; last known up at 2009-03-26 17:09:49 CET 2009-03-26 17:55:04 CET::@:[2901]: LOG: starting archive recovery 2009-03-26 17:55:04 CET::@:[2901]: LOG: restore_command = '/usr/bin/pg_standby /data/pgsql_archive -s5 -w0 -c %f %p %r' cp: cannot stat `/data/pgsql_archive/0001.history': No such file or directory cp: cannot stat `/data/pgsql_archive/0001.history': No such file or directory cp: cannot stat `/data/pgsql_archive/0001.history': No such file or directory 2009-03-26 17:55:20 CET::@:[2901]: LOG: restored log file 0001004E00DF from archive 2009-03-26 17:55:20 CET::@:[2901]: LOG: automatic recovery in progress 2009-03-26 17:55:20 CET::@:[2901]: LOG: redo starts at 4E/DF0001B8 2009-03-26 17:55:20 CET::@:[2901]: LOG: restored log file 0001004E00E0 from archive 2009-03-26 17:55:20 CET::@:[2901]: LOG: restored log file 0001004E00E1 from archive 2009-03-26 17:55:21 CET::@:[2901]: LOG: restored log file 0001004E00E2 from archive 2009-03-26 17:55:21 CET::@:[2901]: LOG: restored log file 0001004E00E3 from archive 2009-03-26 17:55:21 CET::@:[2901]: LOG: restored log file 0001004E00E4 from archive 2009-03-26 17:55:22 CET::@:[2901]: LOG: restored log file 0001004E00E5 from archive 2009-03-26 17:55:22 CET::@:[2901]: LOG: restored log file 0001004E00E6 from archive 2009-03-26 17:55:22 CET::@:[2901]: LOG: restored log file 0001004E00E7 from archive 2009-03-26 17:55:22 CET::@:[2901]: LOG: restored log file 0001004E00E8 from archive 2009-03-26 17:55:22 CET::@:[2901]: LOG: restored log file 0001004E00E9 from archive 2009-03-26 17:55:22 CET::@:[2901]: LOG: restored log file 0001004E00EA from archive 2009-03-26 17:56:37 CET::@:[2901]: LOG: restored log file 0001004E00EB from archive As soon as I issue cmd_standby -F999 I get the following log entry: 2009-03-26 17:59:37 CET::@:[2874]: LOG: received fast shutdown request 2009-03-26 17:59:37 CET::@:[2901]: FATAL: could not restore file 0001004E00EC from archive: return code 15 2009-03-26 17:59:37 CET::@:[2874]: LOG: startup process (PID 2901) exited with exit code 1 2009-03-26 17:59:37 CET::@:[2874]: LOG: aborting startup due to startup process failure What I'm worried about now is that it reapplies all WAL segments it has already consumed in warm standby mode again and always ends with unexpected pageaddr. 2009-03-26 17:59:39 CET::@:[5721]: LOG: database system was interrupted while in recovery at log time 2009-03-26 17:09:49 CET 2009-03-26 17:59:39 CET::@:[5721]: HINT: If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target. 2009-03-26 17:59:39 CET::@:[5721]: LOG: starting archive recovery 2009-03-26 17:59:39 CET::@:[5721]: LOG: restore_command = 'cp /data/pgsql_archive/%f %p' cp: cannot stat `/data/pgsql_archive/0001.history': No such file or directory 2009-03-26 17:59:39 CET::@:[5721]: LOG: restored log file 0001004E00DF from archive 2009-03-26 17:59:39 CET::@:[5721]: LOG: automatic recovery in progress 2009-03-26 17:59:39 CET::@:[5721]: LOG: redo starts at 4E/DF0001B8 2009-03-26 17:59:39 CET::@:[5721]: LOG: restored log file 0001004E00E0 from archive 2009-03-26 17:59:39 CET::@:[5721]: LOG: restored log file 0001004E00E1 from archive 2009-03-26 17:59:39 CET::@:[5721]: LOG: restored log file 0001004E00E2 from archive 2009-03-26 17:59:39 CET::@:[5721]: LOG: restored log file 0001004E00E3 from archive 2009-03-26 17:59:40 CET::@:[5721]: LOG: restored log file 0001004E00E4 from archive 2009-03-26 17:59:40 CET::@:[5721]: LOG: restored log file 0001004E00E5 from archive 2009-03-26 17:59:40 CET::@:[5721]: LOG: restored log file 0001004E00E6 from archive 2009-03-26 17:59:40 CET::@:[5721]: LOG: restored log file 0001004E00E7 from archive 2009-03-26 17:59:40 CET::@:[5721]: LOG: restored log file 0001004E00E8 from archive 2009-03-26 17:59:40 CET::@:[5721]: LOG: restored log file 0001004E00E9 from archive 2009-03-26 17:59:40 CET::@:[5721]: LOG: restored log file 0001004E00EA from archive 2009-03-26 17:59:40 CET::@:[5721]: LOG: restored log file 0001004E00EB from archive cp: cannot stat `/data/pgsql_archive/0001004E00EC': No such file or directory 2009-03-26 17:59:40
Re: [GENERAL] Is there a meaningful benchmark?
On Thu, Mar 26, 2009 at 5:10 PM, Scott Marlowe scott.marl...@gmail.com wrote: It's also important to point out that writers don't necessarily block other writers. As long as they're operating on different ranges of the data set. You can have dozens of writers streaming data in with differening primary keys all running together. To be fare, some database apps have a few rows they update in a near continuous stream, and they row lock. These databases are often better served by db2 or some other row locking database than pgsql where you may or may not have problems with bloating. There are times picking a database some will turn left when they should have turned right. Picking pgsql for this kind of app is usually that kind of situation. Innodb would have bloat problems I'd assume too. MyIsam's table locking puts it out, and you're left with one of the other databases. I wonder how firebird handles that situation. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Enumerating a row set
On Thu, Mar 26, 2009 at 8:55 PM, David Fetter da...@fetter.org wrote: In PostgreSQL 8.4, you'll be able to do: SELECT row_number() OVER (ORDER BY col1) AS i, e.col1, e.col2, ... FROM ... Good news! Better late than never :) Until then, there are some ugly, fragile workarounds with generate_series() and/or temp sequences. That's exactly my impression based on the other replies and searching for postgresql rownum online. Thanks, George -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] log shipping from 64 bit linux server to a 32 bit linux server
On Fri, Mar 27, 2009 at 10:32:33AM +1300, Tim Uckun wrote: What about running a 32bit build of PG on the 64bit machine? How would one go about doing something like this? Depending on your distribution you should be able to install 32bit binaries alongside 64bit binaries; for example in Debian you can do: http://www.unixtutorial.org/2008/03/install-32-bit-deb-packages-on-64-bit/ Alternatives would be to build the code yourself as Scott said, or to copy the binaries across from your 32bit system. Assuming you did the latter you'd need to make sure you got all the appropriate libraries as well which may be a bit of a fiddle---especially to keep up to date. Then again, if you're building it yourself you'd need all the libraries anyway so you may as well figure out how to get 32bit packages installed in your 64bit system anyway. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgresql On Windows
What is the path format for the i (\i) command in the SQL shell? Thank you. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgresql On Windows
Hi, What is the path format for the i (\i) command in the SQL shell? I think it's just as anywhere else on Windows, e.g. \i c:\path\to\file.sql Windows support forward slashes as well, only not all Windows programs do support that (cmd.exe for example doesn't). So, it's worth a try. -- Aurimas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgresql On Windows
From my experience, you must use the forward slash. Using the backslash may give you an error: \i C:\sql\test.sql C:: Permission denied Instead, use \i C:/sql/test.sql From: Raymond O'Donnell r...@iol.ie To: MDB mdb...@yahoo.com Cc: pgsql-general@postgresql.org Sent: Friday, March 27, 2009 7:35:47 AM Subject: Re: [GENERAL] Postgresql On Windows On 27/03/2009 14:28, MDB wrote: What is the path format for the i (\i) command in the SQL shell? I think it's just as anywhere else on Windows, e.g. \i c:\path\to\file.sql If there are spaces in the path you may have to enclose it in double-quotes: \i c:\path with spaces\file.sql Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is there a meaningful benchmark?
I've had lots of experience fighting with the LOAD FROM command with an old application using an old version of Informix. When the data to be loaded is large enough, Informix (dbaccess) hits a wall. Tracking it down with tools generally shows that zillions of locks are accumulating. The system thrashes. So far I haven't found this problem with Postgres. Incidentally, in case people are interested in hearing the follow-up to my original message, my home-cooked benchmark with lots of processes doing tons of updates seemed to initially show MyISAM winning over Postgres. However I spent some time reading what I could from the manual on performance improvement and realized that commits might be an issue. I moved a commit() call out of the main loop, and bang! Postgres showed a substantial performance advantage over MyISAM. -Will -Original Message- From: Scott Marlowe [mailto:scott.marl...@gmail.com] Sent: 27 March 2009 02:25 To: Scott Ribe Cc: Will Rutherdale (rutherw); pgsql-general@postgresql.org Subject: Re: [GENERAL] Is there a meaningful benchmark? To be fare, some database apps have a few rows they update in a near continuous stream, and they row lock. These databases are often better served by db2 or some other row locking database than pgsql where you may or may not have problems with bloating. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgresql On Windows
On Fri, Mar 27, 2009 at 04:45:26PM +0200, Aurimas Černius wrote: Windows support forward slashes as well, only not all Windows programs do support that (cmd.exe for example doesn't). So, it's worth a try. Where did you get the idea that cmd.exe doesn't support forward slashes? It just passes them on to Windows like every other program and hence (pretty much) just works. The only time I've found it not to work is when you type cd / and it doesn't go to the root of the drive you're in which is a little strange. If programs attempt to interpret the path and attribute special meaning to backslashes then they will indeed break, but very few programs seem to do this. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Multidatabase query
Hi all, is possible in PostgreSQL to create query between multidatabase like Sql Server 2005? An example: SELECT db1.a.id FROM db1.a UNION db2.b.id FROM db2.b Where db1 is a database and db2 is another database. a is a table in database db1 and b is a table in database db2 Best regards, Mauro -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Partitioned tabled not using indexes for full text search
I have a table that is partitioned on a daily basis.
Full text searches used to be respectably fast with large tables (40
million + records) but insert speed would slow down. So I went with a
partitioned approach. But now, it doesn't seem like the indexes are
being used.
Any idea why it would not be using the indexes?
Here are appropriate descriptions and Explains:
syslog=# \d systemevents;
Table public.systemevents
Column |Type | Modifiers
+-+---
message| character varying |
facility | integer |
fromhost | character varying(80) |
priority | integer |
devicereportedtime | timestamp without time zone |
receivedat | timestamp without time zone |
infounitid | integer |
syslogtag | character varying(80) |
message_index_col | tsvector|
Rules:
systemevents_insert_032509 AS
ON INSERT TO systemevents
WHERE new.devicereportedtime '2009-03-24 23:59:59'::timestamp
without time zone AND new.devicereportedtime = '2009-03-25
23:59:59'::timestamp without time zone DO INSTEAD INSERT INTO
systemevents_032509 (message, facility, fromhost, priority,
devicereportedtime, receivedat, infounitid, syslogtag,
message_index_col)
VALUES (new.message, new.facility, new.fromhost, new.priority,
new.devicereportedtime, new.receivedat, new.infounitid, new.syslogtag,
new.message_index_col)
systemevents_insert_032609 AS
ON INSERT TO systemevents
WHERE new.devicereportedtime '2009-03-25 23:59:59'::timestamp
without time zone AND new.devicereportedtime = '2009-03-26
23:59:59'::timestamp without time zone DO INSTEAD INSERT INTO
systemevents_032609 (message, facility, fromhost, priority,
devicereportedtime, receivedat, infounitid, syslogtag,
message_index_col)
VALUES (new.message, new.facility, new.fromhost, new.priority,
new.devicereportedtime, new.receivedat, new.infounitid, new.syslogtag,
new.message_index_col)
systemevents_insert_032709 AS
ON INSERT TO systemevents
WHERE new.devicereportedtime '2009-03-26 23:59:59'::timestamp
without time zone AND new.devicereportedtime = '2009-03-27
23:59:59'::timestamp without time zone DO INSTEAD INSERT INTO
systemevents_032709 (message, facility, fromhost, priority,
devicereportedtime, receivedat, infounitid, syslogtag,
message_index_col)
VALUES (new.message, new.facility, new.fromhost, new.priority,
new.devicereportedtime, new.receivedat, new.infounitid, new.syslogtag,
new.message_index_col)
systemevents_insert_032809 AS
ON INSERT TO systemevents
WHERE new.devicereportedtime '2009-03-27 23:59:59'::timestamp
without time zone AND new.devicereportedtime = '2009-03-28
23:59:59'::timestamp without time zone DO INSTEAD INSERT INTO
systemevents_032809 (message, facility, fromhost, priority,
devicereportedtime, receivedat, infounitid, syslogtag,
message_index_col)
VALUES (new.message, new.facility, new.fromhost, new.priority,
new.devicereportedtime, new.receivedat, new.infounitid, new.syslogtag,
new.message_index_col)
syslog=# EXPLAIN ANALYZE SELECT DeviceReportedTime,
REPLACE(REPLACE(Message,'',''),'','') as Message, Facility,
FromHost, Priority, ReceivedAt, InfoUnitID ,SysLogTag FROM
SystemEvents WHERE message_index_col @@ to_tsquery('english', 'MAIL')
ORDER BY DeviceReportedTime DESC LIMIT 25 OFFSET 0;
QUERY PLAN
-
Limit (cost=61548.87..61548.93 rows=25 width=176) (actual
time=31933.287..31933.425 rows=25 loops=1)
- Sort (cost=61548.87..61551.59 rows=1091 width=176) (actual
time=31933.280..31933.327 rows=25 loops=1)
Sort Key: public.systemevents.devicereportedtime
Sort Method: top-N heapsort Memory: 29kB
- Result (cost=0.00..61518.08 rows=1091 width=176) (actual
time=43.351..28941.144 rows=21307 loops=1)
- Append (cost=0.00..61512.62 rows=1091 width=176)
(actual time=43.337..23706.264 rows=21307 loops=1)
- Seq Scan on systemevents (cost=0.00..13.00
rows=1 width=151) (actual time=0.007..0.007 rows=0 loops=1)
Filter: (message_index_col @@ '''mail'''::tsquery)
- Seq Scan on systemevents_032609 systemevents
(cost=0.00..27869.42 rows=494 width=152) (actual time=43.318..6153.645
rows=9309 loops=1)
Filter: (message_index_col @@ '''mail'''::tsquery)
- Seq Scan on systemevents_032509 systemevents
(cost=0.00..19001.65 rows=339 width=153) (actual time=0.611..5861.674
rows=6239 loops=1)
Filter: (message_index_col @@ '''mail'''::tsquery)
- Seq Scan on systemevents_032709 systemevents
Re: [GENERAL] PITRTools: Bring up warm standby - unexpected pageaddr
On Fri, 2009-03-27 at 10:54 +0100, Hannes Dorbath wrote: Hi, I'm trying to setup a warm standby using PITRTools. Environment is CentOS 5.2 x86_64 and PG 8.3.6 from yum.pgsqlrpms.org. This really should be on the pitrtools list: https://lists.commandprompt.com/mailman/listinfo/pitrtools Be happy to help you over there. As a note we are going to immediately as: post your ini files what exactly steps did you follow to fail over (did you just F999) what version for pitrtools are you running. Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgresql On Windows
On 27/03/2009 14:28, MDB wrote: What is the path format for the i (\i) command in the SQL shell? I think it's just as anywhere else on Windows, e.g. \i c:\path\to\file.sql If there are spaces in the path you may have to enclose it in double-quotes: \i c:\path with spaces\file.sql Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] metadata development
Happy spring. I am new to postgres/postgis and am trying to figure out the best way to approach documenting metadata within postgres. Has there been anything developed to add FGDC or Dublin Core standard metadata records into postgres for each table within the database? Is there any program that can access postgres to insert metadata based on a standard or any other way to document metadata for a postgres database? Any help, suggestions or advice from prior experiences would be greatly appreciated. Thanks in advance. Dara
Re: [GENERAL] Partitioned tabled not using indexes for full text search
Justin Funk fun...@iastate.edu writes: Any idea why it would not be using the indexes? The child table you showed us has a btree index on message_index_col, which is useless for @@ queries. Maybe you forgot to specify the index type while rearranging the table into partitioned form? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgresql On Windows
Thank you, I also found you could drop the C: (e.g. /sql/test.sql) if it is on the c drive. The backslashes on my system threw the same error.. --- On Fri, 3/27/09, John Cheng jlch...@ymail.com wrote: From: John Cheng jlch...@ymail.com Subject: Re: [GENERAL] Postgresql On Windows To: pgsql-general@postgresql.org Date: Friday, March 27, 2009, 12:16 PM From my experience, you must use the forward slash. Using the backslash may give you an error: \i C:\sql\test.sql C:: Permission denied Instead, use \i C:/sql/test.sql From: Raymond O'Donnell r...@iol.ie To: MDB mdb...@yahoo.com Cc: pgsql-general@postgresql.org Sent: Friday, March 27, 2009 7:35:47 AM Subject: Re: [GENERAL] Postgresql On Windows On 27/03/2009 14:28, MDB wrote: What is the path format for the i (\i) command in the SQL shell? I think it's just as anywhere else on Windows, e.g. \i c:\path\to\file.sql If there are spaces in the path you may have to enclose it in double-quotes: \i c:\path with spaces\file.sql Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgresql On Windows
MDB wrote: Thank you, I also found you could drop the C: (e.g. /sql/test.sql) if it is on the c drive. to be more precise, you can drop the C: if its the same as the current default drive for whatever process is reading it. Windows, for legacy reasons that date at least back to Digital Research's CP/M circa 1976, tracks default drive separately from default directory. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is there a meaningful benchmark?
On Fri, Mar 27, 2009 at 10:32 AM, Will Rutherdale (rutherw) ruth...@cisco.com wrote: Incidentally, in case people are interested in hearing the follow-up to my original message, my home-cooked benchmark with lots of processes doing tons of updates seemed to initially show MyISAM winning over Postgres. However I spent some time reading what I could from the manual on performance improvement and realized that commits might be an issue. I moved a commit() call out of the main loop, and bang! Postgres showed a substantial performance advantage over MyISAM. This is one of those areas where postgresql acts very differently from other dbs. In lots of other dbs big transactions are the performance killer. In PostgreSQL big transactions are the way to get better performace. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is there a meaningful benchmark?
I see. Perhaps earlier I alienated some people by talking about transactions per second, which is not necessarily an appropriate unit. Thanks for the tip. -Will -Original Message- From: Scott Marlowe [mailto:scott.marl...@gmail.com] Sent: 27 March 2009 16:08 To: Will Rutherdale (rutherw) Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Is there a meaningful benchmark? This is one of those areas where postgresql acts very differently from other dbs. In lots of other dbs big transactions are the performance killer. In PostgreSQL big transactions are the way to get better performace. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is there a meaningful benchmark?
On Fri, Mar 27, 2009 at 2:27 PM, Will Rutherdale (rutherw) ruth...@cisco.com wrote: I see. Perhaps earlier I alienated some people by talking about transactions per second, which is not necessarily an appropriate unit. Hard to say, tps is still important. A lot of loads need to change one thing in a transaction and change it fairly fast. A lot of other loads update / insert 10,000 rows, and tps doesn't mean as much as rows inserted / second. I doubt anybody took it personally though. More likely just got confused over what you and they were measuring. Storytime! One of my best friends came from MSSQL and MySQL shop, and when introduced him to pgsql, he was bitching at how slow this file full of inserts was inserting. This was in the pg 7.0 days, when pg was not super fast, but quite fast enough if you knew a few basic tricks of the trade. I looked at his load file, which was basically one insert after another, 10k of them. I pushed him aside, typed in begin; on top and commit; on the bottom and told him to run it now. It finished in about 10 seconds. It had taken 10 minutes before that. He's now my boss at another company, and pretty hard core pgsql fan. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to specify the locale that psql uses
I had given reply abt windows From: Shoaib Mir shoaib...@gmail.com To: Dhaval Jaiswal bablu_postg...@yahoo.com Cc: Kent Tong k...@cpttm.org.mo; pgsql-general@postgresql.org Sent: Friday, March 27, 2009 4:47:43 AM Subject: Re: [GENERAL] how to specify the locale that psql uses On Fri, Mar 27, 2009 at 9:35 AM, Dhaval Jaiswal bablu_postg...@yahoo.com wrote: When you install postgreSQL over XP at the same time it asks for encoding locale. Set at the same time. Follow the below link will give you more idea. http://www.postgresql.org/docs/7.2/static/multibyte.html Please updates yourself with the latest version as its 8.3.7 these days and you are still like years and years behind with version 7.2 documentation :) If you want to change the locale of existing database. You can't. The workaround for that you can create another cluster with initdb command with supported locale. I dont think so the person asking the question ever asked for changing encoding for the database. Please make sure to read the question atleast, as it was about changing locale for psql. In order to do that For server messages: --- set the configuration parameter lc_messages to a value you like to have for getting messages back from server. It can done in both ways, for the session and permanently. In order to do for a session use: set lc_message = and otherwise set it in your postgresql.conf file and reload. For Client messages: - For client programs like psql, change the current locale where you are starting up psql. I am not sure about windows but in Linux you can do this by setting LANG environment variable. -- Shoaib Mir http://shoaibmir.wordpress.com/
Re: [GENERAL] Installation Error, Server Won't Start
log_statement = none/ddl/mode/all log_min_duration = value -- to log query consumes more than set value -- Thanks Regards Dhaval Jaiswal From: Raymond O'Donnell r...@iol.ie To: ray ray.jos...@cdicorp.com Cc: pgsql-general@postgresql.org Sent: Friday, March 20, 2009 12:34:17 AM Subject: Re: [GENERAL] Installation Error, Server Won't Start On 18/03/2009 22:22, ray wrote: I do not see an opportunity to request a log to be written. From the Psotgre site, I downloaded: postgresql-8.3.7-1-windows.exe Ah, I see - that's the one-click installer, which is maintained by EnterpriseDB. I haven't used that one; I was thinking of the community installer (pgInstaller), and that's the one has the logging option. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Multidatabase query
Hi Mauro,
Not possible in PostgreSQL.
However, you can use dblink for creating query between multidatabase as
given below:
select empno from dblink('dbname=edb','SELECT empno from emp') as
emp(empno numeric)
union
select empno from dblink('dbname=enterprisedb','SELECT empno from emp')
as emp(empno numeric);
where edb and enterprisedb are database names
For More information about dblink, please follow the link given below:
http://www.postgresql.org/docs/current/static/dblink.html
http://www.enterprisedb.com/docs/en/8.3/oracompat/EnterpriseDB_OraCompat_EN_8.3-49.htm
Regards,
Vibhor Kumar
www.enterprisedb.com
Mauro Bertoli wrote:
Hi all,
is possible in PostgreSQL to create query between multidatabase like Sql Server
2005?
An example:
SELECT
db1.a.id FROM db1.a
UNION
db2.b.id FROM db2.b
Where db1 is a database and db2 is another database. a is a table in database db1 and
b is a table in database db2
Best regards,
Mauro
Re: [GENERAL] Determining PUBLIC's permissions
select a.relname, a.relowner, b.oid, b.rolname from pg_class a, pg_roles b where relname like 'table_name' and a.relowner=b.oid and relkind = 'r'; \dt+ -- Thanks Regards Dhaval Jaiswal From: Roderick A. Anderson raand...@cyber-office.net To: PostgreSQL pgsql-general@postgresql.org Sent: Friday, March 20, 2009 12:52:31 AM Subject: [GENERAL] Determining PUBLIC's permissions I'm trying to determine the permissions PUBLIC has on several tables/views. Or would this be the default permissions for a table/view? pg_user, pg_roles, pg_group, pg_authid, pg_auth_members, pg_database, pg_tablespace, and pg_settings. \dp pg_user returns (0 rows). I am logged in to template1 as postgres. I am doing some testing of multi-tenant/shared-database-hosting options and want to try to get around the possible limitations mentioned in the article at http://wiki.postgresql.org/wiki/Shared_Database_Hosting. After revoking from public I want to grant those permission back to the 'main' database user. Need to know what they are. I was sure there was a thread recently on determining table permissions and thought the \dp would do it. Any suggestions? \\||/ Rod -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgresql On Windows
postgres=# \i C:/dj1.sql; If there are space. -- postgres=# \i 'C:/Program Files/PostgreSQL/8.3/bin/dj.sql'; Thanks Regards Dhaval Jaiswal www.enterprisedb.com From: Raymond O'Donnell r...@iol.ie To: MDB mdb...@yahoo.com Cc: pgsql-general@postgresql.org Sent: Friday, March 27, 2009 8:05:47 PM Subject: Re: [GENERAL] Postgresql On Windows On 27/03/2009 14:28, MDB wrote: What is the path format for the i (\i) command in the SQL shell? I think it's just as anywhere else on Windows, e.g. \i c:\path\to\file.sql If there are spaces in the path you may have to enclose it in double-quotes: \i c:\path with spaces\file.sql Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
