Re: [GENERAL] implicit vs. explicit RETURN when OUT is used
On Sun, 06 Jan 2008 02:47:17 -0500 Tom Lane [EMAIL PROTECTED] wrote: Ivan Sergio Borgonovo [EMAIL PROTECTED] writes: But when I switch to select into _BasketID1,_BasketID2 _BasketID1,_BasketID2 from testA(); nothing get back from testB(). I think you've forgotten that plpgsql variables will be substituted for, wherever they appear. The above is just an extremely expensive form of _BasketID1 := _BasketID1; _BasketID2 := _BasketID2; ie, a big no-op. The general rule of thumb is not to name plpgsql parameters or variables the same as fields you'll need to reference in the queries in the function. I've found a solution that is somehow elegant and unexpected: aliasing the function name. select a.field1, a.field2 into field1, field2 from testfunc() as a; The online documentation doesn't give postgresql the honour it deserves. Things like this can't be found even on voluminous books as The Postgresql reference manual. If you're not a Postgres guru you may think that pg doesn't shine for anything other than things just data paranoids can appreciate but force you to live with strangeness like ILIKE and no syntactical sugar. It's plenty of syntactical sugar. It is just hidden in poetry like this: Except for the INTO clause, the SELECT statement is the same as a normal SQL SELECT command and can use its full power. http://www.postgresql.org/docs/8.1/static/plpgsql-statements.html#PLPGSQL-SELECT-INTO -- Ivan Sergio Borgonovo http://www.webthatworks.it ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Performance problem. Could it be related to 8.3-beta4?
2008/1/5, Tom Lane [EMAIL PROTECTED]: Clodoaldo [EMAIL PROTECTED] writes: How did you get 8.3-beta4? I built from the source rpm, which i installed in my machine. There is something I forgot to mention. I created a patch to change XLOG_SEG_SIZE and built with it: -#define XLOG_SEG_SIZE (16*1024*1024) +#define XLOG_SEG_SIZE (1024*1024*1024) What made you decide that was a good idea?? Please revert that change and see if the performance goes back to normal. I discovered i had a faulty raid, one of the drives were not working: $ cat /proc/mdstat Personalities : [raid1] [raid6] [raid5] [raid4] [raid0] md2 : active raid1 sda1[0] sdb1[1] 224768 blocks [2/2] [UU] md0 : active raid0 sda2[0] sdb2[1] 4497920 blocks 256k chunks md1 : active raid1 sdb3[1] 310094592 blocks [2/1] [_U] After some web reading I managed to make the raid functional: # cat /proc/mdstat Personalities : [raid1] [raid6] [raid5] [raid4] [raid0] md2 : active raid1 sda1[0] sdb1[1] 224768 blocks [2/2] [UU] md0 : active raid0 sda2[0] sdb2[1] 4497920 blocks 256k chunks md1 : active raid1 sda3[0] sdb3[1] 310094592 blocks [2/2] [UU] Just to remember the production server completes the insert query in 800 sec. The new server with the faulty raid and 1GB xlog_seg_size did it in 6,889 sec. With the fixed raid and 1GB xlog_seg_size the time lowered to 1,380 sec. Then I rebuilt and reinstalled postgresql with the xlog_seg_size set to the default 16MB and did initdb. Now the time is 7,642 sec. I'm lost. It looks like 1GB xlog_seg_size is indeed faster than 16MB but again it is slower than the production server which uses the default xlog_seg_size. Regards, Clodoaldo Pinto Neto ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] 8.3-beta4, analyze and db owner
I'm the owner of a database and when i issue an analyze command on it, the pg tables are skipped with the message that only the owner can analyze them: $ psql fahstats -U cpn Welcome to psql 8.3beta4, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit fahstats= analyze; WARNING: skipping pg_authid --- only table or database owner can analyze it WARNING: skipping pg_database --- only table or database owner can analyze it WARNING: skipping pg_tablespace --- only table or database owner can analyze it WARNING: skipping pg_pltemplate --- only table or database owner can analyze it WARNING: skipping pg_shdepend --- only table or database owner can analyze it WARNING: skipping pg_shdescription --- only table or database owner can analyze it WARNING: skipping pg_auth_members --- only table or database owner can analyze it ANALYZE fahstats= \l List of databases Name| Owner | Encoding ---+--+--- fahstats | cpn | SQL_ASCII postgres | postgres | SQL_ASCII template0 | postgres | SQL_ASCII template1 | postgres | SQL_ASCII (4 rows) Regards, Clodoaldo Pinto Neto ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] 8.3-beta4, analyze and db owner
Clodoaldo wrote: I'm the owner of a database and when i issue an analyze command on it, the pg tables are skipped with the message that only the owner can analyze them: $ psql fahstats -U cpn Welcome to psql 8.3beta4, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit fahstats= analyze; WARNING: skipping pg_authid --- only table or database owner can analyze it WARNING: skipping pg_database --- only table or database owner can analyze it WARNING: skipping pg_tablespace --- only table or database owner can analyze it WARNING: skipping pg_pltemplate --- only table or database owner can analyze it WARNING: skipping pg_shdepend --- only table or database owner can analyze it WARNING: skipping pg_shdescription --- only table or database owner can analyze it WARNING: skipping pg_auth_members --- only table or database owner can analyze it ANALYZE fahstats= \l List of databases Name| Owner | Encoding ---+--+--- fahstats | cpn | SQL_ASCII postgres | postgres | SQL_ASCII template0 | postgres | SQL_ASCII template1 | postgres | SQL_ASCII (4 rows) These are the global shared tables, that exist only once in the cluster - not once per database. They can only be analyzed by postgres in this case. (There's a whole bunch of other pg_ tables that are local to the database, and those should be analyzed just fine in this case) Seems a bit unnecessary that we should even try and then emit a warning for them though. //Magnus ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] 8.3-beta4, analyze and db owner
2008/1/6, Magnus Hagander [EMAIL PROTECTED]: Clodoaldo wrote: I'm the owner of a database and when i issue an analyze command on it, the pg tables are skipped with the message that only the owner can analyze them: $ psql fahstats -U cpn Welcome to psql 8.3beta4, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit fahstats= analyze; WARNING: skipping pg_authid --- only table or database owner can analyze it WARNING: skipping pg_database --- only table or database owner can analyze it WARNING: skipping pg_tablespace --- only table or database owner can analyze it WARNING: skipping pg_pltemplate --- only table or database owner can analyze it WARNING: skipping pg_shdepend --- only table or database owner can analyze it WARNING: skipping pg_shdescription --- only table or database owner can analyze it WARNING: skipping pg_auth_members --- only table or database owner can analyze it ANALYZE fahstats= \l List of databases Name| Owner | Encoding ---+--+--- fahstats | cpn | SQL_ASCII postgres | postgres | SQL_ASCII template0 | postgres | SQL_ASCII template1 | postgres | SQL_ASCII (4 rows) These are the global shared tables, that exist only once in the cluster - not once per database. They can only be analyzed by postgres in this case. (There's a whole bunch of other pg_ tables that are local to the database, and those should be analyzed just fine in this case) Seems a bit unnecessary that we should even try and then emit a warning for them though. The problem with that warning message is that it implies that the db owner can analyze them which can not be done according to your comment. The message, if emited, should say then that only superuser can analyze those tables. Regards, Clodoaldo Pinto Neto ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] large table vacuum issues
On Jan 5, 2008 5:38 AM, Ed L. [EMAIL PROTECTED] wrote: We need some advice on how to handle some large table autovacuum issues. One of our 8.1.2 autovacuums is launching a DB-wide vacuum on our 270GB database to prevent xid wrap-around, but is getting hung-up and/or bogged down for hours on a 40gb table and taking the server performance down with it, apparently due to an IO bottleneck. The autovac child process becomes completely unresponsive to SIGTERM/SIGINT; only a sigkill restart with disabling the autovac daemon gets us back to adequate performance for now. Looks like you haven't been vacuuming for a while , have you? because it seems the autovac was disabled but was invoked forcefully to avoid wraparound. If infact the wraparound happens you will lose data. When autovacuum is processing a table it wouldn't take more time than what a normal vacuum would take. What might help you really is a temporary increase in maint work memory, whats your current setting? how much RAM do you have?, if you can afford more memory, increase it to significantly high value to help speed up the vacuum process. I understand it might impact some other system activity but you need a vacuum and fast, before you lose all data. You need to get rid of dead rows first and then have a healthy vacuuming schedule , either a daily cron job or autovac, setup as your workload . Prevention is ofcourse better than the cure. ;) Thanks, -- Usama Munir Dar http://www.linkedin.com/in/usamadar Consultant Architect Cell:+92 321 5020666 Skype: usamadar
Re: [GENERAL] Performance problem. Could it be related to 8.3-beta4?
On Jan 6, 2008 5:06 AM, Clodoaldo [EMAIL PROTECTED] wrote: Then I rebuilt and reinstalled postgresql with the xlog_seg_size set to the default 16MB and did initdb. Now the time is 7,642 sec. I'm lost. It looks like 1GB xlog_seg_size is indeed faster than 16MB but again it is slower than the production server which uses the default xlog_seg_size. How fast was it the second time you ran it? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Performance problem. Could it be related to 8.3-beta4?
2008/1/6, Scott Marlowe [EMAIL PROTECTED]: On Jan 6, 2008 5:06 AM, Clodoaldo [EMAIL PROTECTED] wrote: Then I rebuilt and reinstalled postgresql with the xlog_seg_size set to the default 16MB and did initdb. Now the time is 7,642 sec. I'm lost. It looks like 1GB xlog_seg_size is indeed faster than 16MB but again it is slower than the production server which uses the default xlog_seg_size. How fast was it the second time you ran it? You mean the new server with 16MB xlog_seg_size? Yes, I did run it twice and both took about the same time. In all tests (all configurations) I did an analyze before running so I think that is the real time. Regards, Clodoaldo Pinto Neto ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Index trouble with 8.3b4
I decided to play a bit with 8.3-b4. I did a fresh install from source,
fresh initdb, and created a single test table (about 700K rows) to play
with in-core FTS:
Welcome to psql 8.3beta4, the PostgreSQL interactive terminal.
hannes= \d fts
Table public.fts
Column | Type |Modifiers
+--+--
id | integer | not null default nextval('fts_id_seq'::regclass)
text | text | not null
tsv| tsvector |
Indexes:
pk_fts PRIMARY KEY, btree (id)
hannes= CREATE INDEX CONCURRENTLY ts_fts_tsv ON public.fts USING gin
(tsv);
ERROR: item pointer (0,1) alreadt exists
I was able to reproduce that error a few times, but not always. It seems
it only happens with CONCURRENTLY.
After creating a GIST index instead of GIN I tried to cluster on that:
hannes= CLUSTER fts USING ts_fts_tsv;
ERROR: could not create unique index pk_fts
DETAIL: Table contains duplicated values.
So duplicate values in my PK column?
hannes= SET enable_indexscan = off;
SET
hannes=
hannes= SELECT
hannes- f.id,
hannes- COUNT(f.id)
hannes- FROM
hannes- public.fts f
hannes- GROUP BY
hannes- f.id
hannes- HAVING
hannes- COUNT(f.id) 1;
id | count
+---
(0 rows)
Where are they?
I'm a bit short of time and will probably not find time to debug this
further until next weekend, sorry.
--
Best regards,
Hannes Dorbath
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] Index trouble with 8.3b4
Hannes Dorbath [EMAIL PROTECTED] writes: hannes= CREATE INDEX CONCURRENTLY ts_fts_tsv ON public.fts USING gin (tsv); ERROR: item pointer (0,1) alreadt exists I was able to reproduce that error a few times, but not always. It seems it only happens with CONCURRENTLY. After creating a GIST index instead of GIN I tried to cluster on that: hannes= CLUSTER fts USING ts_fts_tsv; ERROR: could not create unique index pk_fts DETAIL: Table contains duplicated values. So duplicate values in my PK column? I didn't have any luck reproducing either of these behaviors --- maybe it's data-dependent. Can you extract a test case? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Insert returning Npgsql
Hi. It can be referred to from the page by which we were renewed. http://npgsql.projects.postgresql.org/ I want it to be useful for you. P.S) [EMAIL PROTECTED] is the place of best discussion. Regards, Hiroshi Saito - Original Message - From: Cesar Alvarez [EMAIL PROTECTED] Good day every one . Im trying to make and insert returning with the connector Npgsql, where can i read more about the syntax of the insert string? or how to configure the Npgsql.command class. Regards Cesar Alvarez. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Performance problem. Could it be related to 8.3-beta4?
On Jan 6, 2008 1:46 PM, Clodoaldo [EMAIL PROTECTED] wrote: 2008/1/6, Scott Marlowe [EMAIL PROTECTED]: On Jan 6, 2008 5:06 AM, Clodoaldo [EMAIL PROTECTED] wrote: Then I rebuilt and reinstalled postgresql with the xlog_seg_size set to the default 16MB and did initdb. Now the time is 7,642 sec. I'm lost. It looks like 1GB xlog_seg_size is indeed faster than 16MB but again it is slower than the production server which uses the default xlog_seg_size. How fast was it the second time you ran it? You mean the new server with 16MB xlog_seg_size? Yes, I did run it twice and both took about the same time. In all tests (all configurations) I did an analyze before running so I think that is the real time. OK, to eliminate the chance that it's sick hardware, I would suggest installing 8.2.5 with exactly the same settings and build (as much as possible) and see how that works. If it's still slow, I would suspect the hardware is making the difference and investigate that first. Once you get even performance from 8.2.5 on both sets of hardware, then you can make a valid comparison with 8.3b4. Unless you've already done that... then I don't have a clue what to do... ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] [OT] Slony (initial) Replication - Slow
Andrew Sullivan wrote: On Fri, Jan 04, 2008 at 06:11:40PM -0300, Alvaro Herrera wrote: swooping elephants must be an interesting sight. If pigs can fly ... Is this what you had in mind? http://www.amoeba.com/dynamic-images/blog/dumbo.gif Hmm, something like that, but Dumbo does not look truly fell ;-) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgresSQL vs Ingress
On Fri, 2007-11-30 at 09:33 -0500, Andrew Sullivan wrote: On Fri, Nov 30, 2007 at 01:22:31PM -, Greg Sabino Mullane wrote: or a scapegoat. Please don't perpetuate this urban myth. No companies are suing Oracle and Microsoft because of their products, and companies have no expectation of doing so. It might be nice if they did, and some theorize Indeed, by using the product, companies have explicitly given up the right to sue over it. This is the main point of the EULA of most products, and is one of the strangest things about the computer industry. No other industry can get away with producing shoddy products that endanger others, and induce its users to give up the right to sue in case that shoddiness causes problems. But if you think you could sue Oracle Corp -- or even complain on a public list about how their software ate your data -- and win that fight, I think you need to have a long talk with your corporate counsel :) Well, that was what I was led to believe in talking to some people and to be truly honest, I've never read the EULA (not in its entirety nor understanding its implications entirely either) So, in that respect, I would say I was un-informed. And I take that back. In anycase, if all they want is someone to call, then by all means, it's not a problem. But that's not what I hear/see or rather, not the item which is steering much of the decision making. anyway.. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Server doesn't seem to be listening...
I recently resized a virtual machine hosting an instance of PostGreSQL. After the resize and the required reboot, PostGreSQL seem to be acting a little odd. As a local user, I can connect to my databases. but my Java applications can now longer connect to the database. (I get the standard org.postgresql.util.PSQLException: Connection refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections. I've confirmed from the commandline using psql -h hostname -p port -U username database psql: could not connect to server: Connection refused Is the server running on host hostname and accepting TCP/IP connections on port port? So I think its something I need to fix at the PG end of things. Both Java and command line were occurring on the same host as the server, and worked before the VM resize. Just in case I tried punching a hole in the firewall for the port (wasn't needed before, but just wanted to check that first), but it didn't make any difference. The IP of the server didn't change as a result of the resize, so the line in pg_hba.conf allowing this single account to connect is still correct. Now the thing I know I did wrong was that I didn't shut down the postmaster before the resize brought the system down. (Its test only data, if its hosed, that's acceptable). And I had to manually restart the postmaster after the resize. I didn't see any errors and the startup scripts seemed to run successfully. What else should I be looking at? Thank you. Eric ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Server doesn't seem to be listening...
Eric D. Nielsen [EMAIL PROTECTED] writes: I've confirmed from the commandline using psql -h hostname -p port -U username database psql: could not connect to server: Connection refused That's not a Postgres problem. You do not have network-level connectivity --- I'm betting you forgot to enable TCP access to that VM, or something along that line. Think firewall configuration, not Postgres. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] basic questions: Postgres with yum on CentOS 5.1
I'm sorry for my delayed response. Tomasz, thanks for your email. At 2:38 PM +0100 1/3/08, Tomasz Ostrowski wrote: On Tue, 01 Jan 2008, Chuck wrote: I'm not sure how to make sure automatic updates are turned on as Tometzky recommended. Is that a yum setting? You need to install and configure yum-updatesd to perform automatic updates for you. I don't use it so I don't know exactly how to do this, but I believe it has a well documented configuration file in /etc/. I'll look into this further. Thanks. [EMAIL PROTECTED] ~]# service postgresql start Initializing database: [ OK ] When I listed the current databases, I found out that UTF-8 is not being used. You did not set your /etc/sysconfig/i18n and reboot before you first started, ignoring my recommendation. I'd delete /var/lib/pgsql/data (if there's no data yet) and try again after this setting and reboot. Since I had sent this email, I contacted my web host for help. They said that I could '-E UTF8 --no-locale' to the initdb call within /etc/init.db/postgresql. I stopped postgres, deleted the data directory and restarted postgres. My cluster was now using UTF-8: bash-3.1$ psql -l List of databases Name | Owner | Encoding --+--+-- postgres | postgres | UTF8 template0| postgres | UTF8 template1| postgres | UTF8 (6 rows) I used 'createdb myTest' to create new database with that uses UTF-8. My main concern was to set the encoding to UTF-8. I knew that was important. I believe that I did that with the '-E UTF8' option for initdb. Sort order, and specifically setting LC_COLLATE and LC_CTYPE was less of a concern. (I still need to read and learn more.) Fortunately, I expect to be able to delete my /var/lib/pgsql/data directory for the next few weeks, if necessary. I wanted to investigate your recommendation further before accepting it. By the way, do you think that specifying '--locale=en_US.UTF-8' for initdb id equivalent to having LANG=en_US.UTF-8 set in the /etc/sysconfig/i18n file (and rebooting)? I need to store multiple languages in my database such as English, French The end of the 21.2.2. Setting the Character Set section says, One way to use multiple encodings safely is to set the locale to C or POSIX during initdb, thus disabling any real locale awareness. This is a very bad solution, as it would allow you to store any garbage string in a database. It won't know letter boundaries, so any text functions will misbehave badly. When your database encoding is UTF-8 then you'll be forced to save consistant UTF-8 strings and sorting, text functions, regular expressions etc... will work as expected. I agree with you that enforcing a database encoding of UTF-8 is a good approach. I believe that I'm doing that. If I'm storing multiple languages such as English, French and Japanese do I really want to specify an English locale for English sorting only (which will affect indexes)? If I have multiple languages and must pick one locale for Postgres, is no locale with (with UTF-8 encoding) acceptable? This reference at the end of the 21.2.2. Setting the Character Set section in the 8.1 manual still makes sense to me: Important: Although you can specify any encoding you want for a database, it is unwise to choose an encoding that is not what is expected by the locale you have selected. The LC_COLLATE and LC_CTYPE settings imply a particular encoding, and locale-dependent operations (such as sorting) are likely to misinterpret data that is in an incompatible encoding. Since these locale settings are frozen by initdb, the apparent flexibility to use different encodings in different databases of a cluster is more theoretical than real. It is likely that these mechanisms will be revisited in future versions of PostgreSQL. One way to use multiple encodings safely is to set the locale to C or POSIX during initdb, thus disabling any real locale awareness. http://www.postgresql.org/docs/8.1/static/multibyte.html Am I on the right track? Any thoughts would be appreciated. Thanks, Chuck ---(end of broadcast)--- TIP 6: explain analyze is your friend
