http://permalink.gmane.org/gmane.comp.db.postgresql.general hourly 1 1901-01-01T00:00+00:00 http://gmane.org/img/gmane-25t.png http://gmane.org http://permalink.gmane.org/gmane.comp.db.postgresql.general/172801 <pre>Hi, (We're running postgres 9.2.X) from reading http://www.postgresql.org/docs/9.2/static/storage-file-layout.html the directories under $PG_DATADIR/data/base should correspond to an actual database. I've however found a few directories in $PG_DATADIR/data/base where select datname from pg_database where oid = &lt;oid&gt; returns 0 rows. The machine does have "high churn" on databases and have suffered a few crashes, is it possible for things to get left behind in $PG_DATADIR/data/base after recovery ? or am I possibly missing things still in use, that's reflected elsewhere ? Additionally, the directory and it's contents haven't been touched in quite a while as well, and some of them do correspond to being touched at the machine having had a hard crash. The reason I'm asking is that space used as reported by pg_database_size is off by a LOT compared to df on the filesystem (4-5TB as oposed to df/du agreeing on 9-ish TB). Cheers, </pre> Kjetil Jørgensen 2013-05-21T20:21:51 http://permalink.gmane.org/gmane.comp.db.postgresql.general/172800 <pre>From: Dev Kumkar [mailto:devdas.kumkar&lt; at &gt;gmail.com] Sent: Tuesday, May 21, 2013 7:33 PM To: Dann Corbit Cc: John R Pierce; pgsql-general&lt; at &gt;postgresql.org; pgsql-odbc&lt; at &gt;postgresql.org Subject: Re: [ODBC] [GENERAL] ODBC constructs [snip] Thanks for the info. Its Red Hat Enterprise Linux Server release 5.5. Please also take a look at my previous reply too. Maybe: https://access.redhat.com/site/documentation/en-US/Red_Hat_Enterprise_Linux/5/html/5.8_Technical_Notes/unixODBC64.html &lt;&lt; </pre> Dann Corbit 2013-05-22T02:36:06 http://permalink.gmane.org/gmane.comp.db.postgresql.general/172799 <pre> This is still a good idea - see below. Btw that doesn't mean anything (neither in terms of performance nor stability), since "the controller" also needs to be paired with an - often vendor-dependent - firmware, which is much more relevant. Since LSI acquired Sandforce this situation has gotten much better (unified upstream). There is (now), because.. Nope, wrong, because.. (..getting there :) Unfortunately since 3.8 discards are issued as synchronous commands, effectively disabling any scheduling/merging etc. The result can be seen easily: - mount drive without discard using kernel &gt;= 3.8 - unpack kernel source - time delete of entire tree - remount with discard - unpack kernel tree - start delete of tree - ... - check it hasn't crashed - ... - go plant a tree or make babies while waiting for it to finish Online discard has gotten so slow that it's now a good idea to turn off for anything but light write workloads. Metadata-heavy writes are obviously the worst case. I experienced this on Samsun</pre> Holger Hoffstaette 2013-05-21T11:27:08 http://permalink.gmane.org/gmane.comp.db.postgresql.general/172798 <pre> Thanks Devrim! Installed postgres-92 server from postgresql92-server-9.2.4-1PGDG.rhel5.x86_64.rpm, actually links which John (Thanks!) mentioned were for CentOS, actually installed unixODBC with them only but then for postgres DB used the one for RHEL http://yum.postgresql.org/9.2/redhat/rhel-5.0-x86_64/ Had to reload unixODBC-libs-2.2.11-10.el5.x86_64.rpm again and then also got postgresql92-odbc rpm from RHEL link. Things are now installed. So I have one box with compiled unixodbc and psqlodbc from sources. And other box where unixodbc and psqlodbc are installed via rpm way. Can someone provide a quick C odbc code snippet? Regards... </pre> Dev Kumkar 2013-05-23T01:06:05 http://permalink.gmane.org/gmane.comp.db.postgresql.general/172797 <pre>[snip] You want me clean some stuff. Because I installed 'unixODBC-2.3.0'? I guess that if you did a successful make install of unixODBC-2.3.0 it will work as your ODBC driver manager. On the other hand, it is easier and more trouble free to use the standardized package installer for your distribution. &lt;&lt; [snip] </pre> Dann Corbit 2013-05-22T03:40:41 http://permalink.gmane.org/gmane.comp.db.postgresql.general/172796 <pre>So I worked around most of my errors.  I removed the bigserial and used two of the columns as the primary key.  I am now getting the following hibernate exception back: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1 This appears to be caused by the fact that the function is not returning back the row count.  I did a google search and found a few suggestions on how to resolve this issue, but they do not seem to work well.  I tried returning NEW, but that seems to cause the engine to also insert the record in the base table as well as a partition.  Thus I end up with 120 records when I am expecting just 60. Any ideas on how I can fix this issue?   Regards, Richard ________________________________ From: Richard Onorato &lt;richard_onorato&lt; at &gt;yahoo.com&gt; To: Raghavendra &lt;raghavendra.rao&lt; at &gt;enterprisedb.com&gt; Cc: "pgsql-general&lt; at &gt;postgresql.org" &lt;pgsql-general&lt; at &gt;postgresql.org&gt; Sent: Wednesday, May 22, 2013 7:27 PM Subject: Re: [GENERAL] Table Partitioning Raghav</pre> Richard Onorato 2013-05-23T02:17:18 http://permalink.gmane.org/gmane.comp.db.postgresql.general/172795 <pre>Raghavendra, I am doing my inserts via Java JPA statements embedded in my Data Access Layer.  I can share them if you would like to see them.   Regards, Richard ________________________________ From: Raghavendra &lt;raghavendra.rao&lt; at &gt;enterprisedb.com&gt; To: Richard Onorato &lt;richard_onorato&lt; at &gt;yahoo.com&gt; Cc: "pgsql-general&lt; at &gt;postgresql.org" &lt;pgsql-general&lt; at &gt;postgresql.org&gt; Sent: Wednesday, May 22, 2013 2:39 AM Subject: Re: [GENERAL] Table Partitioning On Wed, May 22, 2013 at 6:54 AM, Richard Onorato &lt;richard_onorato&lt; at &gt;yahoo.com&gt; wrote: Were you able to get it to insert with the bigserial being used on the table?   Yes.   Every time I go to do an insert into one of the inherited tables I am now getting the following exception: Hmm, I guess you are inserting on the parent table not directly into inherited table. Can you share the INSERT statement.   Is auto-increment supported on table partitioning? Yes, BIGSERIAL will create a sequence that will be shared by all child partitions. Check below example as </pre> Richard Onorato 2013-05-23T00:27:10 http://permalink.gmane.org/gmane.comp.db.postgresql.general/172794 <pre> From the numbers in your attached plan, it seems like it should be doing a nested loop from the 580 rows (it thinks) that match in SARS_ACTS_RUN against the index on sars_run_id to pull out the 3297 rows (again, it think, though it is way of there). I can't see why it would not do that. There were some planner issues in the early 9.2 releases that caused very large indexes to be punished, but I don't think those were in 9.1 Could you "set enable_hashjoin to off" and post the "explain analyze" that that gives? Cheers, Jeff </pre> Jeff Janes 2013-05-23T00:17:26 http://permalink.gmane.org/gmane.comp.db.postgresql.general/172793 <pre>BTW, pgeu-general is not for technical questions, so moving to pgsql-general. (I didn't notice the mailing list this came from until after replying). On 22.05.2013 18:22, Heikki Linnakangas wrote: </pre> Heikki Linnakangas 2013-05-22T22:26:29 http://permalink.gmane.org/gmane.comp.db.postgresql.general/172791 <pre> It's not using history_lookup_lookupid_creator_index, or even history_lookup_lookupid_index, because it thinks, rightly or wrongly, that it can get 1000 rows by reading history_creator_index backwards and filtering out rows that don't match your where clause. Since in this case, ordering is the most beneficial piece, it can't use history_lookup_lookupid_creator_index to do this because creator is the third column in the index. If you redefine that index to this instead: CREATE INDEX history_lookup_lookupid_creator_index ON public.history (creator, lookup, lookupid); You *should* get a much faster result. That would also allow you to drop history_creator_index. Since history_lookup_lookupid_index covers the same first two columns, you shouldn't lose anything in queries that work better with those in the front. </pre> Shaun Thomas 2013-05-22T19:49:49 http://permalink.gmane.org/gmane.comp.db.postgresql.general/172790 <pre>Hi, I have the following simple query on a simple table: system=# select * from history where lookup = 'trunk' and lookupid = '248' order by created desc limit 1000; system=# \d history Table "public.history" Column | Type | Modifiers ----------+--------------------------+------------------------------------------------------ id | integer | not null default nextval('history_id_seq'::regclass) created | timestamp with time zone | creator | integer | not null default 1 contact | integer | not null default 1 type | character varying | not null default ''::character varying lookup | text | lookupid | integer | not null default 1 value | text | Indexes: "history_pkey" PRIMARY KEY, btree (id) "history_created_index" btree (created) "history_creator_index" btree (creator) "histor</pre> Antonio Goméz Soto 2013-05-22T19:38:24 http://permalink.gmane.org/gmane.comp.db.postgresql.general/172789 <pre> On May 21, 2013, at 2:41 PM, Bruce Momjian wrote: Well, the story really began when I ran initdb with a -U arg. vacuumdb takes the -U also, but pg_upgrade does not. It seems like if I have to supply a -u in order to get pg_upgrade to function in the case where there is no default superuser in the cluster, then an associated vacuumdb command requires a -U arg. Perhaps just documenting the behavior is all that is needed, but -U is everywhere and I think that's a good thing. </pre> Ray Stell 2013-05-22T19:05:57 http://permalink.gmane.org/gmane.comp.db.postgresql.general/172788 <pre>Hi All, We are facing one strange problem about data file corruptions. We have many postgres databases. At some point, one simple query on one database started crashing back-end. The query is select count(*), col1 from tab1 group by col1; After using pg_filedump (http://pgfoundry.org/projects/pgfiledump/) on data files for tab1 (relnodeid in pg_class), we found that the number of attributes per tuple is different for few tuples in data file than the rest. pg_filedump utility prints the contents of each tuple along with block header, data header. In our case, the same data file has the following two data headers. valid header ============ &lt;Data&gt; ------ Item 1 – Length: 114 Offset: 32648 (0x7f88) Flags: NORMAL XMIN: 8849668 XMAX: 0 CID|XVAC: 0 Block Id: 0 linp Index: 1 Attributes: 10 Size: 24 infomask: 0x0902 (HASVARWIDTH|XMIN_COMMITTED|XMAX_INVALID) Invalid header ========== &lt;Data&gt; ------ Item 1 – Length: 234 Offset: 32528 (0x7f10) Flags: NORMAL XMIN: 2959623 XMAX: 0 CID|XVAC: 0 Blo</pre> PG User 2013-05-22T19:03:20 http://permalink.gmane.org/gmane.comp.db.postgresql.general/172787 <pre>Solution: The inserts of the foreign key to tb_entity were blocking the updates to those rows of tb_entity. I solved the problem by making the foreign key constraints deferrable and deferring checking on them till the end of the transaction. On Tue, May 21, 2013 at 3:24 PM, Moshe Jacobson &lt;moshe&lt; at &gt;neadwerx.com&gt; wrote: </pre> Moshe Jacobson 2013-05-22T18:54:32 http://permalink.gmane.org/gmane.comp.db.postgresql.general/172786 <pre> why the heck are you not installing unixODBC-libs from RPMs ?!? yum install unixODBC64 unixODBC64-libs unixODBC64-devel should do it, unless you're on Red Hat Enterprise without a subscription, then you can do it the hard way... mkdir unixODBCdownloads &amp;&amp; cd unixODBCdownloads wget http://mirrors.kernel.org/centos/5/os/x86_64/CentOS/unixODBC64-2.2.14-3.el5.x86_64.rpm wget http://mirrors.kernel.org/centos/5/os/x86_64/CentOS/unixODBC64-libs-2.2.14-3.el5.x86_64.rpm wget http://mirrors.kernel.org/centos/5/os/x86_64/CentOS/unixODBC64-devel-2.2.14-3.el5.x86_64.rpm rpm -Uvh *.rpm if you get any dependency errors, fetch the required RPMs from http://mirrors.kernel.org/centos/5/os/x86_64/CentOS/ and install manually with the rpm command. don't be surprised if the dependencies have dependencies, normally yum would sort that out automatically. if your application is 32bit, then you'll need to install 32bit ODBC instead of the 64bit stuff above. if you ARE on Red Hat En</pre> John R Pierce 2013-05-22T18:44:48 http://permalink.gmane.org/gmane.comp.db.postgresql.general/172785 <pre>Okay, so why does wrapping the order by in a function fix it? (or not doing a join, or doing an implicit join) Cody Cutrer On Wed, May 22, 2013 at 11:36 AM, Tom Lane &lt;tgl&lt; at &gt;sss.pgh.pa.us&gt; wrote: </pre> Cody Cutrer 2013-05-22T17:37:23 http://permalink.gmane.org/gmane.comp.db.postgresql.general/172784 <pre> I think it's unhappy because "sortable_name" could refer to either of the output columns (under the old SQL92 convention that an ORDER BY item is an output column name). Probably the easiest way to dodge that is to qualify the name, ie ORDER BY test1.sortable_name. A different line of attack is to use AS to relabel whichever output column you don't want to match. regards, tom lane </pre> Tom Lane 2013-05-22T17:36:09 http://permalink.gmane.org/gmane.comp.db.postgresql.general/172783 <pre>Maybe refcursors ?? All what you can do with cursors is described in http://www.postgresql.org/docs/9.3/static/plpgsql-cursors.html Regards Pavel 2013/5/22 Sajeev Mayandi &lt;Sajeev_Mayandi&lt; at &gt;symantec.com&gt;: </pre> Pavel Stehule 2013-05-22T17:23:45 http://permalink.gmane.org/gmane.comp.db.postgresql.general/172782 <pre>Is there a work around to declare the cursor with hold? Thanks, Sajeev On 5/22/13 10:19 AM, "Pavel Stehule" &lt;pavel.stehule&lt; at &gt;gmail.com&gt; wrote: </pre> Sajeev Mayandi 2013-05-22T17:21:37 http://permalink.gmane.org/gmane.comp.db.postgresql.general/172781 <pre>Hello 2013/5/22 Sajeev Mayandi &lt;Sajeev_Mayandi&lt; at &gt;symantec.com&gt;: holdable cursors are not supported in plpgsql Regards Pavel Stehule </pre> Pavel Stehule 2013-05-22T17:19:07 http://permalink.gmane.org/gmane.comp.db.postgresql.general/172780 <pre> Hi, I am trying to declare a cursor with hold along with NO SCROLL option. I am getting syntax error. Just wondering if CURSOR WITH HOLD option supported. My code snip is. DECLARE noncontainer NO SCROLL CURSOR WITH HOLD FOR SELECT * from phostmapping; NOTE: The code is in plpgsql and postgresql version is 9.2. Thanks, Sajeev </pre> Sajeev Mayandi 2013-05-22T17:14:44 Search the mailing list at Gmane query http://search.gmane.org/?group=$group=gmane.comp.db.postgresql.general