Re: [GENERAL] yum repository packages 9.0 and 9.1 libpq conflict

Hi John, On Wed, 2012-03-14 at 21:54 -0700, John R Pierce wrote: > > So I have a CentOS 6.2 x86_64 system that was running postgres 9.0 > from Devrim's yum repo... It is now a community repo ;) > I installed 9.1 and was going to do a parallel upgrade, but the entry > for 9.0 in /etc/ld.so.

Re: [GENERAL] permission denied to create extension "ltree" Must be superuser to create this extension.

On Wed, Mar 14, 2012 at 9:28 PM, krz...@gmail.com wrote: > Ok, but can someone comment, document something on security of > installing extensions for normal users? Does allowing access to > extension provides a way to circumvent security model? If not why > can't it be allowed for user installatio

Re: [GENERAL] A 154 GB table swelled to 527 GB on the Slony slave. How to compact it?

On Wed, Mar 14, 2012 at 9:57 PM, Scott Marlowe wrote: > Are you sure you're checking the toast table that goes with whatever > parent table? Yep. I find out the relation id of the TOAST table: "select reltoastrelid from pg_class where relname = 'parent_table_name';" Find out the relation name

Re: [GENERAL] permission denied to create extension "ltree" Must be superuser to create this extension.

Ok, but can someone comment, document something on security of installing extensions for normal users? Does allowing access to extension provides a way to circumvent security model? If not why can't it be allowed for user installations (provided that extension was previously allowed in some conf fi

Re: [GENERAL] A 154 GB table swelled to 527 GB on the Slony slave. How to compact it?

On Wed, Mar 14, 2012 at 10:57 PM, Scott Marlowe wrote: > If the du -s numbers don't change or only a little then feel free to > either run a single update while running > > watch "df -h /var/lib/where/my/data/dir/lives" > > and being ready to hit CTRL-C if you see if running your machine out of >

Re: [GENERAL] A 154 GB table swelled to 527 GB on the Slony slave. How to compact it?

On Wed, Mar 14, 2012 at 9:06 PM, Aleksey Tsalolikhin wrote: > > On Wed, Mar 14, 2012 at 7:38 PM, Scott Marlowe > wrote: >> >> The good news is that if the table is >> bloated, it should be able to just write to the free space in the >> table that's already there. > > Thank you, I got it.  The ta

[GENERAL] yum repository packages 9.0 and 9.1 libpq conflict

this is probably mostly for Devrim, since he's the packager of the RPM repository So I have a CentOS 6.2 x86_64 system that was running postgres 9.0 from Devrim's yum repo... I installed 9.1 and was going to do a parallel upgrade, but the entry for 9.0 in /etc/ld.so.conf.d/postgresql-9.

Re: [GENERAL] copy in date string "00-00-00 00:00:00"

On Mar 14, 2012, at 6:32 PM, Andy Colson wrote: > On 03/14/2012 08:16 PM, Scott Marlowe wrote: >> >> >> Can you run it through sed and replace the "-00-00 00:00:00" to >> NULL (no quotes) ? That should work. >> > > I think COPY (depending on arguments) uses \N by default. > > Another op

Re: [GENERAL] A 154 GB table swelled to 527 GB on the Slony slave. How to compact it?

On Wed, Mar 14, 2012 at 8:06 PM, Aleksey Tsalolikhin wrote: > Executive summary:  Why would the TOAST table on the slave have 4x the > page count of the master? Hypothesis: the pages of the TOAST table on the slave have more unused space in them than the pages of the TOAST table on the master.

Re: [GENERAL] A 154 GB table swelled to 527 GB on the Slony slave. How to compact it?

Executive summary: Why would the TOAST table on the slave have 4x the page count of the master? Is there a way to compact it if I don't have enough disk space to duplicate the table? How do I prevent this situation from recurring? On Wed, Mar 14, 2012 at 7:38 PM, Scott Marlowe wrote: > > The g

Re: [GENERAL] Did xpath_table get dropped.

Rob Sargent writes: > I don't see anything to that effect in the release notes I've looked at. That's cause it's still there. Did you remember to build/install contrib/xml2? The xpath functions you're listing are all core, I think. regards, tom lane -- Sent via pgsql-

Re: [GENERAL] A 154 GB table swelled to 527 GB on the Slony slave. How to compact it?

On Wed, Mar 14, 2012 at 8:24 PM, Aleksey Tsalolikhin wrote: > On Tue, Mar 13, 2012 at 5:14 AM, Vick Khera wrote: >> I'll bet what happened was postgres re-wrote your table for you, >> effectively doing a compaction.  You can get similar effect by doing >> an alter table and "changing" an INTEGER

[GENERAL] Did xpath_table get dropped.

I don't see anything to that effect in the release notes I've looked at. I built 9.1.2 from source with these options. CONFIGURE = '--with-libraries=/lib:/lib64:/lib/x86_64-linux-gnu' '--with-python' '--with-openssl' '--with-ossp-uuid' '--with-libxml' '--with-libxslt' \df xpath*

Re: [GENERAL] A 154 GB table swelled to 527 GB on the Slony slave. How to compact it?

On Tue, Mar 13, 2012 at 5:14 AM, Vick Khera wrote: > I'll bet what happened was postgres re-wrote your table for you, > effectively doing a compaction.  You can get similar effect by doing > an alter table and "changing" an INTEGER field to be INTEGER. > Postgres does not optimize that do a no-op,

Re: [GENERAL] copy in date string "00-00-00 00:00:00"

On Wed, Mar 14, 2012 at 7:37 PM, Andy Colson wrote: > On 03/14/2012 08:32 PM, Andy Colson wrote: >> >> On 03/14/2012 08:16 PM, Scott Marlowe wrote: >>> >>> On Wed, Mar 14, 2012 at 6:47 PM, Mark Phillips >>> wrote: I am migrating a data set from Oracle 8i to PG 9.1. The process is to >>>

Re: [GENERAL] copy in date string "00-00-00 00:00:00"

On 03/14/2012 08:32 PM, Andy Colson wrote: On 03/14/2012 08:16 PM, Scott Marlowe wrote: On Wed, Mar 14, 2012 at 6:47 PM, Mark Phillips wrote: I am migrating a data set from Oracle 8i to PG 9.1. The process is to export data into csv files, then use the pg "copy table from file csv header" sta

Re: [GENERAL] copy in date string "00-00-00 00:00:00"

On 03/14/2012 08:16 PM, Scott Marlowe wrote: On Wed, Mar 14, 2012 at 6:47 PM, Mark Phillips wrote: I am migrating a data set from Oracle 8i to PG 9.1. The process is to export data into csv files, then use the pg "copy table from file csv header" statement to load the tables. There are a nu

Re: [GENERAL] copy in date string "00-00-00 00:00:00"

On Wed, Mar 14, 2012 at 6:47 PM, Mark Phillips wrote: > I am migrating a data set from Oracle 8i to PG 9.1. The process is to export > data into csv files, then use the pg "copy table from file csv header" > statement to load the tables. > > There are a number of date columns in the tables that

[GENERAL] copy in date string "00-00-00 00:00:00"

I am migrating a data set from Oracle 8i to PG 9.1. The process is to export data into csv files, then use the pg "copy table from file csv header" statement to load the tables. There are a number of date columns in the tables that include empty value (null), valid dates, and some with the time

Re: [GENERAL] xlog corruption

On Mon, 2012-02-27 at 16:30 -0800, Jameison Martin wrote: > I'd like to get some clarification around an architectural point about > recovery. I see that it is normal to see "unexpected pageaddr" errors > during recovery because of the way Postgres overwrites old log files, > and thus this is taken

Re: [GENERAL] Searching email, Full Text Search prefix, not expected results

But tsvector recognices email format as natural. And I'm not looking for a substring. Please see the queries are incremental with the search string, and see last four results ... I think some think it's no working properly. El 14 de marzo de 2012 19:05, Daniel Vázquez escribió: > Hi guys, I'm goi

Re: [GENERAL] Upgrade questions

On Wed, Mar 14, 2012 at 2:34 PM, John R Pierce wrote: > On 03/14/12 12:53 PM, Scott Marlowe wrote: >> >> Note that if you don't need a lot of storage you can often use 300G >> 15k SAS drives which are around $300 each. 20 of those in a RAID-10 >> gives you ~3TB of storage which is plenty for most

Re: [GENERAL] pg_role vs. pg_shadow or pg_user

Alexander Reichstadt writes: > in the documentation of 8.1 the concept of roles is outlined compared > to users and groups at > . Um ... why are you reading 8.1 documentation while running 9.1? There are likely to be some obsolete things

Re: [GENERAL] pg_role vs. pg_shadow or pg_user

You only get pg_shadow entries for roles that can login (rolcanlogin = true). CREATE ROLE defaults to NO LOGIN. CREATE USER defaults to LOGIN. See http://www.postgresql.org/docs/9.1/interactive/sql-createrole.html _

[GENERAL] pg_role vs. pg_shadow or pg_user

Hi, in the documentation of 8.1 the concept of roles is outlined compared to users and groups at . I am running 9.1 and due to currently learning about the ins and outs of users and permissions in postgres as opposed to mysql, and becau

Re: [GENERAL] Searching email, Full Text Search prefix, not expected results

=?ISO-8859-1?Q?Daniel_V=E1zquez?= writes: > Hi guys, I'm going to crazy about FTS with prefix agains email values on > tsvector. tsvector is meant for searching for natural-language words. It is not a good idea to imagine that it works exactly like a substring match, especially on strings that a

Re: [GENERAL] Upgrade questions

On 03/14/12 12:53 PM, Scott Marlowe wrote: Note that if you don't need a lot of storage you can often use 300G 15k SAS drives which are around $300 each. 20 of those in a RAID-10 gives you ~3TB of storage which is plenty for most transactional applications. I'm actually using 25 x 146gb 15k SAS

Re: [GENERAL] Upgrade questions

On Wed, Mar 14, 2012 at 1:41 PM, John R Pierce wrote: > On 03/14/12 12:16 PM, Steve Crawford wrote: >> >> I was just looking at some modest-sized 15k SAS drives that priced out in >> the $400-550 range. 25 of them would add a minimum of $10,000 to the price >> tag. Still under 6-figures, though. >

Re: [GENERAL] Upgrade questions

On 03/14/12 12:16 PM, Steve Crawford wrote: I was just looking at some modest-sized 15k SAS drives that priced out in the $400-550 range. 25 of them would add a minimum of $10,000 to the price tag. Still under 6-figures, though. those disks aren't any cheaper when they are behind a EMC or Net

Re: [GENERAL] COPY and indices?

Le mercredi 14 mars 2012 à 15:06, Merlin Moncure a écrit : > 2012/3/14 François Beausoleil (mailto:franc...@teksol.info)>: > > In the entirety of my career, I have never found anything more > perplexing than the general reluctance to upgrade hardware to solve > hardware related performance bo

Re: [GENERAL] Upgrade questions

On 03/14/2012 12:04 PM, John R Pierce wrote: On 03/14/12 12:24 AM, John R Pierce wrote: thats my definition of a middle of the road database server. I have no idea what yours is. let me add... this server was under $7000 plus the disk drives (it actually has 25 drives... My car was onl

Re: [GENERAL] COPY and indices?

2012/3/14 François Beausoleil : > Le mardi 13 mars 2012 à 11:15, Merlin Moncure a écrit : > >> 2012/3/13 François Beausoleil > (mailto:franc...@teksol.info)>: >> > >> > I'll go with the COPY, since I can live with the batched requirements just >> > fine. >> >> 30-40 'in transaction' i/o bound inse

Re: [GENERAL] Upgrade questions

On 03/14/12 12:24 AM, John R Pierce wrote: thats my definition of a middle of the road database server. I have no idea what yours is. let me add... this server was under $7000 plus the disk drives (it actually has 25 drives, 20 are in the raid10 used for the database testing).we buil

[GENERAL] Searching email, Full Text Search prefix, not expected results

Hi guys, I'm going to crazy about FTS with prefix agains email values on tsvector. Follow how to reproduce: For the next tsvector: =# select to_tsvector('u...@company.com') to_tsvector -- 'u...@company.com':1 I expects TRUE for all next tsqueryes: select to_tsvector('u

Re: [GENERAL] COPY and indices?

Le mardi 13 mars 2012 à 11:15, Merlin Moncure a écrit : > 2012/3/13 François Beausoleil (mailto:franc...@teksol.info)>: > > > > I'll go with the COPY, since I can live with the batched requirements just > > fine. > > 30-40 'in transaction' i/o bound inserts is so slow as to not really > be

Re: [GENERAL] Alter Column from inet to inet[]

Alex - writes: > Hi,I need to change a column type from inet to inet[] but the alter command > always gives me the following errors > ERROR: column "access_ip" cannot be cast to type inet[] > ALTER TABLE users ALTER COLUMN access_ip SET DATA TYPE inet[] USING > access_ip::inet[]; The problem i

[GENERAL] Alter Column from inet to inet[]

Hi,I need to change a column type from inet to inet[] but the alter command always gives me the following errors ERROR: column "access_ip" cannot be cast to type inet[] ALTER TABLE users ALTER COLUMN access_ip SET DATA TYPE inet[] USING access_ip::inet[]; Tried various but no luck. Couldn't fin

Re: [GENERAL] Upgrade questions

Heh. OK, so I'll plan on about 100 writes per second... *gulp* Thanks a bunch for the info guys. Cheers, Carson On Wed, Mar 14, 2012 at 7:54 AM, Bret Stern < bret_st...@machinemanagement.com> wrote: > I felt pretty good about my server until I read this. > On Wed, 2012-03-14 at 00:24 -0700, Jo

Re: [GENERAL] Upgrade questions

I felt pretty good about my server until I read this. On Wed, 2012-03-14 at 00:24 -0700, John R Pierce wrote: > On 03/13/12 8:41 PM, Carson Gross wrote: > > Does anyone have a reasonable guess as to the inserts per second > > postgres is capable of these days on middle-of-the-road hardware? Any

Re: [GENERAL] Call for Google Summer of Code (GSoC) 2012: Project ideas?

Hi all, 2012/3/14 Thomas Kellerer : > Stefan Keller, 08.03.2012 20:40: > >> Hi >> >> I do have a student who is interested in participating at the Google >> Summer of Code (GSoC) 2012 >> Now I have the "burden" to look for a cool project... Any ideas? >> >> -Stefan >> > > What about an extension t

Re: [GENERAL] Call for Google Summer of Code (GSoC) 2012: Project ideas?

Stefan Keller, 08.03.2012 20:40: Hi I do have a student who is interested in participating at the Google Summer of Code (GSoC) 2012 Now I have the "burden" to look for a cool project... Any ideas? -Stefan What about an extension to the CREATE TRIGGER syntax that combines trigger definition

Re: [GENERAL] Ways to edit users and permissions for database

Excellent, Thank you. Exactly what I was looking for. Am 14.03.2012 um 14:26 schrieb Adrian Klaver: > On 03/14/2012 12:59 AM, Alexander Reichstadt wrote: >> Thanks, creation works fine, but how do I read existing permissions through >> SQL, is there some SELECT-statement I can use? >> > > Not

Re: [GENERAL] Ways to edit users and permissions for database

On 03/14/2012 12:59 AM, Alexander Reichstadt wrote: Thanks, creation works fine, but how do I read existing permissions through SQL, is there some SELECT-statement I can use? Not sure what you want, all permissions for a user(role), permissions for an object or some other combination but her

[GENERAL] provide pg_get_notifications()

Hi, Please provide a pg_get_notifications() function (or similar) as a complement to pg_notify(). Currently, in java jdbc, the clients must poll for notifications using getNotifications() method in org.postgresql.PGConnection. So, clients must obtain a reference to connection object and cast it d

Re: [GENERAL] Frontend/Backend protocol question.

Dmitriy Igrishin wrote: > According to > http://www.postgresql.org/docs/9.1/static/protocol-flow.html#AEN91458 > "is not actually necessary for the frontend to wait for > ReadyForQuery before issuing another command". > > But is it necessary for frontend to wait for ReadyForQuery > before sending

Re: [GENERAL] Ways to edit users and permissions for database

Thanks, creation works fine, but how do I read existing permissions through SQL, is there some SELECT-statement I can use? Am 14.03.2012 um 08:43 schrieb John R Pierce: > On 03/14/12 12:38 AM, Alexander Reichstadt wrote: >> this was probably asked dozens of times before, but I couldn't find whe

Re: [GENERAL] Ways to edit users and permissions for database

On 03/14/12 12:38 AM, Alexander Reichstadt wrote: this was probably asked dozens of times before, but I couldn't find where, and neither in the docs and what I found on the web didn't make sense. I found how to create users and check their permissions using terminal. But I need to alter and cr

[GENERAL] Ways to edit users and permissions for database

Hi, this was probably asked dozens of times before, but I couldn't find where, and neither in the docs and what I found on the web didn't make sense. I found how to create users and check their permissions using terminal. But I need to alter and create users and permissions through libpq or SQL

Re: [GENERAL] Interesting article, Facebook woes using MySQL

On Tue, Mar 13, 2012 at 01:22:18AM +0100, Stefan Keller wrote: > Hi all > > 2011/7/12 Chris Travers : > > I am not convinced that VoltDB is a magic bullet either.  I don't > > I have the chance to help preparing an interview with Mike > Stonebreaker to be published at www.odbms.org > I'd really l

Re: [GENERAL] Upgrade questions

On 03/13/12 8:41 PM, Carson Gross wrote: Does anyone have a reasonable guess as to the inserts per second postgres is capable of these days on middle-of-the-road hardware? Any order of magnitude would be fine: 10, 100, 1000, 10,000. my dedicated database server in my lab, which is a 2U dual X