Re: [GENERAL] Using psql -f to load a UTF8 file
> I strongly disagree. The BOM provides a useful and standard way to > differentiate UTF-8 encoded text files from the random pile of encodings that > any given file could be. I agree on the concept, but I'm having a bit of trouble understanding how a "Byte Order Marker" is useful to an 8-bit encoding. It's not possible to get the byte order wrong in UTF-8, is it? Yes, being able to mark data as being encoded as UTF-8 is useful, but is a BOM the right tool? Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. -- 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_upgrade: out of memory
Tom, As its AIX I don't have top but using topas and comparing it to other processes running a successful pg_dumpall doesn't get very large at all. The database only has around 1000 tables and no more than anpther 500 view, triggers, functions, etc. so its not a big database. There are no BLOBs or anything even slightly non-mainstream. The output file created by a successful pg_dumpall is 11MB for all the databases running on this postgres installation. The other databases are much larger than the first one which is where the failure occurred. The machine is a large AIX box with 64GB of memory and the upgrade was run with nothing else running on the machine so I find it hard to believe that it is genuinely out of memory. The whole of the first database could fit in real memory as its only 28GB. Matthew -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: 20 September 2012 15:48 To: Carrington, Matthew (Produban) Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] pg_upgrade: out of memory "Carrington, Matthew (Produban)" writes: > I have attempted to upgrade my Postgres installation this morning from 9.0.1 > to 9.2.0 and it failed with an out of memory problem using pg_dumpall to dump > the first database. Hm. I'm not aware of any reason for 9.2 pg_dump to take hugely more memory than 9.0. How big is the database (how many objects)? When you run 9.0 pg_dump against it, how big does the process get? (Watching it in "top" is probably a close enough answer here.) regards, tom lane Emails aren't always secure, and they may be intercepted or changed after they've been sent. Produban doesn't accept liability if this happens. If you think someone may have interfered with this email, please get in touch with the sender another way. This message and any documents attached to it do not create or change any contract unless otherwise specifically stated. Any views or opinions contained in this message are solely those of the author, and do not necessarily represent those of Produban, unless otherwise specifically stated and the sender is authorised to do so. Produban doesn't accept responsibility for damage caused by any viruses contained in this email or its attachments. Emails may be monitored. If you've received this email by mistake, please let the sender know at once that it's gone to the wrong person and then destroy it without copying, using, or telling anyone about its contents. Produban Servicios Informaticos Generales, S.L. (UK Branch). Registered office: Shenley Wood House, Chalkdell Drive, Shenley Wood, Milton Keynes MK5 6LA. Branch registration number BR 008486. Ref:[PDB#014] -- 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] Using psql -f to load a UTF8 file
On Fri, Sep 21, 2012 at 09:21:36AM +0800, Craig Ringer wrote: > On 09/20/2012 11:44 PM, Leif Biberg Kristensen wrote: > > Torsdag 20. september 2012 16.56.16 skrev Alan Millington : > >>psql". But how am I supposed to remove the byte order mark from a UTF8 > >>file? I thought that the whole point of the byte order mark was to tell > >>programs what the file encoding is. Other programs, such as Python, rely > >>on this. > > > >http://en.wikipedia.org/wiki/Byte_order_mark > > > >While the Byte Order Mark is important for UTF-16, it's totally irrelevant to > >the UTF-8 encoding. > > I strongly disagree. The BOM provides a useful and standard way to > differentiate UTF-8 encoded text files from the random pile of > encodings that any given file could be. Use of the BOM in UTF-8 causes a host of display and interoperability problems, and is considered by many to be a broken practice. It's also pointless since there are no byte ordering issues with UTF-8. Best to not use it at all. In any case, the BOM byte sequence does not unambiguously identify UTF-8; it's equally valid for 8-bit charsets, so an external means of specifying the encoding is preferable and more robust. Regards, Roger -- .''`. Roger Leigh : :' : Debian GNU/Linuxhttp://people.debian.org/~rleigh/ `. `' schroot and sbuild http://alioth.debian.org/projects/buildd-tools `-GPG Public Key F33D 281D 470A B443 6756 147C 07B3 C8BC 4083 E800 -- 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 csvlog logs contexts without leading tab?
hubert depesz lubaczewski wrote: > When I enabled normal stderr logging, with absurdly full > log_line_prefix, I got this: > a[psql] u[depesz] d[depesz] r[[local]] h[[local]] p[15444] t[2012-09-13 > 21:49:37 CEST] m[2012-09-13 > 21:49:37.840 CEST] i[SELECT] e[0] c[505238d0.3c54] l[4] s[2012-09-13 > 21:49:36 CEST] v[2/2] x[0] > CONTEXT: SQL statement "SELECT b()" > PL/pgSQL function a() line 1 at PERFORM > same function call, with syslog: > Sep 13 21:53:31 h3po4 postgres[16156]: [4-2] a[psql] u[depesz] d[depesz] > r[[local]] h[[local]] > p[16156] t[2012-09-13 21:53:31 CEST] m[2012-09-13 21:53:31.852 CEST] > i[SELECT] e[0] > c[505239bb.3f1c] l[4] s[2012-09-13 21:53:31 CEST] v[2/4] x[0] CONTEXT: SQL > statement "SELECT b()" > Sep 13 21:53:31 h3po4 postgres[16156]: [4-3] #011PL/pgSQL function a() line 1 > at PERFORM > Please note that the 3rd line in both examples has leading tab - either > literal tab in case of stderr log, or #011 in case of syslog. > > But with csvlog, I got something different: > 2012-09-13 21:51:12.642 > CEST,"depesz","depesz",15673,"[local]",5052392f.3d39,3,"SELECT",2012-09-13 > 21:51:11 CEST,2/2,0,LOG,0,"[logged line]","SQL statement ""SELECT > b()"" > PL/pgSQL function a() line 1 at PERFORM","select a();",,,"psql" > There is no leading tab on the 2nd line. Why? Is it intentional, or just > an omission? I think that the current behaviour makes sense. The actual context message did not have a tab in it, just a line break. In CSV you can leave it like that, because the quoting will make it clear that the line break is not the start of a new log entry. In the other two cases a tab is added to distinguish a line break (or new message for syslog) that starts a new log entry from a line break that is part of a log entry. This makes automatic parsing of log files possible: if a line starts with a tab, it is a continuation line. The tab itself is not part of the message. Yours, Laurenz Albe -- 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 csvlog logs contexts without leading tab?
On Fri, Sep 21, 2012 at 11:54:27AM +0200, Albe Laurenz wrote: > This makes automatic parsing of log files possible: > if a line starts with a tab, it is a continuation line. > The tab itself is not part of the message. why the tab isn't appended for other multi-line messages? like queries in LOG: duration: statement: lines? Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.com/ -- 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] application for postgres Log
Arvind Singh wrote:
> Our CSV Log contains lot of statements like the following THREE lines.
They appear exactly one after
> the other.
>
> And they number in thousands for a Session (more than ten thousand)
>
>
> 2011-11-11 12:41:31.484
IST,"agent1","pem",524,"localhost:2141",4ebccaa2.20c,754,"idle",2011-11-
11
> 12:41:30 IST,2/308,0,LOG,0,"statement: INSERT INTO
pemdata.settings (""name"", ""setting"",
> ""unit"", ""server_id"") VALUES ('xmlbinary', 'base64', NULL,
'1')""exec_simple_query,
> .\src\backend\tcop\postgres.c:900",""
You get that if log_statement='all'
> 2011-11-11 12:41:31.484
IST,"agent1","pem",524,"localhost:2141",4ebccaa2.20c,755,"INSERT",2011-1
1-11
> 12:41:30 IST,2/0,0,LOG,0,"duration: 0.000
ms""exec_simple_query,
> .\src\backend\tcop\postgres.c:1128",""
You get that if log_duration=on or log_min_duration_statement=0
> 2011-11-11 12:41:31.484
IST,"agent1","pem",524,"localhost:2141",4ebccaa2.20c,756,"INSERT",2011-1
1-11
> 12:41:30 IST,2/0,0,LOG,0,"QUERY STATISTICS","! system usage stats:
> ! 0.00 elapsed 0.00 user 0.00 system sec
> ! [0.25 user 0.156250 sys total]","INSERT INTO
pemdata.settings (""name"", ""setting"",
> ""unit"", ""server_id"") VALUES ('xmlbinary', 'base64', NULL,
'1')",,"ShowUsage,
> .\src\backend\tcop\postgres.c:4305",""
You get that if log_statement_stats=on
> Is there anything that we enabled, because they dont appear after that
particular session.
Yes, as indicated above, during this session these options must have
been enabled.
I wouldn't worry about it.
Yours,
Laurenz Albe
--
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 csvlog logs contexts without leading tab?
depesz wrote: >> This makes automatic parsing of log files possible: >> if a line starts with a tab, it is a continuation line. >> The tab itself is not part of the message. > why the tab isn't appended for other multi-line messages? like queries > in LOG: duration: statement: lines? I'm not sure what you mean... I tried: test=# SET log_min_duration_statement=0; test=# SELECT 42, test-# 'newline'; And got this in the log: LOG: duration: 0.407 ms statement: SELECT 42, 'newline'; So there is a tab prepended. Yours, Laurenz Albe -- 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 Log MultiLine Records
we have been working on a CSV Log parser application in csharp. we recently discovered that certain log entries or records can span across multiple lines. in the sense, that the same activity has more detail in subsequent lines. For ex, a select,insert query has A query entry A Duration entry A Stats entry and that is why there was a serious coding mistake in counting select entries, because the same select statement is displayed in Query column twice and once in Detail column So our application counted them as three queries. My query is, has any of us come across more such examples or :: is there a link or page explaining type of Log entries :: Number of log statements a logentry type occupy. :: or other finer details that we may overlook it will be helpfull as we wish to release this application for all pg users at no price. thanks arvind --What charm in words, a charm no words could give? O dying words, can Music make you live Far--far--away? - (Alfred, Lord Tennyson in Far-Far-Away)
Re: [GENERAL] PG Log MultiLine Records
Arvind Singh wrote: > we have been working on a CSV Log parser application in csharp. > > we recently discovered that certain log entries or records can span across multiple lines. > > in the sense, that the same activity has more detail in subsequent lines. Not really, they are different log entries that belong to one SQL statement. For example, if you have log_statement='all' and log_min_duration_statement=3s, then some statements will produce one log entry and others will produce two. > For ex, a select,insert query has > A query entry > A Duration entry > A Stats entry > > and that is why there was a serious coding mistake in counting select entries, because the same select > statement > is displayed in Query column twice and once in Detail column > > So our application counted them as three queries. > > My query is, has any of us come across more such examples or > :: is there a link or page explaining type of Log entries Not really, the details are in the code. > :: Number of log statements a logentry type occupy. I don't quite understand that. > :: or other finer details that we may overlook > > it will be helpfull as we wish to release this application for all pg users at no price. It is hard to find a solution that works no matter how logging is configured. It would be helpful to know what your program is trying to achieve. What I did for pgreplay is a) force certain configuration settings as prerequisite (like log_statement='all'). b) search for log lines with severity "LOG" where the query entry begins with "statement: ". That should filter out the "duration" and "stats" lines you mention above. Yours, Laurenz Albe -- 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] Expression to construct a anonymous record with named columns?
On 21 September 2012 07:50, Alban Hertroys wrote: > On 20 Sep 2012, at 20:36, Benedikt Grundmann wrote: > > > So named anonymous records / row types seem to be strangely second > class. Can somebody clarify the restrictions and rationale or even better > show a way to do the equivalent of (made up syntax ahead): > > > > select row(1 as a, 2 as b); > > select * from (values (1, 2, 3)) a (a, b, c); > > Thank you very much. This is very interesting. However this again seems to be strangely limited, because I can neither extract a column from row that was constructed this way in a scalar position nor expand it: proddb_testing=# select (select x from (values (1, 2, 3)) x (a, b, c)); ?column? -- (1,2,3) (1 row) proddb_testing=# select (select x from (values (1, 2, 3)) x (a, b, c)).*; ERROR: syntax error at or near "." LINE 1: select (select x from (values (1, 2, 3)) x (a, b, c)).*; ^ proddb_testing=# select (select x from (values (1, 2, 3)) x (a, b, c)).a; ERROR: syntax error at or near "." LINE 1: select (select x from (values (1, 2, 3)) x (a, b, c)).a; Any more insights? Thanks, bene
Re: [GENERAL] Why csvlog logs contexts without leading tab?
On Fri, Sep 21, 2012 at 12:25:09PM +0200, Albe Laurenz wrote: > So there is a tab prepended. Sorry, you're right. Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.com/ -- 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] Expression to construct a anonymous record with named columns?
On Fri, Sep 21, 2012 at 4:18 AM, Benedikt Grundmann wrote: > > On 21 September 2012 07:50, Alban Hertroys wrote: >> >> On 20 Sep 2012, at 20:36, Benedikt Grundmann wrote: >> >> > So named anonymous records / row types seem to be strangely second >> > class. Can somebody clarify the restrictions and rationale or even better >> > show a way to do the equivalent of (made up syntax ahead): >> > >> > select row(1 as a, 2 as b); >> >> select * from (values (1, 2, 3)) a (a, b, c); >> > Thank you very much. This is very interesting. However this again seems > to be strangely limited, because I can neither extract a column from row > that was constructed this way in a scalar position nor expand it: > > proddb_testing=# select (select x from (values (1, 2, 3)) x (a, b, c)); > ?column? > -- > (1,2,3) > (1 row) select * from (values (1, 2, 3)) x (a, b, c); select x.* from (values (1, 2, 3)) x (a, b, c); :-) merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 9.1 vs 8.4 performance
Hello, I have two postgresql servers 9.1.5 and 8.4.8 running on ubuntu machine, both are fresh installs and both has the same configuration files and databases. I am running queries sequentially on each machine using a database dumped from a life server , and 9.1 server is much slower than 8.4. I am wondering what might be the cause for this behaviour? Does 9.1 requires more attention on setting some parameters .. ? Regards
Re: [GENERAL] Using psql -f to load a UTF8 file
On 09/20/2012 10:27 AM, Alan Millington wrote:
Thank you for the link. I am using Notepad, which inserts the byte order
mark. Following the links a bit further, I gather that the version of
Notepad that I am using may not identify a UTF8 file correctly if the
byte order mark is omitted. Also, as I mentioned, Python makes use of
it. (From the Python documentation on Encoding declarations: "If the
first bytes of the file are the UTF-8 byte-order mark ('\xef\xbb\xbf'),
the declared file encoding is UTF-8 (this is supported, among others, by
Microsoft’s Notepad).")
The conclusion seems to be that I must use one editor for Python, and
another for Postgres.
The problem is Notepad has a limited view of the text world. If you use
it only in the Microsoft universe and for simple tasks it is sufficient.
The issues arise when you try to work with files outside that scope. For
long term peace of mind you will better served by more capable text
editors(see other posts for suggestions). As to the Python
documentation, it goes on to say:
http://docs.python.org/tutorial/interpreter.html#source-code-encoding
"Notice that this signature is not understood in older Python releases
(2.2 and earlier), and also not understood by the operating system for
script files with #! lines (only used on Unix systems)."
From the Zen of Python:
"Explicit is better than implicit"
Following that the preferred way of declaring an encoding would be:
# -*- coding: utf-8 -*-
See the above link for more information.
--
Adrian Klaver
adrian.kla...@gmail.com
--
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_upgrade: out of memory
"Carrington, Matthew (Produban)" writes: > As its AIX I don't have top but using topas and comparing it to other > processes running a successful pg_dumpall doesn't get very large at all. Hmm. Best guess at this point is that there's some specific DDL in your database that confuses some recent change in pg_dump. Can you try to narrow down what it is? Try taking a pg_dump -s (schema only) from the cahoot_monitoring database, load parts of that into a scratch database, see if 9.2 pg_dump fails on that. Alternatively, if there's nothing terribly sensitive in your DDL, maybe you could send me the pg_dump -s output off-list? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [ADMIN] Windows Services and Postgresql 9.1.3
Yes, (I think) is a Bug, sometimes in some circumstances pg_ctl go down but postgres server still running. You can easily reproduce. Go to "task manager", kill the process "pg_ctl" (simulate some kind of crash or something else). Now go to services management and try to start again. Start command will through an error because pg_ctl can't start database because is already started. Due to this bug I always use the pg_ctl command directly and not the windows service. Running pg_ctl command from command prompt show that postgres server is running, but windows service show stopped and is not possible to start again from windows service UI The windows service also can remain in an inconsistent state when the pg_ctl can't stop the postgres server. I think that pg_ctl uses stop "fast" so, if for some reason postgres don't response to stop fast, the service in windows service UI appear as stopping for long time and finally after some kind of timeout it show as not started. Obviously, if it appear as not started you want to start, but you can't. And maybe some inexperienced user get crazy, "database is stopped and can't start" but really DB is started. What the inexperienced do is the only thing he can do... Restart the server, when he restart the postgres server receive a KILL message and then everything get worse I live this situation time ago and obviously learn :) Regards -Mensaje original- De: pgsql-admin-ow...@postgresql.org [mailto:pgsql-admin-ow...@postgresql.org] En nombre de Craig Ringer Enviado el: lunes, 17 de septiembre de 2012 12:43 a.m. Para: Vincent Dautremont CC: pgsql-ad...@postgresql.org Asunto: Re: [ADMIN] Windows Services and Postgresql 9.1.3 On 09/12/2012 04:48 AM, Vincent Dautremont wrote: > For example, after the server had rebooted, I would stop the service > from services.msc Windows then reports it as stopped but I can still > see that the database is running, and can use it. Check in the process monitor (or, better, with Process Explorer from Sysinternals) to see what user the `postgres.exe` processes are running as. > When I would start the service again, I would end up with a timeout > waiting for the service to be started. When in fact the database was > always running. > > Anyone had that bug/problem before ? Nope. Sounds completely crazy. More info needed, though. -- Craig Ringer -- Sent via pgsql-admin mailing list (pgsql-ad...@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin -- 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] Expression to construct a anonymous record with named columns?
> select * from (values (1, 2, 3)) x (a, b, c); > select x.* from (values (1, 2, 3)) x (a, b, c); And more fun with values: select a, b, c from (values (1, 2, 3), (4, 5, 6), (7, 8, 9)) x (a, b, c); -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Another PostgreSQL Diff Tool 2.4 released!
Another PostgreSQL Diff Tool 2.4 released - Another PostgreSQL Diff Tool (also known as apgdiff) is free PostgreSQL database schema diff tool. Project homepage: http://apgdiff.startnet.biz/ Release information --- This release brings several new features and bug fixes. Please also note that apgdiff source repository has been moved to GitHub to be more developer friendly and encourage more apgdiff users to contribute their patches. GitHub link is https://github.com/fordfrog/apgdiff Release Details --- New Features * Added support for ALTER SEQUENCE OWNED BY (patch by Mikhail Petrov). * Added support for CREATE TRIGGER ... UPDATE OF column. * Added switch --ignore-slony-triggers which causes that Slony triggers _slony_logtrigger and _slony_denyaccess are completely ignored during parsing and diffing. Fixes * Fixed issue with comments not being added on newly created columns. * Improved logging errors when parsing strings. * Added support for IF NOT EXISTS (patch by Felipe Sateler). * Fixed NPE when search_path contains quoted schema (patch by Steven Elliott). * Fixed dropping of default values when --add-defaults is specified (patch by Jim Mlodgenski). * Fixed all bugs related to incorrect parsing of end of statement, most often resulting in StringIndexOutOfBoundException. * Fixed CREATE TABLE statement output when table contains no column. Full changelog can be found at http://apgdiff.startnet.biz/changelog.php Download Binary distribution and sources: http://apgdiff.startnet.biz/download.php Sources from Git repository: https://github.com/fordfrog/apgdiff Other Information - If you have issues with running apgdiff, you may find answer for your issue at FAQ page: http://apgdiff.startnet.biz/faq.php If you still have issues with running apgdiff, you can email me at apgd...@startnet.biz If you find any issue with apgdiff or you miss a feature, you can file an issue at https://github.com/fordfrog/apgdiff/issues Best regards. Miroslav Šulc Another PostgreSQL Diff Tool Developer smime.p7s Description: Elektronicky podpis S/MIME
Re: [GENERAL] Why do I have holes in my pages?
On Thu, Sep 20, 2012 at 1:46 PM, Victor Yegorov wrote: > Take a look at this part of the documentation: > http://www.postgresql.org/docs/current/interactive/routine-vacuuming.html#VACUUM-FOR-SPACE-RECOVERY > > The “missing” entries belong to the tuples that you have DELETEd/UPDATEd and > that are no longer visible > to your current session, but still might be for the others, that started > some time ago. When tuples are no longer > needed, VACUUM will “release” the slots by adding them into the > FreeSpaceMap. My understanding is (which is not very deep on this topic) is that it is a lot more complicated than that. To start with, it can be as you say where the ctid and its tuple are interesting to someone, but not to you. But eventually the tuple is not interesting to anyone, and its space can be reused. But the ctid is still needed (to inform stragglers that it's corresponding tuple is not interesting, and in fact no longer exists, so move on, nothing to see). Then eventually even the ctid itself is not needed anymore even for that purpose. At that point the ctid can be re-used, but only if someone actually wants a "new" ctid on that page. An ordinary vacuum will not close up the gaps on un-used ctids. Only a vaccum full will do that. The space used by these ctid gaps is not large, and as the OP discovered, his wasted space was in fact happening outside of the database itself. Cheers, Jeff -- 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] 9.1 vs 8.4 performance
On Fri, Sep 21, 2012 at 7:32 AM, salah jubeh wrote: > Hello, > > I have two postgresql servers 9.1.5 and 8.4.8 running on ubuntu machine, > both are fresh installs and both has the same configuration files and > databases. > > I am running queries sequentially on each machine using a database dumped > from a life server , and 9.1 server is much slower than 8.4. > > I am wondering what might be the cause for this behaviour? Does 9.1 > requires more attention on setting some parameters .. ? At this level of detail, it is impossible to give an answer. Some people will find 9.1 faster, some slower. I suspect more will find it faster than slower. Can you provide an easily reproducible test case? Cheers, Jeff -- 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 do I have holes in my pages?
2012/9/21 Jeff Janes > To start with, it can be as you say where the ctid and its tuple are > interesting to someone, but not to you. But eventually the tuple is > not interesting to anyone, and its space can be reused. But the ctid > is still needed (to inform stragglers that it's corresponding tuple is > not interesting, and in fact no longer exists, so move on, nothing to > see). Then eventually even the ctid itself is not needed anymore even > for that purpose. > > At that point the ctid can be re-used, but only if someone actually > wants a "new" ctid on that page. An ordinary vacuum will not close up > the gaps on un-used ctids. Only a vaccum full will do that. > In the “Routine Vacuuming” section of the documentation I read that: «The standard form of VACUUM removes dead row versions in tables and indexes and marks the space available for future reuse.» and «In contrast, VACUUM FULL actively compacts tables by writing a complete new version of the table file with no dead space.» What I wanted to say in the previous post was exactly this: vacuum will mark the space (or gap) as free, while no space will be “returned” to the OS, except if free pages are at the very end of the data file. I haven't mentioned vacuum full at all. It seems that this also matches your explanation, correct me if I'm wrong. -- Victor Y. Yegorov
Re: [GENERAL] 9.1 vs 8.4 performance
On Fri, Sep 21, 2012 at 8:32 AM, salah jubeh wrote: > Hello, > > I have two postgresql servers 9.1.5 and 8.4.8 running on ubuntu machine, > both are fresh installs and both has the same configuration files and > databases. > > I am running queries sequentially on each machine using a database dumped > from a life server , and 9.1 server is much slower than 8.4. So how different or similar are these two machines? -- 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] Expression to construct a anonymous record with named columns?
On Fri, Sep 21, 2012 at 12:39 PM, Benedikt Grundmann wrote: > On 21 September 2012 14:04, Merlin Moncure wrote: >> >> On Fri, Sep 21, 2012 at 4:18 AM, Benedikt Grundmann >> wrote: >> > >> > On 21 September 2012 07:50, Alban Hertroys wrote: >> >> >> >> On 20 Sep 2012, at 20:36, Benedikt Grundmann wrote: >> >> >> >> > So named anonymous records / row types seem to be strangely second >> >> > class. Can somebody clarify the restrictions and rationale or even >> >> > better >> >> > show a way to do the equivalent of (made up syntax ahead): >> >> > >> >> > select row(1 as a, 2 as b); >> >> >> >> select * from (values (1, 2, 3)) a (a, b, c); >> >> >> > Thank you very much. This is very interesting. However this again seems >> > to be strangely limited, because I can neither extract a column from row >> > that was constructed this way in a scalar position nor expand it: >> > >> > proddb_testing=# select (select x from (values (1, 2, 3)) x (a, b, c)); >> > ?column? >> > -- >> > (1,2,3) >> > (1 row) >> >> select * from (values (1, 2, 3)) x (a, b, c); >> select x.* from (values (1, 2, 3)) x (a, b, c); >> >> :-) >> > I guess I'm not expressing very well what I mean. What you wrote works just > fine but it only works by introducing a from clause. Where as a row > expression can be used in scalar position without the need for a from > clause: > > select row(1, 2); solutions i use: *) cast to defined type postgres=# create type foo as (a int, b int); postgres=# select (row(1,2)::foo).*; a | b ---+--- 1 | 2 *) hstore: postgres=# select avals(hstore(row(1,2))); *) textual manipulation (most fragile) select * from regexp_split_to_array(row(1,2)::text, ','); merlin -- 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] 9.1 vs 8.4 performance
If its not too much work, swap them around and retest to see if its really the DB/version or the machine. -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Scott Marlowe Sent: Friday, September 21, 2012 3:01 PM To: salah jubeh Cc: pgsql Subject: Re: [GENERAL] 9.1 vs 8.4 performance On Fri, Sep 21, 2012 at 8:32 AM, salah jubeh wrote: > Hello, > > I have two postgresql servers 9.1.5 and 8.4.8 running on ubuntu > machine, both are fresh installs and both has the same configuration > files and databases. > > I am running queries sequentially on each machine using a database > dumped from a life server , and 9.1 server is much slower than 8.4. So how different or similar are these two machines? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 9.1 vs 8.4 performance
One thing I sometimes forget to do after loading up an empty DB with data is to run "analyze". I usually "remember" once I see poor query performance, run the analyze, and its fixed. -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Scott Marlowe Sent: Friday, September 21, 2012 3:01 PM To: salah jubeh Cc: pgsql Subject: Re: [GENERAL] 9.1 vs 8.4 performance On Fri, Sep 21, 2012 at 8:32 AM, salah jubeh wrote: > Hello, > > I have two postgresql servers 9.1.5 and 8.4.8 running on ubuntu > machine, both are fresh installs and both has the same configuration > files and databases. > > I am running queries sequentially on each machine using a database > dumped from a life server , and 9.1 server is much slower than 8.4. So how different or similar are these two machines? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Expression to construct a anonymous record with named columns?
On Fri, Sep 21, 2012 at 2:23 PM, Merlin Moncure wrote: > On Fri, Sep 21, 2012 at 12:39 PM, Benedikt Grundmann > wrote: >> On 21 September 2012 14:04, Merlin Moncure wrote: >>> >>> On Fri, Sep 21, 2012 at 4:18 AM, Benedikt Grundmann >>> wrote: >>> > >>> > On 21 September 2012 07:50, Alban Hertroys wrote: >>> >> >>> >> On 20 Sep 2012, at 20:36, Benedikt Grundmann wrote: >>> >> >>> >> > So named anonymous records / row types seem to be strangely second >>> >> > class. Can somebody clarify the restrictions and rationale or even >>> >> > better >>> >> > show a way to do the equivalent of (made up syntax ahead): >>> >> > >>> >> > select row(1 as a, 2 as b); >>> >> >>> >> select * from (values (1, 2, 3)) a (a, b, c); >>> >> >>> > Thank you very much. This is very interesting. However this again seems >>> > to be strangely limited, because I can neither extract a column from row >>> > that was constructed this way in a scalar position nor expand it: >>> > >>> > proddb_testing=# select (select x from (values (1, 2, 3)) x (a, b, c)); >>> > ?column? >>> > -- >>> > (1,2,3) >>> > (1 row) >>> >>> select * from (values (1, 2, 3)) x (a, b, c); >>> select x.* from (values (1, 2, 3)) x (a, b, c); >>> >>> :-) >>> >> I guess I'm not expressing very well what I mean. What you wrote works just >> fine but it only works by introducing a from clause. Where as a row >> expression can be used in scalar position without the need for a from >> clause: >> >> select row(1, 2); > > solutions i use: > *) cast to defined type > postgres=# create type foo as (a int, b int); > postgres=# select (row(1,2)::foo).*; > a | b > ---+--- > 1 | 2 > > *) hstore: > postgres=# select avals(hstore(row(1,2))); > > *) textual manipulation (most fragile) > select * from regexp_split_to_array(row(1,2)::text, ','); > > merlin also, for recent postgres (9.2, or 9.1 with the extension), you can use the row_to_json function and deal with the output that way (either on the client side, or with the up'n'coming pl/v8). merlin -- 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 do I have holes in my pages?
On Fri, Sep 21, 2012 at 11:41 AM, Victor Yegorov wrote: > > It seems that this also matches your explanation, correct me if I'm wrong. I think that the explanations do generally match. But, just because you observe that the ctid space has not been reused (like the OP did), does not mean that the tuple-space has not been reused. My understanding is that tuple space is easier to reuse than ctid space is. Sometimes that distinction is important, and sometimes it is not. In general, doing "select ctid..." is a poor way of figuring out where the space in your database is going. Cheers, Jeff -- 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 do I have holes in my pages?
On Fri, Sep 21, 2012 at 12:45 PM, Jeff Janes wrote: > On Fri, Sep 21, 2012 at 11:41 AM, Victor Yegorov wrote: >> >> It seems that this also matches your explanation, correct me if I'm wrong. > > In general, doing "select ctid..." is a poor way of figuring out > where the space in your database is going. Noted, thank you. Aleksey -- 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] C locale versus en_US.UTF8. (Was: String comparision in PostgreSQL)
On Sun, Sep 2, 2012 at 10:08 PM, Peter Eisentraut wrote: > On Wed, 2012-08-29 at 10:31 -0700, Aleksey Tsalolikhin wrote: >> What is the difference between C and en_US.UTF8, please? > > There are many differences, but here is a simple one: > > $ (echo a; echo A; echo b; echo B) | LC_ALL=C sort > ... > $ (echo a; echo A; echo b; echo B) | LC_ALL=en_US.utf8 sort > ... Beautifully illustrated, and in a language I readily understand as a UNIX sys admin. Thank you, Peter! Thank you, Bruce, Merlin, and everybody else who replied. I love the flexibility 9.1 allows to have a default sort locale with ability to alter locale per-query if needed. It gives us the confidence to move forward with switching to C for sorting -- right now we only need to sort on US English data, so this is a perfect fit. When we internationalize, 9.1 allows us to switch back to UTF8-based sorting if needed. I love it! Thank you! Aleksey -- 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 do I have holes in my pages?
On Fri, Sep 21, 2012 at 11:30 AM, Jeff Janes wrote: > At that point the ctid can be re-used, but only if someone actually > wants a "new" ctid on that page. An ordinary vacuum will not close up > the gaps on un-used ctids. Only a vaccum full will do that. There are a couple of ways to do that except the vacuum full that locks the table exclusively. 1. pg_reorg can re-organize tables on a postgres database without locks. However it requires twice the space of the table size and might lead to IO spikes. 2. pgcompactor a tables and indexes bloat reducing tool, without locking also. It is slower than pg_reorg but does its job more gently. > > The space used by these ctid gaps is not large, and as the OP > discovered, his wasted space was in fact happening outside of the > database itself. > > Cheers, > > Jeff > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sergey Konoplev a database and software architect http://www.linkedin.com/in/grayhemp Jabber: gray...@gmail.com Skype: gray-hemp Phone: +14158679984 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Curosity question regarding "LOCK" NOWAIT
Has there been any discussion regarding adding a time-limited version of NOWAIT, say: "WAITONLY 50" (milliseconds), when dealing the explicit LOCK TABLE or the SELECT.FOR(SHARE|UPDATE) commands? David J.
Re: [GENERAL] Curosity question regarding "LOCK" NOWAIT
On 09/21/12 7:43 PM, David Johnston wrote: Has there been any discussion regarding adding a time-limited version of NOWAIT, say: “WAITONLY 50” (milliseconds), when dealing the explicit LOCK TABLE or the SELECT…FOR(SHARE|UPDATE) commands? is this a feature in any other major databases? is this in the sql spec? what do you expect to happen if these timeouts expire? return an error, and abort the transaction? -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Question about upgrading extensions
If one releases an extension with say a version number of 0.1 and then releases one with important changes at 0.2, how is the best way to manage these changes? I couldn't find anything in the docs to discuss this issue. Am I missing something? Specifically for pg_message_queue, for 0.2 I would like to change the type of a field from text (constrained to 'text', 'xml' or 'bytea) to regtype. Not sure where to put the DDL for an upgrade. Best Wishes, Chris Travers
Re: [GENERAL] Curosity question regarding "LOCK" NOWAIT
On Sep 22, 2012, at 0:08, John R Pierce wrote: > On 09/21/12 7:43 PM, David Johnston wrote: >> Has there been any discussion regarding adding a time-limited version of >> NOWAIT, say: “WAITONLY 50” (milliseconds), when dealing the explicit LOCK >> TABLE or the SELECT…FOR(SHARE|UPDATE) commands? > > is this a feature in any other major databases? Doesn't matter to me...I'm only using PostgreSQL > > is this in the sql spec? > Guessing no if it is not implemented... > what do you expect to happen if these timeouts expire? return an error, and > abort the transaction? > The same thing that happens if "NOWAIT" had been specified instead. The goal is to specify that I don't mind waiting but only for a short period of time. David J. -- 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] Curosity question regarding "LOCK" NOWAIT
On Fri, Sep 21, 2012 at 7:43 PM, David Johnston wrote: > Has there been any discussion regarding adding a time-limited version of > NOWAIT, say: “WAITONLY 50” (milliseconds), when dealing the explicit LOCK > TABLE or the SELECT…FOR(SHARE|UPDATE) commands? I think you could do this by issuing SET statement_timeout = 50; and then attempting the LOCK TABLE or SELECT ... FOR UPDATE command, without NOWAIT. You'll either get an "ERROR: canceling statement due to statement timeout" or the command should succeed. Josh -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
