[GENERAL] How can I get list of views that are using given column in table?

hi I have situation, where I need to change datatype of column. But when I do: alter table xx alter column yy type zz; i get error: ERROR: cannot alter type of a column used by a view or rule DETAIL: rule _RETURN on view some_view depends on column "yy" how can I get a list of all such views (in

Re: [GENERAL] How can I get list of views that are using given column in table?

On 20 February 2012 12:06, hubert depesz lubaczewski wrote: > hi > I have situation, where I need to change datatype of column. > But when I do: > alter table xx alter column yy type zz; > i get error: > ERROR:  cannot alter type of a column used by a view or rule > DETAIL:  rule _RETURN on view s

[GENERAL] How to split up phone numbers?

Hi, is there a way to split up phone numbers? I know that's a tricky topic and it depends on the national phone number format. I'm especially interested in a solution for Germany, Swizerland and Austria. I've got everything in a phone number column that makes hardly sense like: +49432156780 004

Re: [GENERAL] How to split up phone numbers?

On 02/20/2012 08:49 AM, Andreas wrote: Hi, is there a way to split up phone numbers? I know that's a tricky topic and it depends on the national phone number format. I'm especially interested in a solution for Germany, Swizerland and Austria. I've got everything in a phone number column that mak

Re: [GENERAL] Question on Rules

<<< text/html; charset="utf-8": Unrecognized >>> <>

Re: [GENERAL] How can I get list of views that are using given column in table?

On Mon, Feb 20, 2012 at 01:06:29PM +, Thom Brown wrote: > You could try this: > > SELECT distinct dependee.relname > FROM pg_depend > JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid > JOIN pg_class as dependee ON pg_rewrite.ev_class = dependee.oid > JOIN pg_class as dependent ON pg_depend.

Re: [GENERAL] How can I get list of views that are using given column in table?

On 20 February 2012 17:29, hubert depesz lubaczewski wrote: > On Mon, Feb 20, 2012 at 01:06:29PM +, Thom Brown wrote: >> You could try this: >> >> SELECT distinct dependee.relname >> FROM pg_depend >> JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid >> JOIN pg_class as dependee ON pg_rewrit

Re: [GENERAL] How to split up phone numbers?

Dear Andreas, this will basically require some external knowledge about the numbers logic. The Format is not so a problem in the first place. Numbers that start with national prefix will either start with "00" or "+" followed by the 2 or 3 digit international prefix. City part is of variable lengt

Re: [GENERAL] Question on Rules

On 02/20/12 9:07 AM, mgo...@isstrucksoftware.net wrote: Are rules deprecated? rules are very tricky, and primarily exist now for internal use. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list

[GENERAL] Unfamous 'could not read block ... in file "...": read only 0 of 8192 bytes' again

One of servers under my support 2 days ago produced the next error: ERROR: could not read block 135 in file "base/16404/118881486": read only 0 of 8192 bytes Server version 9.0.6. No db or server crashes or db recovery happen on that server since setup. Server is db backend for very large web pr

Re: [GENERAL] Unfamous 'could not read block ... in file "...": read only 0 of 8192 bytes' again

Maxim Boguk writes: > One of servers under my support 2 days ago produced the next error: > ERROR: could not read block 135 in file "base/16404/118881486": read only > 0 of 8192 bytes > ... > What I see in file system: > hh=# SELECT relfilenode from pg_class where relname='agency_statistics_old';

Re: [GENERAL] Unfamous 'could not read block ... in file "...": read only 0 of 8192 bytes' again

On Monday, February 20, 2012 5:32:01 pm Tom Lane wrote: > Maxim Boguk writes: > > One of servers under my support 2 days ago produced the next error: > > ERROR: could not read block 135 in file "base/16404/118881486": read > > only 0 of 8192 bytes > > ... > > What I see in file system: > > hh=# S

Re: [GENERAL] Unfamous 'could not read block ... in file "...": read only 0 of 8192 bytes' again

> > > So table file size zero bytes (seems autovacuum truncated that table to 0 > > bytes). > > Hmmm something did, but I see no clear evidence that it was > autovacuum. > > Do you know why the mod date on the file is 2012-02-20 12:04? That's > more than two days after the error in your logs,

Re: [GENERAL] Unfamous 'could not read block ... in file "...": read only 0 of 8192 bytes' again

Maxim Boguk writes: >> Do you know why the mod date on the file is 2012-02-20 12:04? > Cron was attempt to populate the table once per hour after that problem > happened. > And each time it was produced the same error. That's interesting ... is there any possibility that the insertions were atte

Re: [GENERAL] Unfamous 'could not read block ... in file "...": read only 0 of 8192 bytes' again

On Tue, Feb 21, 2012 at 1:37 PM, Tom Lane wrote: > Maxim Boguk writes: > >> Do you know why the mod date on the file is 2012-02-20 12:04? > > > Cron was attempt to populate the table once per hour after that problem > > happened. > > And each time it was produced the same error. > > That's inter

Re: [GENERAL] Unfamous 'could not read block ... in file "...": read only 0 of 8192 bytes' again

On 02/20/2012 07:46 PM, Maxim Boguk wrote: On Tue, Feb 21, 2012 at 1:37 PM, Tom Lane > wrote: Maxim Boguk mailto:maxim.bo...@gmail.com>> writes: >> Do you know why the mod date on the file is 2012-02-20 12:04? > Cron was attempt to populate the tabl

Re: [GENERAL] Unfamous 'could not read block ... in file "...": read only 0 of 8192 bytes' again

On Tue, Feb 21, 2012 at 1:46 PM, Maxim Boguk wrote: > > > On Tue, Feb 21, 2012 at 1:37 PM, Tom Lane wrote: > >> Maxim Boguk writes: >> >> Do you know why the mod date on the file is 2012-02-20 12:04? >> >> > Cron was attempt to populate the table once per hour after that problem >> > happened.

Re: [GENERAL] Unfamous 'could not read block ... in file "...": read only 0 of 8192 bytes' again

Maxim Boguk writes: > On Tue, Feb 21, 2012 at 1:46 PM, Maxim Boguk wrote: >> There isn't possibility but close to 100% new inserted values were matched >> a previously-existing primary >> key value. >> The table is hand-made 'materialyzed view'-type statistic table which is >> getting recalculate

Re: [GENERAL] Unfamous 'could not read block ... in file "...": read only 0 of 8192 bytes' again

I wrote: > OK, so that pretty much explains where the visible symptoms are coming > from: somehow, the table got truncated but its pkey index did not get > cleared out. So an insert creates an empty page zero, inserts a heap > tuple there, tries to insert an index entry. The btree code sees there

Re: [GENERAL] Unfamous 'could not read block ... in file "...": read only 0 of 8192 bytes' again

> > OK, so that pretty much explains where the visible symptoms are coming > from: somehow, the table got truncated but its pkey index did not get > cleared out. So an insert creates an empty page zero, inserts a heap > tuple there, tries to insert an index entry. The btree code sees there > is a

Re: [GENERAL] Unfamous 'could not read block ... in file "...": read only 0 of 8192 bytes' again

On Tue, Feb 21, 2012 at 3:32 PM, Tom Lane wrote: > I wrote: > > OK, so that pretty much explains where the visible symptoms are coming > > from: somehow, the table got truncated but its pkey index did not get > > cleared out. So an insert creates an empty page zero, inserts a heap > > tuple ther

Re: [GENERAL] Unfamous 'could not read block ... in file "...": read only 0 of 8192 bytes' again

On Tue, Feb 21, 2012 at 3:47 PM, Maxim Boguk wrote: > > > On Tue, Feb 21, 2012 at 3:32 PM, Tom Lane wrote: > >> I wrote: >> > OK, so that pretty much explains where the visible symptoms are coming >> > from: somehow, the table got truncated but its pkey index did not get >> > cleared out. So an

Re: [GENERAL] Unfamous 'could not read block ... in file "...": read only 0 of 8192 bytes' again

Maxim Boguk writes: > There is some funny results: > hh=# VACUUM verbose agency_statistics_old; > INFO: vacuuming "public.agency_statistics_old" > INFO: index "agency_statistics_pkey" now contains 0 row versions in 605 > pages > DETAIL: 0 index row versions were removed. Wow. That seems to b

Re: [GENERAL] Unfamous 'could not read block ... in file "...": read only 0 of 8192 bytes' again

On Tue, Feb 21, 2012 at 4:03 PM, Tom Lane wrote: > Maxim Boguk writes: > > There is some funny results: > > > hh=# VACUUM verbose agency_statistics_old; > > INFO: vacuuming "public.agency_statistics_old" > > INFO: index "agency_statistics_pkey" now contains 0 row versions in 605 > > pages > >

Re: [GENERAL] Unfamous 'could not read block ... in file "...": read only 0 of 8192 bytes' again

Maxim Boguk writes: > While I waiting for gdb/gcc on that server I had built pg_filedump on the > development server using same postgresql version and created pg_filedump of > the index file. > It can be downloaded there: > http://maximboguk.com/static/etc/agency_statistics_pkey.pg_filedump.gz If

Re: [GENERAL] Unfamous 'could not read block ... in file "...": read only 0 of 8192 bytes' again

On Tue, Feb 21, 2012 at 5:32 PM, Tom Lane wrote: > Maxim Boguk writes: > > While I waiting for gdb/gcc on that server I had built pg_filedump on the > > development server using same postgresql version and created pg_filedump > of > > the index file. > > It can be downloaded there: > > http://ma