Re: [GENERAL] view dependent on system view caused an upgrade to fail

"Hu, Patricia" writes: > I have the following function and view in my db: > create or replace function ${catalogSchema}.fn_show_pg_stat_activity() > returns setof pg_catalog.pg_stat_activity as $$ select * from > pg_catalog.pg_stat_activity; $$ language sql volatile

[GENERAL] view dependent on system view caused an upgrade to fail

I have the following function and view in my db: create or replace function ${catalogSchema}.fn_show_pg_stat_activity() returns setof pg_catalog.pg_stat_activity as $$ select * from pg_catalog.pg_stat_activity; $$ language sql volatile security definer; create or replace view

Re: [GENERAL] View not allowing to drop column (Bug or Feature enhancement )

Hi: On Mon, May 16, 2016 at 9:46 AM, Sachin Kotwal wrote: >> You need to drop the view before recreating it. Then it works. If you >> changed the access to the view with grants or revokes, you also neet to >> recreate them. They are dropped with the view. > Sorry to say but

Re: [GENERAL] View not allowing to drop column (Bug or Feature enhancement )

Hi, > > * >> * >> *While trying to drop a column by replacing view definition from view it >> throws an error saying cannot drop column from view.* >> *=* >> postgres=# create or replace view vi1 as select >> id , name from

Re: [GENERAL] View not allowing to drop column (Bug or Feature enhancement )

Hello Please post your answers to the list. You need to drop the view before recreating it. Then it works. If you changed the access to the view with grants or revokes, you also neet to recreate them. They are dropped with the view. Sorry to say but If we need to drop and replace

Re: [GENERAL] View not allowing to drop column (Bug or Feature enhancement )

On Mon, May 16, 2016 at 8:49 AM, Shrikant Bhende wrote: > While working on the view I came across an unusual behaviour of the view, > PostgreSQL do not allows to drop a column from the view, whereas same > pattern of Create and Replace view works while adding a

Re: [GENERAL] View not allowing to drop column (Bug or Feature enhancement )

Hello On 05/16/2016 08:49 AM, Shrikant Bhende wrote: Hi all, While working on the view I came across an unusual behaviour of the view, PostgreSQL do not allows to drop a column from the view, whereas same pattern of Create and Replace view works while adding a column. Please find below test

[GENERAL] View not allowing to drop column (Bug or Feature enhancement )

Hi all, While working on the view I came across an unusual behaviour of the view, PostgreSQL do not allows to drop a column from the view, whereas same pattern of Create and Replace view works while adding a column. Please find below test for the same. *Version info * *===* postgres=#

Re: [GENERAL] View deleted records in a table

On 03/26/2016 12:47 PM, Boyan Botev wrote: Thanks, Adrian! That worked great for what I needed. I greatly appreciate your help. Do you know if there is a way to also display system columns like xmin, xmax with this extension. I can see the need for that in some future investigation. You are

Re: [GENERAL] View deleted records in a table

Thanks, Adrian! That worked great for what I needed. I greatly appreciate your help. Do you know if there is a way to also display system columns like xmin, xmax with this extension. I can see the need for that in some future investigation. Thanks, Boyan On Sat, Mar 26, 2016 at 1:45 PM, Adrian

Re: [GENERAL] View deleted records in a table

That works only if I know I will need the deleted/updated records for a table ahead of time. I need this feature/ability as a way to perform "data forensics" on a random table that someone has done something to. Next time it may be another table. Basically I need a way to remove the visibility map

Re: [GENERAL] View deleted records in a table

On 03/26/2016 10:32 AM, Boyan Botev wrote: I want to view deleted records in table from a week ago in order to troubleshoot a data issue. The table has not been vacuumed yet. I was Are you sure? In other words do you have autovacuum turned off? Autovacuum is

Re: [GENERAL] View deleted records in a table

On Sat, Mar 26, 2016 at 12:23 PM, Adrian Klaver wrote: > On 03/26/2016 09:08 AM, Boyan Botev wrote: > >> I want to view deleted records in table from a week ago in order to >> troubleshoot a data issue. The table has not been vacuumed yet. I was >> > > Are you sure? >

Re: [GENERAL] View deleted records in a table

> > > I want to view deleted records in table from a week ago in order to >> troubleshoot a data issue. The table has not been vacuumed yet. I was >> > > Are you sure? > In other words do you have autovacuum turned off? Autovacuum is on. The table is fairly static and the last vacuum analyze was

Re: [GENERAL] View deleted records in a table

On 03/26/2016 09:08 AM, Boyan Botev wrote: I want to view deleted records in table from a week ago in order to troubleshoot a data issue. The table has not been vacuumed yet. I was Are you sure? In other words do you have autovacuum turned off? trying to use the pageinspect v1.4 extension

[GENERAL] View deleted records in a table

I want to view deleted records in table from a week ago in order to troubleshoot a data issue. The table has not been vacuumed yet. I was trying to use the pageinspect v1.4 extension but can't seem to convert the t_data to a readable record. Is there an easy way for me to get a SQL to produce all

Re: [GENERAL] view

>ERROR: permission denied for relation sub_item So obviously, the user doing the select on the view (ie: YOU) does not have SELECT permission on table sub_item. On Tue, Sep 8, 2015 at 8:19 AM, Ramesh T wrote: > HI , > I have view .when i try to select

[GENERAL] view

HI , I have view .when i try to select view ,it had return select * from art; ERROR: permission denied for relation sub_item SQL state: 42501

Re: [GENERAL] view

On 09/08/2015 06:44 AM, Melvin Davidson wrote: >ERROR: permission denied for relation sub_item So obviously, the user doing the select on the view (ie: YOU) does not have SELECT permission on table sub_item. It is more subtle then that: test=# select current_user; current_user

Re: [GENERAL] view

On 09/08/2015 05:19 AM, Ramesh T wrote: HI , I have view .when i try to select view ,it had return select * from art; ERROR: permission denied for relation sub_item Pretty self-explanatory, the user running the query does not have rights on sub_item, which I presume is part of the

[GENERAL] View 'instead of' update row with new object

I am partitioning a number of tables in our system and am using a view on the real master table with 'instead of’ triggers to insert and update rows. I have used a view because my application uses Hibernate which does not work well with triggers that return a null to indicate no further

[GENERAL] View permission error after upgrading from 8.4 - 9.2

I'm upgrading our database from 8.4 to 9.2 and I've run across a view that is no longer working.   When selecting from the view, I get a permission denied error on one of the referenced tables.   However, I can run the view's query directly without problems and I have read access to all the

Re: [GENERAL] View permission error after upgrading from 8.4 - 9.2

Brian Hirt bh...@me.com writes: I'm upgrading our database from 8.4 to 9.2 and I've run across a view that is no longer working.   When selecting from the view, I get a permission denied error on one of the referenced tables.   However, I can run the view's query directly without problems

Re: [GENERAL] View permission error after upgrading from 8.4 - 9.2

None of the relations used by vcredit_info are views.   They are all tables.   Oddly, I dropped the view and recreated it and the problem went away.  Earlier I was just using create or replace view and the problem persisted.   The schema was created by using pg_restore from an 8.4 custom dump.

Re: [GENERAL] View permission error after upgrading from 8.4 - 9.2

Brian Hirt bh...@me.com writes: None of the relations used by vcredit_info are views.   They are all tables.   Oddly, I dropped the view and recreated it and the problem went away.  Earlier I was just using create or replace view and the problem persisted. Hm. I think that CREATE OR

[GENERAL] View to show privileges on views/tables/sequences/foreign tables

A while back I was looking for a way to display object privileges quickly with a bit better readibility. The following view is what I came up with. Suggestions and improvements welcome (or comments stating that there are much easi\er ways to get the same details). (was created in a utility admin

Re: [GENERAL] View to show privileges on views/tables/sequences/foreign tables

On Thu, Feb 21, 2013 at 9:38 AM, bricklen brick...@gmail.com wrote: A while back I was looking for a way to display object privileges quickly with a bit better readibility. The following view is what I came up with. Suggestions and improvements welcome (or comments stating that there are much

[GENERAL] view with insert rule

Hello, I use some views for creating some different accessibility of the table data. For inserting I use a rule, which works at the moment, but my rules runs the SQL statement insert into myschema.mytable values (new.*) If I run on my view the command: insert into myview (field1, field4) values

Re: [GENERAL] view with insert rule

Philipp Kraus philipp.kr...@flashpixx.de writes: Can I create a view, that can be used like a table, so on insert statements the default values are set? Sure. Just use ALTER VIEW SET DEFAULT to attach default values to the columns of the view. regards, tom lane --

Re: [GENERAL] View using dblink fails if not able to make connection

- Original Message - From: Igor Romanchenko On Fri, Nov 9, 2012 at 9:21 PM, George Weaver gwea...@shaw.ca wrote: Hi Everyone, I have a view made up of a local query unioned with a view comprised of a dblink query. If the dblink query cannot establish a connection, I get the could

[GENERAL] View using dblink fails if not able to make connection

Hi Everyone, I have a view made up of a local query unioned with a view comprised of a dblink query. If the dblink query cannot establish a connection, I get the could not connect to server error and the whole view fails. Setting fail_on_error to false does not work in this situation. Is

Re: [GENERAL] View using dblink fails if not able to make connection

On Fri, Nov 9, 2012 at 9:21 PM, George Weaver gwea...@shaw.ca wrote: ** Hi Everyone, I have a view made up of a local query unioned with a view comprised of a dblink query. If the dblink query cannot establish a connection, I get the could not connect to server error and the whole view

Re: [GENERAL] View definition and schema search path bug or expected behaviour?

On 07/25/2012 08:19 PM, Chris Bartlett wrote: Ah! The bob schema has no access privileges set. I had used pgAdmin3 - the schema definition pgAdmin3 reports is: CREATE SCHEMA bob AUTHORIZATION bob; (i.e., no grants) So: GRANT ALL ON SCHEMA bob TO bob; DROP VIEW bob.people; CREATE OR REPLACE

[GENERAL] View definition and schema search path bug or expected behaviour?

I am not sure if this is expected behaviour or a bug. Using PG 9.2 beta 2 and PGAdmin3 1.16 beta 2. Connect as bob (superuser) In public schema: create table people (cols...) create view people_view as select * from people Create schema bob create table bob.people (cols...) create

Re: [GENERAL] View definition and schema search path bug or expected behaviour?

On 07/25/2012 07:25 PM, Chris Bartlett wrote: I am not sure if this is expected behaviour or a bug. Using PG 9.2 beta 2 and PGAdmin3 1.16 beta 2. Connect as bob (superuser) In public schema: create table people (cols...) create view people_view as select * from people Create schema bob

Re: [GENERAL] View definition and schema search path bug or expected behaviour?

At 7:37 PM -0700 25/7/12, Adrian Klaver wrote: I am guessing if you do show search_path; from psql you will see that the public schema is before the bob schema. The SELECT for the unqualified people table in CREATE VIEW bob.people_view will find public.people first in that case. I don't

Re: [GENERAL] View definition and schema search path bug or expected behaviour?

On 07/25/2012 07:47 PM, Chris Bartlett wrote: At 7:37 PM -0700 25/7/12, Adrian Klaver wrote: I am guessing if you do show search_path; from psql you will see that the public schema is before the bob schema. The SELECT for the unqualified people table in CREATE VIEW bob.people_view will find

Re: [GENERAL] View definition and schema search path bug or expected behaviour?

At 7:51 PM -0700 25/7/12, Adrian Klaver wrote: On 07/25/2012 07:47 PM, Chris Bartlett wrote: At 7:37 PM -0700 25/7/12, Adrian Klaver wrote: I am guessing if you do show search_path; from psql you will see that the public schema is before the bob schema. The SELECT for the unqualified people

Re: [GENERAL] View parsing

Hi, how about this one? SELECT n.nspname, c.relname, a.attname FROM pg_depend d INNER JOIN pg_class c ON (c.oid = refobjid) INNER JOIN pg_attribute a ON (c.oid = a.attrelid AND d.refobjsubid = a.attnum) INNER JOIN pg_namespace n ON (c.relnamespace = n.oid) INNER JOIN pg_rewrite rw ON

[GENERAL] View parsing

Hi all, I would like to be able to determine which tables each field of a view comes from. I have a view definition like SELECT tbl_a.fld_a, tbl_b.fld_b AS fld_e, function(c,d,f) as fld_c, (SELECT fld_d FROM tbl_d WHERE tbl_d.fld_e=tbl_a.fld_a) as fld_d FROM tbl_a inner join tbl_b on

[GENERAL] VIEW still referring to old name of field

Hi, This is a case where I changed the name of a field in a table that a VIEW referred to, but the VIEW definition still points to the old name of the field. The surprise is that the VIEW still works (with live data). Excerpt from psql (v9.1.2) given below. The problem came up when I took a

Re: [GENERAL] VIEW still referring to old name of field

Robins Tharakan robins.thara...@gmail.com writes: This is a case where I changed the name of a field in a table that a VIEW referred to, but the VIEW definition still points to the old name of the field. The surprise is that the VIEW still works (with live data). Specifically, you mean that

Re: [GENERAL] VIEW still referring to old name of field

Thanks Tom. I get it. Putting it in another way, if there was a function and a VIEW and this field name were to be changed, then we'd have a broken function anyway. The only issue is that (before writing this mail) I expected that a VIEW would either throw up errors or would work without fail

[GENERAL] View Caching - Is this Known and Expected Behavior?

Hey All, I am wondering whether the behavior I am observing is expected. The rough scenario I have setup goes as follows (I can likely put together a test script if that is warranted): version PostgreSQL 9.0.3 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5)

Re: [GENERAL] View Caching - Is this Known and Expected Behavior?

David Johnston pol...@yahoo.com writes: I am wondering whether the behavior I am observing is expected. No, it isn't. Please provide a concrete test case. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your

Re: [GENERAL] View Caching - Is this Known and Expected Behavior?

On Tue, Aug 23, 2011 at 4:36 PM, David Johnston pol...@yahoo.com wrote: Hey All, I am wondering whether the behavior I am observing is expected.  The rough scenario I have setup goes as follows (I can likely put together a test script if that is warranted): version PostgreSQL 9.0.3 on

Re: [GENERAL] View Caching - Is this Known and Expected Behavior?

-Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Tuesday, August 23, 2011 5:51 PM To: David Johnston Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] View Caching - Is this Known and Expected Behavior? David Johnston pol...@yahoo.com writes: I am wondering

Re: [GENERAL] view table pkey values

Thanks for the thorough explanation and link to more docs. Very much appreciated! Scott On Aug 24, 2009, at 11:03 AM, Raymond O'Donnell wrote: On 24/08/2009 18:37, Scott Frankel wrote: If I understand how tables are managed internally, there are 2 sequences: my explicit foo_id and

[GENERAL] view table pkey values

Hello, Is it possible to select or otherwise view a table's primary key values? I'm troubleshooting the following error: ERROR: duplicate key value violates unique constraint foo_pkey The insert that yields the error seems innocuous enough: INSERT INTO foo (color_id,

Re: [GENERAL] view table pkey values

On 24/08/2009 17:31, Scott Frankel wrote: Is it possible to select or otherwise view a table's primary key values? [snip] CREATE TABLE foo ( foo_idSERIALPRIMARY KEY, select foo_id from foo; ? or am I missing something? Ray.

Re: [GENERAL] view table pkey values

On 24/08/2009 17:31, Scott Frankel wrote: The insert that yields the error seems innocuous enough: INSERT INTO foo (color_id, ordinal, person_id) VALUES (1, 1019, 2); It seems as if there's a sequence (foo_pkey) that's got some weird values in it. The table itself looks like this:

Re: [GENERAL] view table pkey values

Hi Ray, On Aug 24, 2009, at 9:48 AM, Raymond O'Donnell wrote: On 24/08/2009 17:31, Scott Frankel wrote: The insert that yields the error seems innocuous enough: INSERT INTO foo (color_id, ordinal, person_id) VALUES (1, 1019, 2); It seems as if there's a sequence (foo_pkey) that's

Re: [GENERAL] view table pkey values

Scott Frankel lekn...@pacbell.net writes: Is there a command that lists the values for the internal, foo_foo_id_seq, sequence? select * from foo_foo_id_seq; The usual way to get into this sort of trouble is to load a bunch of data into the table while explicitly specifying ID values. It

Re: [GENERAL] view table pkey values

Got it! Yes, this started happening after loading from a pg_dump. Thanks for the explanation! Scott On Aug 24, 2009, at 10:52 AM, Tom Lane wrote: Scott Frankel lekn...@pacbell.net writes: Is there a command that lists the values for the internal, foo_foo_id_seq, sequence? select *

Re: [GENERAL] view table pkey values

On 24/08/2009 18:37, Scott Frankel wrote: If I understand how tables are managed internally, there are 2 sequences: my explicit foo_id and the internal sequence foo_foo_id_seq: public | foo_foo_id_seq | sequence | pguser | It's this internal sequence that must be involved in the

Re: [GENERAL] View vs Constantly Updated Table

How often are you using the bank balance value? I have no data on this as of yet, but it obviously needs to be correct for when the user looks at it. The opposite argument, is how long does the computation take? The computation is simple, however the amount of data that goes into it

Re: [GENERAL] View vs Constantly Updated Table

It is generally better to save the balance. The general rule in accounting systems design is what can be printed, should be explicitly on disk. for an invoice: value before tax, tax percentage, value after tax, total before tax, total after tax, etc, should all be saved explicitly. An account

[GENERAL] View vs Constantly Updated Table

if i have a column that is a calculation, say a bank balance - sum of all the debits and credits...is it more efficient to make a view that executes the underlying calc query doing the math, or to create a table that has a column called balance that is updated for each transaction? so in

Re: [GENERAL] View vs Constantly Updated Table

On 15/12/2008 16:14, Ketema Harris wrote: if i have a column that is a calculation, say a bank balance - sum of all the debits and credits...is it more efficient to make a view that executes the underlying calc query doing the math, or to create a table that has a column called balance that is

Re: [GENERAL] View vs Constantly Updated Table

if i have a column that is a calculation, say a bank balance - sum of all the debits and credits...is it more efficient to make a view that executes the underlying calc query doing the math, or to create a table that has a column called balance that is updated for each transaction? so in

Re: [GENERAL] View vs Constantly Updated Table

On Dec 15, 11:25 am, r...@iol.ie (Raymond O'Donnell) wrote: On 15/12/2008 16:14, Ketema Harris wrote: if i have a column that is a calculation, say a bank balance - sum of all the debits and credits...is it more efficient to make a view that executes the underlying calc query doing the

[GENERAL] view row-level locks

Hi, This seems like it must be a common question, but Google can't find the answer for me. How do I view the currently open row-level locks? The manual says that the table pg_locks doesn't contain row-level locks, since they are stored on disk rather than in memory, but it doesn't say how one

Re: [GENERAL] view row-level locks

Michael Shulman wrote: Hi, This seems like it must be a common question, but Google can't find the answer for me. How do I view the currently open row-level locks? The manual says that the table pg_locks doesn't contain row-level locks, since they are stored on disk rather than in memory, but

Re: [GENERAL] view row-level locks

On Jul 11, 2008, at 4:24 AM, Richard Huxton wrote: If you just want to see if a lock has been taken (e.g. SELECT FOR UPDATE) then that shows in pg_locks. If you want details on the actual rows involved, then you probably want pgrowlocks mentioned in Appendix F. Additional Supplied

Re: [GENERAL] view row-level locks

Vivek Khera [EMAIL PROTECTED] writes: On Jul 11, 2008, at 4:24 AM, Richard Huxton wrote: If you just want to see if a lock has been taken (e.g. SELECT FOR UPDATE) then that shows in pg_locks. If you want details on the actual rows involved, then you probably want pgrowlocks mentioned in

[GENERAL] View fields are cast as text and link to Access as a Memo field

Hi, I have a table CREATE TABLE HR.Participant_Names ( PIP int4 NOT NULL, LastNAME varchar(32) NOT NULL, FirstName varchar(20) NOT NULL, NameUsed varchar(20), CONSTRAINT pkey_PIP PRIMARY KEY (PIP), ) WITHOUT OIDS; and a view CREATE OR REPLACE VIEW HR.tvw_EmployeeNames_ActiveAndInactive

Re: [GENERAL] View fields are cast as text and link to Access as a Memo field

Karen Springer [EMAIL PROTECTED] writes: I need employeeName to be a text field in Access. I have tried casting the fields in the view as varchar, but it seem to default back to ::text. The result of a || operator is always going to be type text. Put the cast around the whole expression, not

Re: [GENERAL] view management

Em Friday 16 November 2007 18:57:24 Ed L. escreveu: I often have need for views that reference views that reference views, and so on. When I need to make a small update to one of the views, I am faced with having to drop and recreate all dependent views even if the driving change just adds

Re: [GENERAL] view management

On Nov 16, 2007 4:02 PM, Ed L. [EMAIL PROTECTED] wrote: On Friday 16 November 2007 1:57 pm, Ed L. wrote: I have a question about view management... I often have need for views that reference views that reference views, and so on. When I need to make a small update to one of the views,

[GENERAL] view management

I have a question about view management... I often have need for views that reference views that reference views, and so on. When I need to make a small update to one of the views, I am faced with having to drop and recreate all dependent views even if the driving change just adds another

Re: [GENERAL] view management

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Fri, 16 Nov 2007 13:57:24 -0700 Ed L. [EMAIL PROTECTED] wrote: I have a question about view management... I often have need for views that reference views that reference views, and so on. When I need to make a small update to one of the

Re: [GENERAL] view management

On Friday 16 November 2007 1:57 pm, Ed L. wrote: I have a question about view management... I often have need for views that reference views that reference views, and so on. When I need to make a small update to one of the views, I am faced with having to drop and recreate all dependent

Re: [GENERAL] view management

On Nov 16, 2007 4:01 PM, Joshua D. Drake [EMAIL PROTECTED] wrote: On Fri, 16 Nov 2007 13:57:24 -0700 Ed L. [EMAIL PROTECTED] wrote: I have a question about view management... I often have need for views that reference views that reference views, and so on. When I need to make a small

Re: [GENERAL] view management

On Fri, Nov 16, 2007 at 02:02:37PM -0700, Ed L. wrote: subtree of view dependencies just to change one minor aspect of an independent view. Well, it's not independent, if other things depend on it, is it? :) A -- Andrew Sullivan Old sigs will return after re-constitution of blue smoke

Re: [GENERAL] view management

On Fri, Nov 16, 2007 at 02:43:01PM -0700, Ed L. wrote: That looks about as ugly as can be. Ugh. What it appears to boil down to is that views become unusable unless you are willing to invest the effort in a complex build system. The DB You're kidding, right? You don't think that a build

Re: [GENERAL] view management

On Friday 16 November 2007 2:09 pm, Merlin Moncure wrote: you have to rig a build system.  if you have a lot of views (which is good), and keeping them up to date is a pain, you have to automate their creation. simplest way to do that is to rig a build system around sql scripts.  when you

Re: [GENERAL] view management

On Nov 16, 2007 3:43 PM, Ed L. [EMAIL PROTECTED] wrote: That looks about as ugly as can be. Ugh. What it appears to boil down to is that views become unusable unless you are willing to invest the effort in a complex build system. The DB should handle this issue automatically. Does Oracle?

Re: [GENERAL] view management

On Nov 16, 2007 4:43 PM, Ed L. [EMAIL PROTECTED] wrote: On Friday 16 November 2007 2:09 pm, Merlin Moncure wrote: you have to rig a build system. if you have a lot of views (which is good), and keeping them up to date is a pain, you have to automate their creation. simplest way to do that

Re: [GENERAL] view management

On Friday 16 November 2007 2:48 pm, Scott Marlowe wrote: On Nov 16, 2007 3:43 PM, Ed L. [EMAIL PROTECTED] wrote: That looks about as ugly as can be. Ugh. What it appears to boil down to is that views become unusable unless you are willing to invest the effort in a complex build system.

Re: [GENERAL] view management

Ed L. wrote: On Friday 16 November 2007 2:48 pm, Scott Marlowe wrote: On Nov 16, 2007 3:43 PM, Ed L. [EMAIL PROTECTED] wrote: That looks about as ugly as can be. Ugh. What it appears to boil down to is that views become unusable unless you are willing to invest the effort in a

Re: [GENERAL] view management

Thanks, Justin. On Friday 16 November 2007 4:38 pm, Justin Pasher wrote: We have a system that has quite a few views to access some of the data (although we purposely tried to avoid views that pulled from other view due to some performance issues), but when we had all of the view

[GENERAL] View Triggers

It seems that insertion triggers on views will/may be added in version 8.3. http://archives.postgresql.org/pgsql-general/2007-02/msg01466.php However, Tom mentioned that adding update and deletion triggers may be a bad idea: http://archives.postgresql.org/pgsql-general/2007-02/msg01466.php ...

[GENERAL] View optimization

Hello, I was wondering it there was a way to optimize a view. If my view contains 20 LEFT joins, my guessing is that each time I will make a select on it, the view will be completely recalculated. Is there a way to have it up and running all the time like a table would be ? Any advice here

Re: [GENERAL] View optimization

am Tue, dem 19.12.2006, um 13:32:10 +0900 mailte Richard Ollier folgendes: Hello, I was wondering it there was a way to optimize a view. If my view contains 20 LEFT joins, my guessing is that each time I will make a select on it, the view will be completely recalculated. Is there a way

[GENERAL] view creation question

I need a read only view that concatenates data from two tables. Basically both tables would need a simple query like Select name, description, date from Tasks; Select name, description, date from Issues; Is there some way to wrap these two independent queries in a CREATE VIEW myview AS

Re: [GENERAL] view creation question

On Thu, Mar 09, 2006 at 10:55:12AM -0500, Larry White wrote: I need a read only view that concatenates data from two tables. Basically both tables would need a simple query like Select name, description, date from Tasks; Select name, description, date from Issues; Is there some way to

Re: [GENERAL] view creation question

On Mar 10, 2006, at 0:55 , Larry White wrote: Is there some way to wrap these two independent queries in a CREATE VIEW myview AS statement? CREATE VIEW myview AS Select name, description, date from Tasks UNION Select name, description, date from Issues; Michael Glaesemann grzm myrealbox

Re: [GENERAL] view creation question

Larry White wrote: I need a read only view that concatenates data from two tables. Basically both tables would need a simple query like Select name, description, date from Tasks; Select name, description, date from Issues; Is there some way to wrap these two independent queries in a CREATE

Re: [GENERAL] view creation question

In addition to the other replies (regarding UNION), are you aware that you can use inheritance in postgres to factor out common fields ? Then you could use the parent table to access the common denominator of the children. I'm not sure though if this fits your needs, just worth mentioning. See

Re: [GENERAL] view creation question

Richard Huxton wrote: Larry White wrote: SELECT * FROM Tasks UNION ALL SELECT * FROM Issues; In case you care about where a record originated from: SELECT *, 'Tasks' AS source FROM Tasks UNION ALL SELECT *, 'Issues' AS source FROM Issues; Regards, -- Alban Hertroys [EMAIL PROTECTED]

Re: [GENERAL] view or index to optimize performance

On Wed, Dec 21, 2005 at 10:49:29PM +0100, Klein Bal?zs wrote: I thought that if I used a view to retrieve data its content might be cached so it would make the query faster. No. A view is essentially exactly the same as inserting the view definition into the query that's using it. IE: CREATE

Re: [GENERAL] view or index to optimize performance

thanks for the help What you could do is partition the table so that critical information is stored in a smaller table while everything else goes to a larger table. I was thinking the other way round - maybe I can split the large table by creating a materialized view. But than I read that it is

Re: [GENERAL] view or index to optimize performance

=?iso-8859-1?Q?Klein_Bal=E1zs?= [EMAIL PROTECTED] writes: But later in the same blog it seems to indicate that there is a choice to either use or not use the query cache of pg. Hm? There is no query cache in PG. regards, tom lane ---(end of

[GENERAL] view or index to optimize performance

Hello everybody, I have a table that stores responses to questions in different questionnaires. This table will grow to millions of rows. My problem is that while most of the data in the table are rarely used in queries one type of response will be used quite often: biodata - name, gender,

Re: [GENERAL] view or index to optimize performance

Am Mittwoch, 21. Dezember 2005 21:27 schrieb Klein Balázs: Or should I instead create a view that contains only the biodata and select from that? But will postgres use the indexes than? Would that be a better approach? Whether the query is executed by a view or typed in in its full form by

Re: [GENERAL] view or index to optimize performance

I thought that if I used a view to retrieve data its content might be cached so it would make the query faster. Am Mittwoch, 21. Dezember 2005 21:27 schrieb Klein Balázs: Or should I instead create a view that contains only the biodata and select from that? But will postgres use the indexes

Re: [GENERAL] view or index to optimize performance

Klein Balázs wrote: Hello everybody, I have a table that stores responses to questions in different questionnaires. This table will grow to millions of rows. [snip] I can store the fact that it is biodata in the bio field - it is biodata if the value of that field is not 0 and I can index

Re: [GENERAL] View with an outer join - is there any way to optimise

Tom Lane wrote: Rich Doughty [EMAIL PROTECTED] writes: I have a view vw_tokens defined as ... I cannot however perform a meaningful join against this view. ... PG forms the full output of the view. You seem to be wishing that PG would push the INNER JOIN down inside the nested LEFT JOINs.

[GENERAL] View with an outer join - is there any way to optimise this?

I have a view vw_tokens defined as CREATE VIEW tokens.vw_tokens AS SELECT -- too many columns to mention FROM tokens.ta_tokens t LEFT JOIN tokens.ta_tokenhist i ON t.token_id = i.token_id AND i.status = 'issued' LEFT JOIN

Re: [GENERAL] View with an outer join - is there any way to optimise

John McCawley wrote: First of all, check out this thread: http://archives.postgresql.org/pgsql-general/2005-11/msg00734.php I had a similar performance issue with a view. Look at my solution and it might help you out. i'm not sure that'll help in my case as ta_tokens has a 1-to-many

  1   2   >