Re: [GENERAL] pg_dump and --inserts / --column-inserts
Thomas Kellerer wrote on 17.07.2010 18:29: Want to do some experiments? Apparently there *is* a substiantial overhead, but I suspected the sending of the raw SQL literal to be a major factor here. (Server and JDBC program were running on the same machine) In case any one is interested. Out of curiosity I ran the same test with a local Oracle installation (10.2) and there the overhead is substantially lower (for 20 columns, only 2% slower, compared to 26% with Postgres) I can't run something equivalent to explain analyze in Oracle from within JDBC, so I could not compare those figures. But it seems that either the parsing overhead in Oracle is lower or the JDBC driver is more efficient... Regards Thomas -- 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] pg_dump and --inserts / --column-inserts
Tom Lane wrote on 17.07.2010 16:36: Thomas Kellerer writes: I'm till a bit surprised that parsing the statement _with_ a column list is mesurably slower than withou a column list. Well, nobody's offered any actual *numbers* here. It's clear that parsing the column list will take more time than not doing so, but whether that adds up to anything significant or even measurable compared to the rest of the statement's cost is not so clear. Want to do some experiments? OK, I wrote a small Java program that inserts rows using a column list and without column list I did that for 5,10,15 and 20 (integer) columns. then inserting 1 rows into the empty table. I measured the runtime as seen from the JDBC client and as reported by explain analyze (the last line reading "Total runtime:") All times are milliseconds and are averaged over 20 runs ColumnCount with columns without columns 5 1132 1092.6 10 1288.531148.33 15 1430 1215.67 20 1657.6 1313.2 Apparently there *is* a substiantial overhead, but I suspected the sending of the raw SQL literal to be a major factor here. (Server and JDBC program were running on the same machine) So I ran "EXECUTE ANALYZE" instead of INSERT to get the processing time of the server and remove the JDBC/SQL literal overhead. ColumnCount with columns without columns 5 116.33 115.3 10 149.89 128.28 15 169.94 159.14 20 197.72 193.66 Which still shows an overhead, but less. So apparently the "even slower" in the manual *is* right, as the overhead of sending the longer SQL Statement over the network does impact psql as well. -- 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] pg_dump and --inserts / --column-inserts
Craig Ringer wrote on 17.07.2010 03:13: On 17/07/10 04:26, Thomas Kellerer wrote: Hmm. For years I have been advocating to always use fully qualified column lists in INSERTs (for clarity and stability) And now I learn it's slower when I do so :( If you're not doing hundreds of thousands of identical ones at a time, it's still very much a good idea. The costs of parsing and transmission are usually pretty insignificant, and the readability/maintenance benefits are huge. It's only when dealing with bulk loading that this sort of thing starts to be worth thinking about. Thanks for the clarification. I'm till a bit surprised that parsing the statement _with_ a column list is mesurably slower than withou a column list. Regards Thomas -- 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] pg_dump and --inserts / --column-inserts
Tom Lane wrote on 16.07.2010 18:40: Thomas Kellerer writes: the explanation of the --inserts option of pg_dumps states that "The --column-inserts option is safe against column order changes, though even slower." The way I read this is, that INSERT INTO table (column, ...) VALUES ... is slower than INSERT INTO table VALUES ... Is that really true? I believe so, though I've not measured by how much. Why would explicitely stating the columns be slower than relying on implicit column ordering? Well, first off, the volume of pg_dump'd data gets a lot larger due to all the extra text. If your column values aren't textually wide, you could easily be looking at 2x the space. That costs in I/O and network transmission. Of course In the second place, it does take time to parse those column names and look them up in the catalog. Not much, but it'll add up since it's done over again for every row. Hmm. For years I have been advocating to always use fully qualified column lists in INSERTs (for clarity and stability) And now I learn it's slower when I do so :( Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_dump and --inserts / --column-inserts
Hi, the explanation of the --inserts option of pg_dumps states that "The --column-inserts option is safe against column order changes, though even slower." The way I read this is, that INSERT INTO table (column, ...) VALUES ... is slower than INSERT INTO table VALUES ... Is that really true? Why would explicitely stating the columns be slower than relying on implicit column ordering? Regards Thomas -- 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] Testing 9.0beta3 and pg_upgrade
Thomas Kellerer, 12.07.2010 23:29: Hi, I'm trying pg_upgrade on my Windows installation and I have two suggestions for the manual regarding pg_upgrade: I found another problem and I'm not sure if this is a bug or a user error :) My batch file to start pg_upgrade looks like this: %~dp0server\bin\pg_upgrade ^ --check ^ --old-bindir="c:/Program Files/PostgreSQL/8.4/bin" ^ --new-bindir="c:/etc/Postgres9.0-beta3/server/bin" ^ --old-datadir="c:/Daten/db/pgdata84" ^ --new-datadir="c:/etc/Postgres9.0-beta3/datadir" ^ --user=postgres ^ --logfile=migrate.log ^ --verbose with these settings pg_upgrade fails to start the old server. On the console I can see: Checking new data directory (c:/etc/Postgres9.0-beta3/datadir)ok ""c:/Program Files/PostgreSQL/8.4/bin/pg_ctl" -l "migrate.log" -D "c:/Daten/db/pgdata84" -o "-p 5432 -c autovacuum=off -c autovacuum_freeze_max_age=20 " start >> "nul" 2>&1" Trying to start old server ... Unable to start old postmaster with the command: ""c:/Program Files/PostgreSQL/8.4/bin/pg_ctl" -l "migrate.log" -D "c:/Daten/db/pgdata84" -o "-p 5432 -c autovacuum=off -c autovacuum_freeze_max_age=20" start >> "nul" 2>&1" Perhaps pg_hba.conf was not set to "trust". Now when I manually copy and paste the logged command, the server starts up fine and I can verify with ProcessExplorer that pg_upgrade indeed does not spawn a postgres.exe (or pg_ctl.exe) As I have done one successful migration already, and the only difference was the logfile, I removed the --logfile switch from the batch file and then pg_upgrade ran without problems. In the commandline starting the old server -l "migrate.log" is replaced with -l "nul" I _think_ the reason for this is that pg_upgrade locks migrate.log and then postgres.exe cannot write to the file and thus fails to start. When I manually run the command, pg_upgrade is not locking migrate.log and therefor the server starts (and thus the confusion that the same command works from the commandline but not when pg_upgrade runs it) Regards Thomas P.S.: pg_upgrade --help says: "Report bugs to " Shouldn't that be changed as well? -- 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] Testing 9.0beta3 and pg_upgrade
Thomas Kellerer, 12.07.2010 23:29: Hi, I'm trying pg_upgrade on my Windows installation and I have two suggestions for the manual regarding pg_upgrade: When specifying directories, pg_upgrade *requires* a forward slash as the path separator. This is (still) uncommon in the Windows world (although Windows does support it) and even though the example in the manual does show forward slashes, I think it would be a good idea to specifically mention the fact that it will *not* work with a backslash. There is another misleading error message. When the old *bin*dir is not specified correctly, pg_upgrade claims the old *data*dir does not exist Something like: pg_upgrade --check --old-bindir="c:/Program Files/PostgreSQL/8.4" (note the missing /bin part) will cause the following output: 'c:/Program' is not recognized as an internal or external command, operable program or batch file. Performing Consistency Checks - Checking old data directory (c:/Daten/db/pgdata84) check for postgres failed - No such file or directory It took me a while to find out that the bindir was wrong, not the datadir. The "c:/Program' is not recognized as an.." made it even more confusing. Regards Thomas -- 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] Testing 9.0beta3 and pg_upgrade
Craig Ringer, 13.07.2010 05:11: On 13/07/10 05:29, Thomas Kellerer wrote: I would suggest to either manually change the autocommit mode from within pg_upgrade or to add a note in the manual to disable/remove this setting from psqlrc.conf before running pg_upgrade. Personally I think the first option would be the better one. Should pg_upgrade be reading psqlrc at all? There are bound to be all sorts of exciting issues that psqlrc settings can create. I interpret the error message such that pg_upgrade _calls_ (i.e spawns) psql to run the CREATE DATABASE command. If that is true, probably the easiest solution would be to run psql using the -X switch. Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Testing 9.0beta3 and pg_upgrade
Hi,
I'm trying pg_upgrade on my Windows installation and I have two suggestions
for the manual regarding pg_upgrade:
When specifying directories, pg_upgrade *requires* a forward slash as the path
separator. This is (still) uncommon in the Windows world (although Windows does
support it) and even though the example in the manual does show forward
slashes, I think it would be a good idea to specifically mention the fact that
it will *not* work with a backslash.
Actually the error message when you do so is a bit misleading as well ("You must
identify the directory where the old cluster binaries reside") even though the
paramter is there.
After I sorted that out I ran pg_upgrade and it failed somewhere in the middle:
-- snip
C:\etc\pg90-beta3>C:\etc\pg90-beta3\pgsql\bin\pg_upgrade.exe --user=postgres
--old-datadir "c:/Daten/db/pgdata84/" -
-old-bindir "c:/Programme/PostgreSQL/8.4/bin/" --new-datadir
"c:/etc/pg90-beta3/datadir/" --new-port=5434 --new-bind
ir "C:\etc\pg90-beta3\pgsql\bin"
Performing Consistency Checks
-
Checking old data directory (c:/Daten/db/pgdata84) ok
Checking new data directory (c:/etc/pg90-beta3/datadir) ok
Checking for /contrib/isn with bigint-passing mismatch ok
Checking for large objects ok
Creating catalog dump ok
Checking for presence of required libraries ok
| If pg_upgrade fails after this point, you must
| re-initdb the new cluster before continuing.
| You will also need to remove the ".old" suffix
| from c:/Daten/db/pgdata84/global/pg_control.old.
Performing Migration
Adding ".old" suffix to old global/pg_control ok
Analyzing all rows in the new cluster ok
Freezing all rows on the new clusterok
Deleting new commit clogs ok
Copying old commit clogs to new server 1 Datei(en) kopiert
ok
Setting next transaction id for new cluster ok
Resetting WAL archives ok
Setting frozenxid counters in new cluster ok
Creating databases in the new cluster
psql:C:/etc/pg90-beta3/pg_upgrade_dump_globals.sql:29: ERROR: CREATE DATABASE c
annot run inside a transaction block
There were problems executing ""C:\etc\pg90-beta3\pgsql\bin/psql" --port 5434 --username
"postgres" --set ON_ERROR_STOP=on -f "C:\etc\pg90-b
eta3/pg_upgrade_dump_globals.sql" --dbname template1 >> "nul""
-- end of console output
The "cannot run inside a transaction block" rang a bell, and once I removed "\set
AUTOCOMMIT off" from my psqlrc.conf, pg_upgrade went through without problems.
I would suggest to either manually change the autocommit mode from within
pg_upgrade or to add a note in the manual to disable/remove this setting from
psqlrc.conf before running pg_upgrade. Personally I think the first option
would be the better one.
Regards
Thomas
--
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 9.0 beta 3 release announcement
Bruce Momjian wrote on 12.07.2010 21:34: Thom Brown wrote: Could someone clarify the info in this paragraph: "Note that, due to a system catalog change, an initdb and database reload will be required for upgrading from 9.0Beta1. We encourage users to use this opportunity to test pg_upgrade for the upgrade from Beta2 or an earlier version of 9.0. Please report your results." This suggests that the system catalog change only occurred in Beta2, not Beta3. So if that's the case, why would I want to test pg_upgrade going from Beta2 to Beta3 if they use the same system catalog layout? Yes, this is wrong. It should be "We encourage users to use this opportunity to test pg_upgrade for the upgrade from Beta1 or an earlier version of 9.0. Please report your results." However, I see the beta3 release notes are now on the web site so it seems too late to fix this. I'm a bit confused that pg_upgrade is "advertised" in this way, but is "hidden" in the manual under "additionally supplied modules". If I was a new user, I would look in the administration chapter for any reference on how to do in-place upgrades. Is there any reason why pg_upgrade is not documented in the "main" manual? Regards Thomas -- 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 download Non-Installer (ZIP) Postgres 8.3 for Windows?
Dave Page, 09.07.2010 10:20: So how do I download the "binaries only" (no installer) bundle of Postgres 8.3 for windows? EnterpriseDB don't produce one for 8.3. There is a copy from the old MSI installer at http://www.postgresql.org/ftp/binary/v8.3.11/win32/, but it's not binary-compatible with the one-click installers (different integer-datetime settings). Ah, right I forgot that 8.3 was bundled differently ;) Thanks for the quick response! Cheers Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to download Non-Installer (ZIP) Postgres 8.3 for Windows?
Hi, I'm trying to download the ZIP archive for Postgres 8.3 on Windows, but I can't find a download location where I do not need to register with EnterpriseDB When I go to http://www.enterprisedb.com/products/download.do and click on the Windows link for Postgres 8.3 I end up on the "Please register" page. When I follow the download link from http://www.postgresql.org/download/ I wind up at http://www.enterprisedb.com/products/pgbindownload.do but there is no link to version 8.3 So how do I download the "binaries only" (no installer) bundle of Postgres 8.3 for windows? Regards Thomas -- 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] SQL Query Help Please !
GrGsM, 06.07.2010 09:06: Now i need a column in the same result of the query which shows the difference between the two columns . For Example : the result shoud be Closedate , status , NT028, NT031, NT050,NT062 , NT028-NT031 Please note the last column in bold, i need the difference . Already answered here: http://www.dbforums.com/postgresql/1658135-sql-query-help-please.html -- 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] moderninzing/upgrading mail list format
Dennis Gearon, 05.07.2010 23:43: I belong to MANY email listservers, probably like all of us. All of them, I am on digest. The CONTENT from all of you contributors is superior, more mature, and more directly helpful than all the other lists. I think it has something to do with the conservative, structured mind set of us Dbase programmers. (Or at least when we discuss database issues ;-) However, I think that the mailing list world has moved beyond what we use, at least for those of us who receive digest mode. The google groups group all the same topics in one block, and uses intra-document html links to get to those blocks. By using the 'subjects contents' table at the top of the digest email, and the back button, it is VERY easy to investigate only the subjects one is intersted in, without having to scan through the whole digest. There are other, better-than-pgsql-mail-program convenience attributes of the google groups email system. I read it through the gmane newsreader, so I get threaded display and can easily "scan" the subjects. Regards Thomas -- 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] Out of memory on update of a single column table containg just one row.
Hi,
i tried a simple test:
create temp table _t as select repeat('x',382637520) as test;
update _t set test=test||test;
pg 8.3 32bit fails with
[Error Code: 0, SQL State: 53200] ERROR: out of memory
Detail: Failed on request of size 765275088.
pg 8.4.4 64bit works fine
so upgrade to 64bit
regards
Thomas
--
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] Uncable to commit: transaction marked for rollback
Hi,
it happens fi at transaction timeout or you executed a broken/failed
statement und catched the exception
try { call invalid sql } catch (Exception e) {}
connection.commit() <- exception throws here, happens automatically
inside your appserver
regards
Thomas
Am 01.07.2010 20:06, schrieb David Kerr:
> I'm intermittantly getting this error message in a java app.
> using Geronimo / Hibernate / Postgres 8.3.9
>
> javax.transaction.RollbackException: Unable to commit: transaction marked for
> rollback
>
> Can someone give me a scenario where this would happen? "unable to commit"
> makes everyone immediatly go to database issue. But it seems like an app
> issue to me.
>
> I was thinking that maybe it's a 2 phase commit / XA or something like that.
> (TX open, phase 1 commit fails, phase 2 commit throws this error?)
>
> I can't imagine how this would happen within a single transaction setup.
>
> Thanks!
>
> Dave
>
>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] owner of a database does not own "public" schema?
Hi, I was playing around with schemas and noticed that that the owner of a the database (specified with the CREATE DATABASE command) is not the owner of the database's public schema: (Connect as super user) c:\temp>psql postgres postgres Password for user postgres: psql (8.4.3) Type "help" for help. postgres=# create user foo password 'bar'; CREATE ROLE postgres=# create database foo owner = foo encoding = 'UTF-8'; CREATE DATABASE postgres=# \q c:\>psql foo foo Password for user foo: psql (8.4.3) Type "help" for help. foo=> drop schema public; ERROR: must be owner of schema public foo=> (As you can see, I'm using Postgres 8.4.3 on Windows) I understand that I could grant the necessary privileges to the role after creating the database. I'm just curious why the databse owner is not the owner of the public schema. Regards Thomas -- 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] Looking for a PostGre SQL Trainer, Bangalore
Ravi Kariparmbil - Epistiuum Solutios, 28.06.2010 14:40: Hello, I am looking for a PostGre SQL trainer who can do a training program for a client of mine in Bangalore. I think this if off-topic here And you should learn how to write the name correctly :) Writing PostGre is like writing MerCed, PoRsch, ToYot, MicroSof, OrAcl or BangAlor Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: Need Some Recent Information on the Differences between Postgres and MySql
Thomas Kellerer, 25.06.2010 14:32: Wang, Mary Y, 25.06.2010 01:04: Hi, I'm trying to find some write-ups about the differences between Postgres and MySql. A lot of stuff showed up on Google, but most of them are old. I saw this wiki over here http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL_2009 and plan to watch a recent webcast on PostgreSQL vs. MySQL that was offered by EnterpriseDB. Are there any other most recent summaries on the differences between Postgres and MySql? Thanks in advance Mary My favorite features in Postgres that MySQL doesn't have - deferrable constraints - sequences - check constraints - windowing functions - recursive common table expressions - the absence of a program to check the consistency of the data - the ability to use a subselect in a DML statement that references the table to be updated - generate_series() - array handling And another thing: The following works in Postgres (and Oracle, DB2, SQL Server, Derby) but not in MySQL (using InnoDB): create table fktest ( idinteger primary key not null, name varchar(20), parent_id integer ); alter table fktest add constraint fktest_parent foreign key (parent_id) references fktest(id); insert into fktest (id,name,parent_id) values (1,'Root', null); insert into fktest (id,name,parent_id) values (2,'Sub1', 1); insert into fktest (id,name,parent_id) values (3,'Subsub', 2); insert into fktest (id,name,parent_id) values (4,'Sub2', 1); commit; delete from fktest where id in (1,2,3,4); commit; MySQL complains that it cannot delete the rows"Cannot delete or update a parent row: a foreign key constraint fails" Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: Need Some Recent Information on the Differences between Postgres and MySql
Wang, Mary Y, 25.06.2010 01:04: Hi, I'm trying to find some write-ups about the differences between Postgres and MySql. A lot of stuff showed up on Google, but most of them are old. I saw this wiki over here http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL_2009 and plan to watch a recent webcast on PostgreSQL vs. MySQL that was offered by EnterpriseDB. Are there any other most recent summaries on the differences between Postgres and MySql? Thanks in advance Mary My favorite features in Postgres that MySQL doesn't have - deferrable constraints - sequences - check constraints - windowing functions - recursive common table expressions - the absence of a program to check the consistency of the data - the ability to use a subselect in a DML statement that references the table to be updated - generate_series() - array handling Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: Need Some Recent Information on the Differences between Postgres and MySql
John Gage, 25.06.2010 11:50: Replying to my own post, and on further examination of the MySQL documentation, I am astonished to discover that MySQL does not support regular expressions much less something like tsvector. Please disabuse me of this idea if I am mistaken. Getting really off-topic now: but MySQL does support Regex http://dev.mysql.com/doc/refman/5.1/en/regexp.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: Need Some Recent Information on the Differences between Postgres and MySql
Wang, Mary Y, 25.06.2010 01:04: Hi, I'm trying to find some write-ups about the differences between Postgres and MySql. A lot of stuff showed up on Google, but most of them are old. I saw this wiki over here http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL_2009 and plan to watch a recent webcast on PostgreSQL vs. MySQL that was offered by EnterpriseDB. Are there any other most recent summaries on the differences between Postgres and MySql? Thanks in advance Mary You might be interested in these postings (from a MySQL developer?) http://krow.livejournal.com/692692.html http://marksverbiage.blogspot.com/2010/05/mysql-what-are-you-smoking.html Thomas -- 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 flatten a database table
mai fawzy, 22.06.2010 10:38: I have a table that has the following fields: IDMoney Date State 1 20 2010-01-01 done 2 10 2010-01-02done I need to select the values from this table to join them 2 another select statement but the problem that I need to flatten this table first. I need the returned values to be as one row. Based on which condition? -- 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] A thought about other open source projects
David Goodenough wrote on 20.06.2010 11:08: I don't support anyone has written a "how to write database agnostic code" guide? That way its not a matter of porting, more a matter of starting off right. I don't believe in "database agnostic code". In the end it basically means that the application will run equally slow on all platforms. I'm not necessarily talking about syntax features/differences (e.g. hierarchical queries or other advanced features) but about behavioral features that stem from the way the engine works, e.g. due to different locking strategies or different optimizers. Some engines don't like single large transactions, some don't like a lot of small transactions. Then think about syntactically identical statements that will behave differently because each engine has different optimization strategies. Some engines are better with complex joins and subqueries some are better with several small queries. An index that might be used in one engine to speed up a select might be totally ignored by another. Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: Error on Windows server could not open relation base/xxx/xxx Permission denied
Magnus Hagander, 07.06.2010 16:15: On Mon, Jun 7, 2010 at 15:58, Thomas Kellerer wrote: Magnus Hagander, 07.06.2010 15:52: Some AV software probably behaves fine. Probably. In case anyone is interested: I have two development computers that run Postgres on Windows XP. One with Avira the other with Sophos. Neither has or had any problems installing or running Postgres What kind of load do the systems have? Particularly, how many parallell connections? That seems to push things over the edge more often than high transaction single-user ones. Ah, that might make the difference: I have no real load on those computers (as I said, developer machine) So it's more a single-user type of load Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: Error on Windows server could not open relation base/xxx/xxx Permission denied
Magnus Hagander, 07.06.2010 15:52: Some AV software probably behaves fine. Probably. In case anyone is interested: I have two development computers that run Postgres on Windows XP. One with Avira the other with Sophos. Neither has or had any problems installing or running Postgres Regards Thomas -- 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] use of IN() with literals
Dennis Gearon wrote on 18.05.2010 19:05:
select * from pg_class where relkind IN IN (‘r’, ‘v’, ‘S’);
^^ ^ ^
You repeated the keyword IN, and you are using the wrong quotes (unless this is a
copy & paste problem of a broken email client)
select *
from pg_class
where relkind IN ('r', 'v', 'S');
should work
--
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] Why Performance of SQL Query is *much* Slower in GUI PgAdmin
Scott Marlowe, 17.05.2010 10:58: Why on earth would anybody compare database performance using a command that is usually executed only once in the lifetime of a database? It's like saying "The car from manufacturer A is slower than the one from manufacturer B, because it takes 1 second longer to start the engine..." I refer you to this classic post on the subject: http://www.mail-archive.com/pgsql-general@postgresql.org/msg93043.html Nice one :) That reminds me of a "fun" car test in a German car magazine some years ago. They were comparing the cheapest and the most expensive car from the Fiat group: a Fiat 127 (similar to a Fiat 500 nowadays) against a Ferarri Testarossa. The first test was to get 3 people from Munich to Augsburg (approx. 70km), the Ferarri lost with about 30 minutes difference because it had to drive two times ;) Regards Thomas -- 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] Why Performance of SQL Query is *much* Slower in GUI PgAdmin
Yan Cheng CHEOK, 17.05.2010 03:21: Recently, I try to introduce my friend to use PostgreSQL. However, he first impression is that. PostgreSQL is much slower compared to MySQL. He realize he has to wait for 7 seconds, to create a tmp database. Why on earth would anybody compare database performance using a command that is usually executed only once in the lifetime of a database? It's like saying "The car from manufacturer A is slower than the one from manufacturer B, because it takes 1 second longer to start the engine..." Thomas -- 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] last and/or first in a by group
Dino Vliet wrote on 16.05.2010 18:07: Dear postgresql experts, I want to know if postgresql has facilities for getting the first and or the last in a by group. Suppose I have the following table: resnr,dep,arr,cls,dbd meaning reservationsnumber, departure station, arrival station, the class of the reservation and the daysbeforedeparture and records like: xxx,NYC,BRA,C,80 xxx,NYC,BRA,M,75 xxx,NYC,BRA,Q,50 yyy,WAS,LIS,T,55 zzz,NYC,LIS,Z,40 zzz,NYC,LIS,J,39 I want to select only the most recent records being: xxx,NYC,BRA,Q,50 yyy,WAS,LIS,T,55 zzz,NYC,LIS,J,39 Something like this? SELECT * FROM your_table t1 WHERE dbd = (SELECT min(dbd) FROM your_table t2 WHERE t2.dep = t1.dep AND t2.arr = t1.arr AND t2.resnr = t1.resnr) Regards Thomas -- 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] Reliability of Windows versions 8.3 or 8.4
Richard Broersma wrote on 12.05.2010 17:45: I'm considering using the windows version PostgreSQL in the following conditions: at least 10 years of up time (with periodic power failures<= 1 a year) I don't think you can get 10 years of up time on a Windows Server. Most of the security patches will need a reboot, and that means probably one reboot every month. Thomas -- 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] Sorting with materialized paths
Ovid wrote on 09.05.2010 15:33: My apologies. This isn't PG-specific, but since this is running on PostgreSQL 8.4, maybe there are specific features which might help. I have a tree structure in a table and it uses materialized paths to allow me to find children quickly. However, I also need to sort the results depth-first, as one would expect with threaded forum replies. id | parent_id | matpath | created +---+-+ 2 | 1 | 1 | 2010-05-08 15:18:37.987544 3 | 1 | 1 | 2010-05-08 17:38:14.125377 4 | 1 | 1 | 2010-05-08 17:38:57.26743 5 | 1 | 1 | 2010-05-08 17:43:28.211708 7 | 1 | 1 | 2010-05-08 18:18:11.849735 6 | 2 | 1.2 | 2010-05-08 17:50:43.288759 9 | 5 | 1.5 | 2010-05-09 14:02:43.818646 8 | 6 | 1.2.6 | 2010-05-09 14:01:17.632695 So the final results should actually be sorted like this: id | parent_id | matpath | created +---+-+ 2 | 1 | 1 | 2010-05-08 15:18:37.987544 6 | 2 | 1.2 | 2010-05-08 17:50:43.288759 8 | 6 | 1.2.6 | 2010-05-09 14:01:17.632695 3 | 1 | 1 | 2010-05-08 17:38:14.125377 4 | 1 | 1 | 2010-05-08 17:38:57.26743 5 | 1 | 1 | 2010-05-08 17:43:28.211708 9 | 5 | 1.5 | 2010-05-09 14:02:43.818646 7 | 1 | 1 | 2010-05-08 18:18:11.849735 Try this: with recursive thread_display (id, parent_id, matpath, created, sort_key) as ( select id, parent_id, matpath, created, array[id] as sort_key from threads where id = 1 union all select c.id, c.parent_id, c.matpath, c.created, p.sort_key||array[c.id] from threads c join thread_display p on c.parent_id = p.id ) select id, parent_id, matpath, created from thread_display order by sort_key; Thomas -- 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 vs. Microsoft SQL server
On Sun, May 2, 2010 at 7:57 PM, Andy wrote: > Skype, perhaps the largest telephony app in the world, uses Postgresql. > > Here's some info on their postgresql usage: > > http://highscalability.com/skype-plans-postgresql-scale-1-billion-users > https://developer.skype.com/SkypeGarage/DbProjects/SkypePostgresqlWhitepaper Thank you very much for those two links. Very interesting reading indeed. And also thanks to all the other replies in this thread. You've all given me something to think about. :o) Thomas -- 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 vs. Microsoft SQL server
Greg Smith wrote on 02.05.2010 01:16: Scott Ribe wrote: PG's locking scheme, MVCC, basically precludes certain specific optimizations that means a small number of very specific queries don't perform as well, while at the same time it means that throughput with multiple simultaneous connections scales extremely well with multiple processors. SQL Server uses MVCC too as of their 2005 release, implemented with row versioning similarly to Postgres. The main non-MVCC holdout at this point is DB2. AFAIK even in a fresh install of SQL Server 2008 the row versioning is turned off by default (at least this is true for 2005) I don't know if this is for compatibility reason or because of the performance penalty that comes with it And DB2 9.7 introduced MVCC as part of their Oracle compatibility. Thomas -- 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] postgres crashes - could not reattach to shared memory
Sofer, Yuval wrote on 02.05.2010 09:27: Hi Postgres crashes with - PG "FATAL: could not reattach to shared memory (key=5432001, addr=0210): Invalid argument. The version is 8.2.4, the platform is win32 Does someone know the reason/workaround ? I think this is supposed to be fixed with 8.4 Regards Thomas -- 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 vs. Microsoft SQL server
Anybody know of any recent comparisons made between the two? I'm in the process of buying a new telephony related software suite, and I'm getting mixed advice. Some say that MSSQL is _much_ better/faster than PostgreSQL, and others say the opposite. The vendor is more or less indifferent, with a small plus to the Microsoft solution because, well, they are a Microsoft shop. The sales-people all bang on about MSSQL being the superior choice, and PostgreSQL being a "toy compared to the Microsoft RDBMS". The tech people though are divided into three groups: One group says the two systems are more or less equal, another group who says the Microsoft database is superior and finally a group who speaks highly of PostgreSQL. I've not been able to convince them to send me some actual benchmark numbers, which actually should turn on quite a few alarms, come to think about it. :o) Maybe you guys are aware of some recent generic tests/comparisons between the two systems? Regards, Thomas -- 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] sql help, reusing a column
Andy Colson wrote on 29.04.2010 23:51: Here is my query, which works: select organization, state, (select max(idate) from times where customers.custid=times.custid and taskid = 27) as lastdate, age( (select max(idate) from times where customers.custid=times.custid and taskid = 27) ) from customers order by lastdate desc nulls last; I'd love to use age(lastdate) instead of age( (repeat sql) ), but it does not seem to work. This should work: SELECT organization, state, lastdate, age(lastdate) FROM ( SELECT organization, state, (select max(idate) from times where customers.custid=times.custid and taskid = 27) as lastdate FROM customers ) t order by lastdate desc -- 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] Convert odb to Postgres
Luís de Sousa, 26.04.2010 12:09: Hello everyone, I have an OpenOffice dabatase that I'd like to convert to Postgres. More specifically I need to replicate tables, not null constraints, primary keys, foreign keys, autonumbers and data. There's a tool that does this with Microsoft databses: http://wiki.postgresql.org/wiki/Microsoft_Access_to_PostgreSQL_Conversion Is there a counterpart of this tool for OpenOffice? Or any other tool that may for instance convert OpenOffice databases into SQL statements? odb is essentially a HSQL[1] database. You can unzip the odb file, and then access the contents of the database with any JDBC (Java) enabled SQL Tool. The actual HSQL database consists of two files: one with the extension .properties and the other with the extension .script The Postgres Wiki contains several JDBC SQL Tools: http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools Regards Thomas [1] http://www.hsqldb.org/ -- 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] EXCEPT doesn't compare TIMESTAMP type?
dipti shah, 23.04.2010 13:17: Thanks but I don't have text type in my table. sysdb=# \d changelogtest techdb=# INSERT INTO changelogtest (id, txid, txtime) values (5, 123, 'now') except select id, txid, txtime from changelogtest where id=5; 'now' *is* a text type value Thomas -- 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] Performance impact of log streaming replication
Andy, 21.04.2010 01:44: No I haven't. I'm using MySQL right now. But I want to learn more about Postgresql's Hot Standby and see if it offers a better replication solution. Can anyone share their experience about Postgresql replication performance impact? Thanks. You might be interested in this post: http://www.depesz.com/index.php/2010/02/01/waiting-for-9-0-streaming-replication/ -- 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] Embedded Postgres
Ognjen Blagojevic, 21.04.2010 17:08: More precisely, to run it without using TCP/IP port No and without installing as a service? Yes (simply run pg_ctl "manually" from the command line) Thomas -- 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 find avg() of sum()?
semi-ambivalent wrote on 16.04.2010 19:57: I have some data fields that I have summed, grouped by a date field. The sums are different. How can I then calculate the average value for those sums? Everything I've tried errors out with something along the lines of using agregates where I can't, or for using multiple values where that is not allowed. I'm sure this can be done in one query, without temp tables, but I don't know it and haven't found it yet in the docs. Assuming your sum() statement looks like: SELECT one_field, sum(other_field) FROM the_table GROUP BY one_field; You can get the average of the sums using: SELECT avg(the_sum) FROM ( SELECT one_field, sum(other_field) as the_sum FROM the_table GROUP BY one_field ) t Thomas -- 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] Where to configure pg_xlog file-size?
Clemens Eisserer wrote on 12.04.2010 23:25: Hi, I am using postgres-8.3 on an embedded ARM9 system. Works pretty well, except for stoarge consumptions. The actual table data is rather small, but postgres creates 2 16mb files in pg_xlog: r...@mesrv:/var/lib/postgresql/8.3/main# ls -la pg_xlog/ total 32820 -rw--- 1 postgres postgres 16777216 2010-04-12 15:00 00010006 -rw--- 1 postgres postgres 16777216 2010-04-11 23:42 00010007 Is there anything I can do to lower the size of those two files? What are reasonable values for smaller databases, and if it can be changed, what impact would it have on the system? Thanks, Clemens Those are checkpoint segments. I don't think you change the size of the files, but you should be able to limit that to one file. http://www.postgresql.org/docs/8.3/static/runtime-config-wal.html#RUNTIME-CONFIG-WAL-CHECKPOINTS Although I have no idea about the impact regarding performance. But I guess if you don't have too many writes it might actually be OK. Thomas -- 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] "1-Click" installer problems
Magnus Hagander, 01.04.2010 11:50: 2010/4/1 Craig Ringer: instead of %ProgramFile%. I bet half of the problems would go away if the installer refused to put the data directory into c:\Program Files. Yep - it's not a clever place to put it. IIRC, that was modeled on where Microsofts own SQL Server put it's data files by default. Shouldn't Postgres make it better than Microsoft ;) -- 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] "1-Click" installer problems
Craig Ringer, 01.04.2010 09:24: I do not like the installer's suggestion to put the data directory into c:\Program Files either, I think this should default to %APPDATA% That seems fairly sensible *IF* it checks very carefully to make sure the postgresql user does not have a roaming profile, ie they're a local user not a domain user. I think the installer should simply not "suggest" any directory, but force the user to select one manually (maybe even activley prevent c:\Program Files). I don't know if this is possible (or how hard it would be) but I think a very useful feature for the installer would be to try to check the permissions that the service account has on the chosen data directory. If the datadir was put in an account with roaming profiles enabled, Windows would try to sync the datadir to and from the profile share on the server at every user login/logout. Ah, didn't think of that one. such as a virus scanner or some funky option. True. In the german Postgres forum there are several posts regarding that topic. It seems that especially Norton and the Windows built-in Antivirus do not work well with Postgres. Personally I have no problems with Sophos and Avira Thomas -- 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] "1-Click" installer problems
Nikhil G. Daddikar, 01.04.2010 08:04: In about 30 seconds I found the following unanswered threads relating to installation on Windows Vista. If anybody is interested I can find more. The problem with this kind of statistics is that you will only find people who complain, you'll never find people who do not complain because they have no problems. Actually that's true for all internet forums or mailing lists: you'll seldomly find people posting something like "Hey everything works fine, I had no problems". All the posts seem to share the same root cause: the data directory has been put into "c:\Program Files" but a regular user does not have write permissions on that directory. As the installer is usually run with Administrator rights, the directory can be created but the service (or initdb) runs under a normal user account that cannot write to that directory because. I do not like the installer's suggestion to put the data directory into c:\Program Files either, I think this should default to %APPDATA% instead of %ProgramFile%. I bet half of the problems would go away if the installer refused to put the data directory into c:\Program Files. Given the fact that Microsoft finally tries to enforce people not to work as Administrators makes this even more important. My suggestion is to try to use a different data directory when installing Postgres and make sure that the postgres service account is allowed to read and write that directory. Personally I switched to using the ZIP packages completely because it is so much easer (unzip, initdb, pg_ctl -register, done) Thomas -- 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] need a query
and what abourt that : http://www.postgresql.org/docs/8.4/interactive/functions-string.html Le lundi 29 mars 2010 à 14:44 +0600, AI Rumman a écrit : > I need a query to get the initial letter of the words: > Like: > > Ispahani Public School IPS > Ahmed Iftekhar AI > > Any help please.
[GENERAL] Re: Is there any easy way to determine a default value specified for table column?
Belka Lambda wrote on 14.03.2010 01:24: Hi everyone! Is there a way to "nicely" determine a default value of a table column? A function, which could be used, like: The defaults are store in pg_attrdef, the corresponding column definitions in pg_attribute. So you would need to do a join between the two tables, something like: select c.relname, a.attname, def.adsrc from pg_attrdef def join pg_class c on def.adrelid = c.oid join pg_attribute a on a.attrelid = c.oid and a.attnum = def.adnum where c.relname = 'the_table_name' Thomas -- 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] \copy command: how to define a tab character as the delimiter
Raymond O'Donnell wrote on 09.03.2010 18:39:
This is Postgres you're talking about - of course it's that easy! :-)
:)
The main reason I asked, was that the manual actually claims that '\t' can be used
("The following special backslash sequences are recognized by COPY FROM")
As this is part of the description for the COPY command, does this maybe mean
it is only valid for COPY but not for \copy?
if that is the case, it should be documented somewhere).
Or is this related to the value of standard_conforming_strings?
Thomas
--
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] \copy command: how to define a tab character as the delimiter
Tom Lane wrote on 09.03.2010 18:21: Thomas Kellerer writes: \copy foo (foo, bar) from foobar.txt delimiter as '\t' csv header So how can I specify a tab character if I also need to specify that my file has a header line? Type an actual tab. Blush That easy? Thanks Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] \copy command: how to define a tab character as the delimiter
Hi, I tried to import a text file using the \copy command in psql using the following: \copy foo (foo, bar) from foobar.txt delimiter as '\t' csv header but that gives me an error: ERROR: COPY delimiter must be a single one-byte character So how can I specify a tab character if I also need to specify that my file has a header line? Regards Thomas -- 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] timestamp literal out of line
Tom Lane wrote on 07.03.2010 16:34: We wouldn't even support it at all because it's so syntactically messy and inextensible I like it :) It's the only cross-DBMS way to write down a date or timestamp literal. Thomas -- 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] need some advanced books on Postgres
Good advice ,tks both of you . For database books ,I found so many good books on Oracle,some on mysql,but db2 and postgres, so few. I have to read some books on Oracle for some advanced topics,although oracle and postgres are different ,I also get some useful info from it . I hope postgres will be as popular as linux one day , :) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: [NOVICE] Is there any oracle user_source table equivalent in postgre?
venkatra...@tcs.com, 05.03.2010 13:04: Thanks Thomas for your reply. when i am trying - select * from pg_catalog.pg_proc.prosrc You have to use: SELECT prosrc FROM pg_catalog.pg_proc Thomas -- 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 any oracle user_source table equivalent in postgre?
venkatra...@tcs.com, 05.03.2010 12:40: Hello, Can any one help me if oracle like any user_source table is there in postgre 8.4 (in pg_catalog or information_schema). Actually we migrated code from Oracle to Postgre. Now i want to search in how many places( i.e. in functions ) we have used a particular syntax( e.g. current_date). This can be achieved by using data dictionary view user_source in oracle. thanks in advance... Source code for stored functions is available in pg_catalog.pg_proc.prosrc Source code for views is available in pg_catalog.pg_views.definition All catalog views are documented here: http://www.postgresql.org/docs/current/static/catalogs.html Thomas P.S.: it's either Postgres or PostgreSQL but never Postgre ;) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] need some advanced books on Postgres
sigh,I didn't find a book with enough internal topics. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Not all functions in schema pg_catalog are "visible"
Hi, I just noticed that that there are functions defined (such as pg_catalog.time(timestamp) that can only be called when prefixed with pg_catalog. However other functions (that are at first glance defined identically to time()) can be called without prefixing them with pg_catalog. My understanding is that time(timestamp) is there to support the various CAST ( .. ) expressions, but why isn't it exposed as a "regular" function as well (as it seems to work just fine when being called directly) So, just out of curiosity: how could I tell by looking at pg_proc (or other system tables) which of those functions is "public" and which is not? Regards Thomas -- 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] FSM and VM file
akp geek wrote on 02.03.2010 22:11: Hi all - There are lot of FSM and VM files getting generated in the base directory. Do we need these files and I don't know the reason why these files are getting generated. I read the documentation, but not able to follow well. I will read it again. But do we need to keep these files or there is any process can we run to clean these. Can you please help? Regards My understanding is that the FSM files are the "Free Space Map" files. But you should never ever under no circumstance delete files manually from the data directory! Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] does writer process also write data to WAL log files ?
I found not only WAL writer process write data to WAL log files ,but also write process which I thought only write dirty buffer to data file . Could some body tell me the reason?Tks a lot !! -- 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] Performance comparison
Greg Smith, 25.02.2010 17:47: Based on tests showing a similar style and magnitude regression at Sun by Jignesh Shah, I would assume this is mainly because some of the starting parameter changes in 8.4 detuned this particular benchmark a bit, in favor of proving a better default for real-world users. For example, the starting default_statistics_target was raised from 10 to 100 in 8.4. This causes a mild decrease in performance on trivial benchmarks like this one, while potentially providing a large improvement in the sorts of query plans seen in real applications. That was the basic theme for the sorts of performance changes that showed up in 8.4. Another example (not actually relevant to this benchmark) is that the Free Space Map used to track deleted items is now kept on disk instead of in shared memory. That's obviously less efficient in the short term--disk write instead of just a memory one--but it prevents all sorts of nasty worst-case scenarios you used to run into the FSM wasn't big enough in earlier versions. Basically, the 8.4 performance related changes reduced average performance on trivial benchmark workloads a small amount, in favor of large improvements in the sort of situations people run into in production deployments. I think it was the right trade-off to make. Thanks for the detailed answer! Regards Thomas -- 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] Performance comparison
Greg Smith, 25.02.2010 03:13: Martijn van Oosterhout wrote: I remember a while back someone posted a graphs showing a scalability of postgresql for various versions (I think 8.0 to 8.4). I've tried to find this image again but havn't been able to locate it. Does anyone here remember? http://suckit.blog.hu/2009/09/29/postgresql_history It would be interesting to know why the max. performance in the r/w scenario for 8.4.1 is lower compared to 8.3.7 (and if maybe 8.4.2 fixed this) Thomas -- 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] Procedures
Nilesh Govindarajan wrote on 20.02.2010 14:28: Okay here's my query - select c.cid, c.subject, n.title from comments c, node n where c.nid = n.nid and c.status != 0; This is the query to check list of comments requiring admin approval and also the article titles on which this is posted. I want to see this result on the screen at psql prompt. Since it may return multiple rows, a cursor has to be employed here. Hmm, I don't understand your question. When you run the query, psql will display the result... Thomas -- 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] Procedures
Nilesh Govindarajan wrote on 20.02.2010 14:08: On 02/20/2010 02:32 PM, John R Pierce wrote: Nilesh Govindarajan wrote: How do I create a procedure using plpgsql cursors to print the output of the query in the cursor (using for loop) ? In all docs I found, it seems to be a must to return data to the call which is not what I want. what is it going to print it on? the postgres server processes have no console or stdout device. Okay, so how do I print it to stdout ? Even if you could, that would be stdout of the *server*, not the one of the client calling the procedure! Regards Thomas -- 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 can I get the English version?
Nils Gösche wrote on 20.02.2010 00:20: set LC_MESSAGES=English Yes, that works very well for psql, thanks! However, pgAdmin is still in German. Any other trick? File -> Options -> User Language -> English works for me Thomas -- 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 can I get the English version?
Nils Gösche wrote on 19.02.2010 23:29: Hi! I am running PostgreSQL on a German Windows machine. Client programs like psql and pgAdmin are printing German translation strings everywhere, even though I told the installer to use English/United States locale. How can I disable all translations and simply use the English version of all programs? Regards, set LC_MESSAGES=English Regards Thomas -- 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] PERFORM not working properly, please help..
And what about that : http://www.postgresql.org/docs/8.4/interactive/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-SET Maybe my french english disallowed me to understand right the question, but I think that this item could help in a way! Le vendredi 19 février 2010 à 11:04 +0100, Pavel Stehule a écrit : > 2010/2/19 : > > Hi Pavel, thanks for reply. Your solution: > > > > CREATE OR REPLACE FUNCTION A3() RETURNS VOID AS $BODY$ > > begin > > return query select * from A1(); > > return; > > end; > > $BODY$ LANGUAGE 'plpgsql'; > > > > generates error "cannot use RETURN QUERY in a non-SETOF function" because > > A3 returns VOID. > > problem is in A3, cannot be void. > > PostgreSQL has only function. It hasn't "procedures" where you can > execute unbinded queries. So if you can take any result from any > rutine, you have to take it explicitly. VOID in pg means, there are no > any interesting result, really no any interesting result. It can be > problem, when you know MySQL procedures or MSSQL procedures. You have > to forgot on procedures with returning recordset or multirecordset as > secondary effect. > > regards > Pavel Stehule > > > > > > > "Pavel Stehule" napisał(a): > > > Hello > > > > > > 2010/2/18 : > > > > I have a function A1 that returns setof records, and I use it in two > > ways: > > > > 1) from function A2, where I need results from A1 > > > > 2) from function A3, where I don't need these results, all I need is to > > > > execute logic from A1 > > > > > > > > Here ale very simple versions of my functions: > > > > > > > > CREATE OR REPLACE FUNCTION A1() RETURNS setof record AS $BODY$ > > > > begin > > > > Â -- some logic here > > > > Â return query select col from tab; > > > > end; > > > > $BODY$ LANGUAGE 'plpgsql'; > > > > > > > > CREATE OR REPLACE FUNCTION A2() RETURNS setof record AS $BODY$ > > > > begin > > > > Â -- some logic here > > > > Â return query select * from A1() as dummy ( x double precision); > > > > end; > > > > $BODY$ LANGUAGE 'plpgsql'; > > > > > > > > CREATE OR REPLACE FUNCTION A3() RETURNS VOID AS $BODY$ > > > > begin > > > > Â perform A1(); > > > > end; > > > > $BODY$ LANGUAGE 'plpgsql'; > > > > > > > > And here are my function calls: > > > > select * from A1() as(x double precision) --ok > > > > select * from A2() as(x double precision) --ok > > > > select * from A3(); --not ok, argh! > > > > > > > > > > it is correct. Every function has own stack for result. There are not > > > some global stack. Perform just run function and doesn't copy inner > > > result's stack to outer result stack. > > > > > > your A3 function have to be > > > begin > > > return query select * from a1 > > > return; > > > end; > > > > > > like a2 function > > > > > > regards > > > Pavel Stehule > > > > The last one generates error "set-valued function called in context > > that > > > > cannot accept a set". Why doesn't PERFORM work here? Thanks for help.. > > > > > > > > >
Re: [GENERAL] migrating data from 8.4 to 8.3
A. Kretschmer, 11.02.2010 09:42: In response to Thomas Kellerer : Marc Lustig, 08.02.2010 11:36: Hi, can we safely put the data from /var/lib/postgresql/8.4/main/ of server x to /var/lib/postgresql/8.3/main/ of server y, considering that the new target machine is running 8.3 whereas the old one ran 8.4 ? No, a dump& restore is required (as stated in the release notes) You can also try pg_migrator. It was introduced with 8.4 and will upgrade the data "in-place" (i.e. without a dump and restore) I don't believe that pg_migrator can do a downgrade ... Ooops! I read it the wrong way round (I thought the OP wanted to migrate from 8.3 to 8.4...) Regards Thomas -- 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] migrating data from 8.4 to 8.3
Marc Lustig, 08.02.2010 11:36: Hi, can we safely put the data from /var/lib/postgresql/8.4/main/ of server x to /var/lib/postgresql/8.3/main/ of server y, considering that the new target machine is running 8.3 whereas the old one ran 8.4 ? No, a dump & restore is required (as stated in the release notes) You can also try pg_migrator. It was introduced with 8.4 and will upgrade the data "in-place" (i.e. without a dump and restore) Regards Thomas -- 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] Attribute a value to a record
Thanks a lot for this precision. unfortunately, the cursor doesn't accept "complicated" queries whereas record type stay more powerfull on this aspect. I found a solution and BTW it has considerably simplfy my code! A clue can make you think better! Le mercredi 03 février 2010 à 14:33 +0100, Wappler, Robert a écrit : > On 2010-02-03, Florent THOMAS wrote: > > > Dear laurenz Albe, > > > > Thank you for answering so fast. for me, the variable ventilation_local > > is defined as a record type. So as I wrote on the other mail, I made > > some additionnal test because the doc precise that the syntax above is > > allowed : http://www.postgresql.org/docs/8.4/interactive/plpgsql-stateme > > nts.html#PLPGSQL-STATEMENTS-ASSIGNMENT (last line) > > > > I understood that in the Loop you can change the values of a > > variable! Exactly what I needed. > > but unfortunately all of this seems to be temporary. > > Consequently, the record in the table won't be updated by the > > changes we made on the local variable even if it points to a > > record in the table. > > I forgot the aspect of the cursor that is temporary. > > > > But in all the case, It could be a great improvement to let > > the syntax modify directly the table. > > > > I think I will find another way to do it. with EXECUTE!! > > > > Best regards > > > > Le mercredi 03 février 2010 à 10:05 +0100, Albe Laurenz a écrit : > > > > > > Florent THOMAS wrote: > > > I'm currently running on pg8.4 and I have a trigger > > with a loop : > > > > > > FOR ventilation_local IN (SELECT * FROM XXX) LOOP > > > IF (mytest) THEN > > > ventilation_local.myfield:=mynewvalue; > > > END IF; > > > END LOOP; > > > > > > my problem is that the record doen't accept the new value. > > > I've chek before the value that is not null. > > > Is it a fonctionnality accepted in pg8.4 on record type? > > > > What do you mean by "the record doen't accept the new value"? > > > > Can you show us some SQL statements that exhibit the problem? > > > > Yours, > > Laurenz Albe > > > > > > A record variable is not a physical record. It is a type consisting of some > fields. > > DECLARE > ventilation_local refcursor FOR SELECT * FROM XXX; > BEGIN > OPEN ventilation_local; > MOVE ventilation_local; > WHILE FOUND LOOP > UPDATE XXX SET myfield = mynewvalue WHERE CURRENT OF ventilation_local; > MOVE ventilation_local; > END LOOP; > END; > > This way, ventilation_local is not a record variable, but a cursor, which is > indeed updatable. >
Re: [GENERAL] Attribute a value to a record
Le mercredi 03 février 2010 à 14:38 +0100, Florent THOMAS a écrit : > Thank you, > > As I posted on a french list, whene I start to develop trigger bigger > than 10lines, I always come back on developper habits and forget > database aspects. > So I WILL PRINT IT ON MY WALL : With records everything is temporary. > > Best regards and sorry for the english! > > > > Le mercredi 03 février 2010 à 14:13 +0100, Albe Laurenz a écrit : > > > Florent THOMAS wrote: > > > I understood that in the Loop you can change the values of a > > > variable! Exactly what I needed. > > > but unfortunately all of this seems to be temporary. > > > Consequently, the record in the table won't be updated by the > > > changes we made on the local variable even if it points to a > > > record in the table. > > > I forgot the aspect of the cursor that is temporary. > > > > I get you now - you expected that the underlying table would > > be updated if you change a variable in PL/pgSQL. > > > > I don't think that you need dynamic SQL for that - > > all it takes is an UPDATE statement in your loop, like > > > > UPDATE XXX SET XXX.myfield = mynewvalue > >WHERE XXX.pkey = ventilation_local.pkey; > > > > (using the names from your sample) > > > > Yours, > > Laurenz Albe > >
Re: [GENERAL] Attribute a value to a record
Dear laurenz Albe, Thank you for answering so fast. for me, the variable ventilation_local is defined as a record type. So as I wrote on the other mail, I made some additionnal test because the doc precise that the syntax above is allowed : http://www.postgresql.org/docs/8.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-ASSIGNMENT (last line) I understood that in the Loop you can change the values of a variable! Exactly what I needed. but unfortunately all of this seems to be temporary. Consequently, the record in the table won't be updated by the changes we made on the local variable even if it points to a record in the table. I forgot the aspect of the cursor that is temporary. But in all the case, It could be a great improvement to let the syntax modify directly the table. I think I will find another way to do it. with EXECUTE!! Best regards Le mercredi 03 février 2010 à 10:05 +0100, Albe Laurenz a écrit : > Florent THOMAS wrote: > > I'm currently running on pg8.4 and I have a trigger with a loop : > > > > FOR ventilation_local IN (SELECT * FROM XXX) LOOP > > IF (mytest) THEN > > ventilation_local.myfield:=mynewvalue; > > END IF; > > END LOOP; > > > > my problem is that the record doen't accept the new value. > > I've chek before the value that is not null. > > Is it a fonctionnality accepted in pg8.4 on record type? > > What do you mean by "the record doen't accept the new value"? > > Can you show us some SQL statements that exhibit the problem? > > Yours, > Laurenz Albe
Re: [GENERAL] Attribute a value to a record
Hy I made an additionnal test FOR ventilation_local IN (SELECT * FROM XXX) LOOP IF (mytest) THEN RAISE NOTICE 'ventilation %', ventilation_local; ventilation_local.myfield:=10; RAISE NOTICE 'ventilation %', ventilation_local; END IF; END LOOP; the first notice and the second one are different. Unfortunately, when I get out from the LOOP, the result doesn't seems to be updated in the table Best regards Le mercredi 03 février 2010 à 00:28 +0100, Florent THOMAS a écrit : > Hello, > > I'm currently running on pg8.4 and I have a trigger with a loop : > > FOR ventilation_local IN (SELECT * FROM XXX) LOOP > IF (mytest) THEN > ventilation_local.myfield:=mynewvalue; > END IF; > END LOOP; > > my problem is that the record doen't accept the new value. > I've chek before the value that is not null. > Is it a fonctionnality accepted in pg8.4 on record type? > > Best regards
[GENERAL] Attribute a value to a record
Hello, I'm currently running on pg8.4 and I have a trigger with a loop : FOR ventilation_local IN (SELECT * FROM XXX) LOOP IF (mytest) THEN ventilation_local.myfield:=mynewvalue; END IF; END LOOP; my problem is that the record doen't accept the new value. I've chek before the value that is not null. Is it a fonctionnality accepted in pg8.4 on record type? Best regards
Re: [GENERAL] combine SQL SELECT statements into one
Hi, If I were you, I worked like this. First make a union of those three query Then make a crosstab : http://www.postgresonline.com/journal/index.php?/archives/14-CrossTab-Queries-in-PostgreSQL-using-tablefunc-contrib.html documented here : http://www.postgresql.org/docs/8.4/interactive/tablefunc.html Le dimanche 31 janvier 2010 à 23:36 -0800, Neil Stlyz a écrit : > Good Evening, Good Morning Wherever you are whenever you may be > reading this. > > I am new to this email group and have some good experience with SQL > and PostgreSQL database. > > > I am currently working on a PHP / PostgreSQL project and I came upon > something I could not figure out in SQL. I was wondering if anyone > here could take a look and perhaps offer some guidance or assistance > in helping me write this SQL query. > > Please Consider the following information: > --- > > I have a postgresql table called 'inventory' that includes two fields: > 'model' which is a character varying field and 'modified' which is a > timestamp field. > > So the table inventory looks something like this: > > > model modified > --- > I7782881762010-02-01 08:27:00 > I778288176 2010-01-31 11:23:00 > I778288176 2010-01-29 10:46:00 > JKLM112345 2010-02-01 08:25:00 > JKLM112345 2010-01-31 09:52:00 > JKLM112345 2010-01-28 09:44:00 > X22TUNM7652010-01-17 10:13:00 > V8893456T6 2010-01-01 09:17:00 > > > > Now with the table, fields and data in mind look at the following > three queries: > > > > SELECT COUNT(distinct model) FROM inventory WHERE modified >= > '2010-02-01'; > SELECT COUNT(distinct model) FROM inventory WHERE modified >= > '2010-01-20'; > SELECT COUNT(distinct model) FROM inventory WHERE modified >= > '2010-01-01'; > > > > All three of the above queries work and provide results. However, I > want to combine the three into one SQL Statement that hits the > database one time. How can I do this in one SQL Statement? Is it > possible with sub select? > > > > Here is what result I am looking for from one SELECT statement using > the data example from above: > > > > count1 | count2 | count3 > --- > 2 2 4 > > > Can this be done with ONE SQL STATEMENT? touching the database only > ONE time? > > Please let me know. > > > > Thanx> :) > NEiL > > > > >
Re: [GENERAL] problem with triggers
Thank you for answering so fast, After a good night, I found the solution. There was a problem with a variable that was name as a field name I execute in the query. So everything looks fine now! Thanks a lot Le dimanche 31 janvier 2010 à 16:55 -0700, Scott Marlowe a écrit : > On Sun, Jan 31, 2010 at 4:53 PM, Florent THOMAS wrote: > > Hy everybody, > > > > I have a problem with 2 triggers. > > > > I work on 3 tables : > > table A ==> with one trigger after insert that insert values in table B > > Table B ==> with one trigger after insert that insert values in table C > > Table C > > As I insert values on table A, I have a message that indicates the EXECUTE > > statement as null. > > I wonder if it is because the 2nd insert is sent as the first one is not > > ended. > > In this case, how configure postgresql to accept this second insertion? > > OK, that's a good overview, but it would help if you had a simple > self-contained test case to post so we could reproduce what you're > seeing. >
[GENERAL] problem with triggers
Hy everybody, I have a problem with 2 triggers. I work on 3 tables : table A ==> with one trigger after insert that insert values in table B Table B ==> with one trigger after insert that insert values in table C Table C As I insert values on table A, I have a message that indicates the EXECUTE statement as null. I wonder if it is because the 2nd insert is sent as the first one is not ended. In this case, how configure postgresql to accept this second insertion? Best regards
[GENERAL] dynamic crosstab
Hello everybody, I'm trying to find out how to have a dynamic crosstab as in excel, ireport,etc... As i understand of the manual here : http://docs.postgresqlfr.org/8.4/tablefunc.html I can have multiple columns. Unfortunately, it seems indispensible to name the columns in the AS clause. Am I right or is ther a way to let the query generate the columns and there name without naming them? Best regards Florent THOMAS
Re: [GENERAL] Self-referential records
Ovid wrote on 24.01.2010 14:43: Assuming I have the following table: CREATE TABLE refers ( idSERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, parent_id INTEGER NOT NULL, FOREIGN KEY (parent_id) REFERENCES refers(id) ); I need to insert two records so that "select * from refers" looks like this: =# select * from refers; id | name | parent_id +--+--- 1 | | 1 2 | yyy | 2 The first record can't be inserted because I don't yet know the parent_id. I ususally identify the root record by setting the parent_id to NULL. In my experience creating a cycle in the tree creates a lot of trouble that is hard to come by. Thomas -- 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] PgSQL problem: How to split strings into rows
Kynn Jones wrote on 21.01.2010 19:49: I have a table X with some column K consisting of whitespace-separated words. Is there some SELECT query that will list all these words (for the entire table) so that there's one word per row in the returned table? E.g. If the table X is K - foo bar baz quux frobozz eeny meeny miny moe ...I want the result of this query to be foo bar baz quux frobozz eeny meeny miny moe How can I do this? (I have a slight preference for solutions that will work with version 8.2, but I'm interested in any solution to the problem.) Don't know if this will work with 8.3: select regexp_split_to_table(k, ' ') from x; Thomas -- 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] db cluster location
Scott Frankel wrote on 21.01.2010 18:34: Hi all, Is there a query I can use to find the location of a db cluster? SELECT name, setting FROM pg_settings WHERE category = 'File Locations'; You need to be connected as the superuser (usually postgres) -- 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] Currently connected users...
Dhimant Patel, 21.01.2010 17:40: I'm a beginner Postgres user, and need quick hint from someone. How could I know which users are currently connected to postgres instance? http://www.postgresql.org/docs/current/static/monitoring.html More precisely: http://www.postgresql.org/docs/current/static/monitoring-stats.html#MONITORING-STATS-VIEWS Thomas -- 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] Size of row-metadata?
tmp, 20.01.2010 11:25: http://www.postgresql.org/docs/8.4/static/storage-page-layout.html I fail to find the size of the *row* header on that link. "All table rows are structured in the same way. There is a fixed-size header (occupying 23 bytes on most machines), followed by an optional null bitmap, an optional object ID field, and the user data. The header is detailed in Table 53-4." http://www.postgresql.org/docs/8.4/static/storage-page-layout.html#HEAPTUPLEHEADERDATA-TABLE -- 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] Equivalent of mysql type mediablob in postgres?
Chris Barnes wrote on 18.01.2010 21:05: I would like to move a table that is used to store images from mysql to postgres. The only stumbling I may encounter, may be switching from mysql blob to something in postgres. We store chart images in a mysql medium blob type. How can I store these in postgres? bytea is the datatype you are looking for http://www.postgresql.org/docs/current/static/datatype-binary.html Thomas -- 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] Data Generators
Jamie Kahgee, 17.01.2010 16:26: I'm looking for a data generator. Free would be nice, if possible. Has anyone had good luck w/ anything? maybe point me in a good direction :) Thanks, Jamie K. Have a look at "Benerator" I have been told, that it's quite good (I haven't used it myself though) http://databene.org/databene-benerator Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 8.5 Alpha3 - broken downloadlink
Hi, I was trying to download the windows binaries for 8.5alpha3 but the link on the EnterpriseDB page (http://www.enterprisedb.com/products/pgbindownload.do) only returns an error. When I follow the link http://www.enterprisedb.com/getfile.jsp?fileid=824 then I get a 404 Page with the following message The requested URL /postgresql/postgresql-8.5alpha-windows-binaries.zip was not found on this server. When I manually change "postgresql-8.5alpha-windows-binaries.zip" to "postgresql-8.5alpha3-windows-binaries.zip" in my browser URL, things are working. Regards Thomas -- 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] Migration of db
akp geek wrote on 31.12.2009 21:45: Hi All - We have 2 databases test and prod. Now they are out of sync ( of course they will be to some extent ). But there are some functions in some schemas. we have to sync from prod to test. What I wanted to ask, is there any tool that you recommend for version control. Because we are having tough time tracking the changes Regards Have a look at Liquibase: www.liquibase.org -- 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] Planner Row Estimate with Function
Michael Fork wrote on 29.12.2009 18:08: I have an index scan on a custom function that is returning a wildly incorrect row estimate that is throwing off the rest of the query planning. The result of the function is roughly unique - there are a handful with multiple entries - but the planner is estimating 227,745 rows. I re-ran ANALYZE on the table and the results did not change. Any suggestions on how to get more accurate planner result? You can add the ROWS nnn option to your create statement to give the planner a hint about the number of rows: http://www.postgresql.org/docs/current/static/sql-createfunction.html Check out the /ROWS result_rows/ part. Thomas -- 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] Get Comments on Tables / Functions
Alex - wrote on 27.12.2009 14:57: Hi, i am adding comments to tables and functions with version information and would like to extract that information through a query. Is there and easy way to do that? simple list. table_name, comment SELECT n.nspname as schema_name, c.relname as table_name, a.attname as column_name, dsc.description FROM pg_catalog.pg_namespace n JOIN pg_catalog.pg_class c ON (c.relnamespace = n.oid) JOIN pg_catalog.pg_attribute a ON (a.attrelid = c.oid) LEFT JOIN pg_catalog.pg_description dsc ON (c.oid = dsc.objoid AND a.attnum = dsc.objsubid) WHERE a.attnum > 0 AND NOT a.attisdropped and c.relname = 'account' Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] /var/lib/pgsql/data/pg_xlog/000000010000000000000000,two process access it ?
I guess PID 19045 write data to the log file first when I insert data into table ,but why did writer process also access the log file ? Could some guy tell me some details ? FYI: postgres: writer process's PID is 18848 . postgres test [local] idle's PID is 19045 . [r...@localhost tmp]# lsof /var/lib/pgsql/data/pg_xlog/ 0001 COMMAND PID USER FD TYPE DEVICE SIZENODE NAME postmaste 18848 postgres4u REG 253,0 16777216 1770912 /var/lib/ pgsql/data/pg_xlog/0001 postmaste 19045 postgres 37u REG 253,0 16777216 1770912 /var/lib/ pgsql/data/pg_xlog/0001 [r...@localhost tmp]# ps aux|grep postgre postgres 2429 0.0 0.6 21044 3364 ?S07:32 0:00 /usr/ bin/postmaster -p 5432 -D /var/lib/pgsql/data postgres 2434 0.0 0.1 10824 804 ?S07:32 0:00 postgres: logger process root 9539 0.0 0.2 4908 1232 pts/3S15:31 0:00 su - postgres postgres 9540 0.0 0.2 4528 1480 pts/3S15:31 0:00 -bash postgres 18848 0.0 0.2 21180 1324 ?S17:23 0:00 postgres: writer process postgres 18849 0.0 0.1 11824 780 ?S17:23 0:00 postgres: stats buffer process postgres 18850 0.0 0.1 11056 1000 ?S17:23 0:00 postgres: stats collector process postgres 19029 0.0 0.4 8292 2296 pts/3S+ 17:25 0:00 psql postgres 19045 0.0 0.7 21888 4016 ?S17:25 0:00 postgres: postgres test [local] idle root 19607 0.0 0.1 3912 696 pts/2R+ 17:31 0:00 grep postgre -- 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] postgres: writer process,what does this process actually do?
On Dec 23, 3:44 pm, r...@iol.ie ("Raymond O'Donnell") wrote:
> On 23/12/2009 02:56, Thomas wrote:
>
> > And could you give me some info about postgres internals? Such as
> > ebooks or online articles.
>
> There's quite a bit in the manual:
>
> http://www.postgresql.org/docs/8.4/interactive/internals.html
>
> Ray.
>
> --
> Raymond O'Donnell :: Galway :: Ireland
> r...@iol.ie
>
> --
> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
> To make changes to your
> subscription:http://www.postgresql.org/mailpref/pgsql-general
Tks man.
:)
--
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] postgres: writer process,what does this process actually do?
On 12月22日, 下午11时26分, gryz...@gmail.com (Grzegorz Jaśkiewicz) wrote: > On Tue, Dec 22, 2009 at 10:19 AM, Thomas wrote: > > Does it write data to data files to make buffer "clean"?if it > > does ,but I can not find open files from the result of lsof -p > > PID. > > writer actually takes care about writing pages down, from shared > memory. It is the central point that accesses disk on behalf of all > backends (which are spawned every time you create new connection). > > -- > GJ > > -- > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > To make changes to your > subscription:http://www.postgresql.org/mailpref/pgsql-general hi GJ Tks a lot . And could you give me some info about postgres internals? Such as ebooks or online articles. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] postgres: writer process,what does this process actually do?
Does it write data to data files to make buffer "clean"?if it does ,but I can not find open files from the result of lsof -p PID. -- 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] alter table performance
Antonio Goméz Soto wrote on 17.12.2009 22:26: Hi, I am regularly altering tables, adding columns setting default values etc. This very often takes a very long time and is very disk intensive, and this gets pretty annoying. Things are hampered by the fact that some of our servers run PG 7.3 Suppose I have a table and I want to add a non NULL column with a default value. What I normally do is: alter table person add column address varchar(64); update person set address = '' where address IS NULL; alter table person alter column address set not NULL; alter table person alter column address set default ''; When the table contains millions of records this takes forever. Am I doing something wrong? Do other people have the same problems? What's wrong with: alter table person add column address varchar(64) not null default ''; Although I don't know if such a pre-historic version like 7.3 would support that. It works for 8.4 and I believe this was working with 8.3 and 8.2 as well Thomas -- 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] Justifying a PG over MySQL approach to a project
Greg Smith wrote on 16.12.2009 22:44: You've probably already found http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL:_Comparing_Reliability_and_Speed_in_2007 which was my long treatment of this topic (and overdue for an update). There is an update: http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL_2009 -- 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] Justifying a PG over MySQL approach to a project
Gauthier, Dave wrote on 16.12.2009 22:02:
Hi Everyone:
Tomorrow, I will need to present to a group of managers (who know
nothing about DBs) why I chose to use PG over MySQL in a project,
What kind of project is that?
If you are developing something that you are selling to other people, MySQL's
GPL license will force you to buy a commercial license in order to distribute
your application unless it is GPL as well.
You don't have such constraints with PostgreSQL
There are some features that you might want to mention as well
- ANSI standard windowing functions
- ANSI standard common table expressions
- XML support (not necessarily important, but can potentially be nice)
Something that drives me nuts with MySQL: it behaves differently depending on
the configuration settings, different defaults with different OS (regarding
case sensitivity for example) or the default storage engine selected (thinking
about ANSI mode, strict tables, the ability to store invalid dates, insert 0
instead of null and all those little things...).
That makes the QA for a project much more complicated, especially if you don't
have control over the installation at the customer's site
PostgreSQL behaves the same ("syntactically"), regardless on where or how it
was installed
Thomas
--
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] Too many postgres.exe
a.bhattacha...@sungard.com, 15.12.2009 10:51: Hi All, I have my application UI in Java and which is communicating with Postgresql database. However whenever my application is running I could see there are too many postgres.exe are created even though the application is not doing anything in database. In general the observation is that there are too many progress.exe processes get created every time I run my application and it postgres.exe eats up the maximum memory. Define "too many". Each connection that you open will start up a new postgres.exe As Craig has already pointed out you have not specified enough details, so people can only guess what is going wrong (if at all) My best guess is: - you are simply not closing your connections when you are don - you have configured a connection pool that creates a high number of initial connections Thomas -- 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] Counts and percentages and such
jackassplus wrote on 08.12.2009 22:21: What does ::numeric signify? I'm using the jdbc driver from http://jdbc.postgresql.org/ in SQuirreL and it asks me for the value of :numeric. As Scott has pointed out this is a typecast. If Squirrel mistakes that for a parameter, it's clearly a bug in Squirrel. But maybe that prompting for parameters can be turned off somewhere (I don't use Squirrel, so I cannot tell) Thomas -- 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] Help with starting portable version of postgresql
John R Pierce wrote on 06.12.2009 00:01: I downloaded the portable version of Postegresql from http://greg.webhop.net/postgresql_portable It works but the problem is that to start the server you must load it from the gui. Instead since I want to start the server from my app, I need to understand how to load it manually. I tried starting postgres.exe but it's saying: Execution of PostgreSQL by a user with administrative permissions is not permitted. You should use pg_ctl instead. That will take care of dropping any administrative rights the current user might have. I'm using a set of batch files to create a "portable" postgres, and I use pg_ctl for that purpose even with administrator account. -- 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] date_trunct() and start of week
Thomas Markus, 27.11.2009 09:41: Hi, not all to zero : "that are less significant than the selected one set to zero (or one, for day and month)" Sorry, I missed the "or one" part. see http://www.postgresql.org/docs/8.1/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT dow: "The day of the week (0 - 6; Sunday is 0)" So essentially it *is* always returning Monday independently of any setting. Thanks for your help Regards Thomas -- 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] date_trunct() and start of week
Hi,
not all to zero : "that are less significant than the selected one set
to zero (or one, for day and month)"
so
select extract('dow' from date_trunc('week', current_date))
returns always 1 (i think accordingly to ISO-8601)
see
http://www.postgresql.org/docs/8.1/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
dow:
"The day of the week (0 - 6; Sunday is 0)"
regards
Thomas
Thomas Kellerer schrieb:
>
> Hmm, I don't see that in there.
> It just states that the field will be set to "zero". But does zero
> refer to a Monday or a Sunday?
> Regards
> Thomas
>
>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
