Re: "cache lookup failed for type ####" when running unit tests

Hi Tom, Apologies for not giving more context/code however, your analysis is right on the spot. Thanks for the hint! I ended up closing and re-opening the connection between fixtures instead of persisting them which is what was happening before. While it doesn't fix the root cause of the issue (i

Re: WAL replay issue from 9.6.8 to 9.6.10

Hi, 2018-08-29 6:02 GMT+02:00 Dave Peticolas : > Hello, I'm seeing some issues with WAL replay on a test server running > 9.6.10 using WAL archived from a 9.6.8 primary server. It reliably PANICs > during replay with messages like so: > > WARNING: page 1209270272 of relation base/16422/47496599

Re: using a plpgsql function argument as a table column.

On Tuesday, August 28, 2018, Pavel Stehule wrote: > Hi > > 2018-08-29 7:09 GMT+02:00 Shaun Savage : > >> I have a table with many years as columns. y1976, y2077, .. , y2019,y2020 >> I want to dynamically return a column from a function. > > > Personally, your design is unahappy - against to ideas

Re: using a plpgsql function argument as a table column.

Hi 2018-08-29 7:09 GMT+02:00 Shaun Savage : > I have a table with many years as columns. y1976, y2077, .. , y2019,y2020 > I want to dynamically return a column from a function. > no - it is not possible - the functions should to return exact same set of columns. Teoretically you can use SETOF R

Re: using a plpgsql function argument as a table column.

On Tuesday, August 28, 2018, Shaun Savage wrote: > I want to dynamically return a column from a function. > Variable object identifiers requires executing dynamic SQL. See: https://www.postgresql.org/docs/10/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN David J.

using a plpgsql function argument as a table column.

I have a table with many years as columns. y1976, y2077, .. , y2019,y2020 I want to dynamically return a column from a function. select * from FUNCTION('y2016') . select t1.cola t1.colb, t1.colc, t2.y2016 from . Where t2.y2016 != 0; or if I select year y2012 I want FUNCTION('y2012')

Re: using a plpgsql function argument as a table column.

CREATE OR REPLACE FUNCTION test(year VARCHAR) RETURNS TABLE (agencycode INT, bureaucode INT, acctname VARCHAR, beacat VARCHAR, onoffbudget VARCHAR, val INT) AS $$ BEGIN RETURN QUERY SELECT t1.agencycode, t1.bureaucode, t1.acctcode, t2.beacat, t2.onoffbudget, t2.XX  FROM allnames AS t1 JOIN

Re: using a plpgsql function argument as a table column.

CREATE OR REPLACE FUNCTION test(year VARCHAR) RETURNS TABLE (agencycode INT, bureaucode INT, acctname VARCHAR, beacat VARCHAR, onoffbudget VARCHAR, val INT) AS $$ BEGIN RETURN QUERY SELECT t1.agencycode, t1.bureaucode, t1.acctcode, t2.beacat, t2.onoffbudget, t2.XX  FROM allnames AS t1 JOIN

Re: using a plpgsql function argument as a table column.

Off the top of my head, I think you could do this using dynamic (execute) SQL in a function. However, it is going to be messy, possibly slow and likely fragile. You would need to query the catalogue to get the column names in the table and then build the SQL dynamically 'on the fly'. Without havin

Re: using a plpgsql function argument as a table column.

Hello, Yes, you can :-) Using a functional that returns a table, like in the following example: http://www.postgresqltutorial.com/plpgsql-function-returns-a-table/ You will have to generate a dynamic sql statement for querying your table with the accurate WHERE clause. Like in this example: http

using a plpgsql function argument as a table column.

I have a table with many years as columns. y1976, y2077, .. , y2019,y2020 I want to dynamically return a column from a function. select * from FUNCTION('y2016') . select t1.cola t1.colb, t1.colc, t2.y2016 from . Where t2.y2016 != 0; or if I select year y2012 I want FUNCTION('y2012')

Re: WAL replay issue from 9.6.8 to 9.6.10

On 29 August 2018 06:26:06 CEST, Dave Peticolas wrote: >On Tue, Aug 28, 2018 at 9:21 PM Andreas Kretschmer > >wrote: > >> On 29 August 2018 06:02:45 CEST, Dave Peticolas >wrote: >> >Hello, I'm seeing some issues with WAL replay on a test server >running >> >9.6.10 using WAL archived from a 9.6.8

Re: WAL replay issue from 9.6.8 to 9.6.10

On Tue, Aug 28, 2018 at 9:21 PM Andreas Kretschmer wrote: > On 29 August 2018 06:02:45 CEST, Dave Peticolas wrote: > >Hello, I'm seeing some issues with WAL replay on a test server running > >9.6.10 using WAL archived from a 9.6.8 primary server. It reliably > > > > Can you tell us the wal-level

Re: WAL replay issue from 9.6.8 to 9.6.10

On 29 August 2018 06:02:45 CEST, Dave Peticolas wrote: >Hello, I'm seeing some issues with WAL replay on a test server running >9.6.10 using WAL archived from a 9.6.8 primary server. It reliably > Can you tell us the wal-level? Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company

WAL replay issue from 9.6.8 to 9.6.10

Hello, I'm seeing some issues with WAL replay on a test server running 9.6.10 using WAL archived from a 9.6.8 primary server. It reliably PANICs during replay with messages like so: WARNING: page 1209270272 of relation base/16422/47496599 does not exist CONTEXT: xlog redo at 4810/C84F8A0 for Btr

Re: archive items not in correct section order

Tim Clarke writes: > On 27/08/18 15:22, Tom Lane wrote: >> That's not supposed to happen. Can you create a test case, by any chance? > It wasn't the cross-tab/pivot, it was this materialized view: Hm, could I trouble you for a self-contained test case? I tried to flesh it out as attached, but

Re: Duplicating data folder without tablespace, for read access

To follow up, ZFS snapshots (appear to) offer a great solution to the problem I posed a couple of weeks ago, and avoid any hacking around with misuse of tablespaces. My goal was to have a database with a 100GB table and a 600GB table, and to routinely and efficiently clone the 100GB table and its

Re: archive items not in correct section order

On 27/08/18 15:22, Tom Lane wrote: >> That's not supposed to happen. Can you create a test case, by any chance? >> Presumably, it's triggered by some database schema change you made since >> the last successful dump. >> >> regards, tom lane >> It wasn't the cross-tab/pivot, i

Re: archive items not in correct section order

On 27/08/18 15:22, Tom Lane wrote: > Tim Clarke writes: >> Last night for the first time our pg_dump backup threw this error for >> the first time. Repeated runs this morning consistently throw it too. > That's not supposed to happen. Can you create a test case, by any chance? > Presumably, it's

Re: pg_basebackup + SSL error: bad length

On 08/28/2018 01:58 AM, Csaba Ragasits wrote: Hello, Only one copy is from the pg_basebackup on the machines. I have only a tablespace only a different location: /srv/pgdata/tbs_int303 I think I solved the problem: When I delete manually the /srv/pgdata/tbs_int303 folder, the pg_basebackup i

Re: Will there ever be support for Row Level Security on Materialized Views?

On 08/27/2018 07:38 PM, Ken Tanzer wrote: > On Mon, Aug 27, 2018 at 4:23 PM David Rowley > mailto:david.row...@2ndquadrant.com>> wrote: > >> If I had to guess what's going on here then I'd say that nobody has >> been sufficiently motivated to work on this yet. If that's the case, >> everyone who r

Re: Will there ever be support for Row Level Security on Materialized Views?

Greetings, * Dean Rasheed (dean.a.rash...@gmail.com) wrote: > On 28 August 2018 at 01:49, Alvaro Herrera wrote: > > On 2018-Aug-27, Ken Tanzer wrote: > >>- In the scheme of things, is it a lot of work or not so much? > > > > Probably not much. > > Yeah, it doesn't seem like it would be parti

Re: Granting a user the privilege to create views?

Hello Olivier, You can write an event trigger that will prevent specific user to create objects that are not views. Link to the CREATE EVENT TRIGGER documentation: https://www.postgresql.org/docs/current/static/sql-createeventtrigger.html Regards, Thomas Le mar. 28 août 2018 à 11:00, pavan95 a

Re: Granting a user the privilege to create views?

Hi Olivier, Instead if you grant select privilege on a table to a particular user, he can be able to create whatever the views he wants to. Regards, Pavan -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: pg_basebackup + SSL error: bad length

Hello, Only one copy is from the pg_basebackup on the machines. I have only a tablespace only a different location: /srv/pgdata/tbs_int303 I think I solved the problem: When I delete manually the /srv/pgdata/tbs_int303 folder, the pg_basebackup is running fine. Thank you for your help, Csaba

Granting a user the privilege to create views?

Hello dear list, Context: PostgreSQL instance, version 10.1 used for Datawarehousing. We have an ETL system loading lots of data on this instance, with all kind of business metrics. For most of the data, we're using a snowflake schema (https://en.wikipedia.org/wiki/Snowflake_schema

Re: Which background task looks for pg_xlog in 10?

Hi Johann, >>postgres postgres@template1 ERROR: could not open directory "pg_xlog": No such file or directory AFAIK, pg_xlog directory is changed to pg_wal in postgres 10. So pg_xlog directory can't be found. Did you connect to template1 database? Being a role model database for any newly cre

Re: Which background task looks for pg_xlog in 10?

Am 28.08.2018 um 08:32 schrieb Johann Spies: I see this in /var/log/postgresql/postgresql-10-main.log: postgres postgres@template1 ERROR: could not open directory "pg_xlog": No such file or directory wild guess: some outdated monitoring software. This directory is renamed to pg_wal. Re

Re: Will there ever be support for Row Level Security on Materialized Views?

On 28 August 2018 at 01:49, Alvaro Herrera wrote: > On 2018-Aug-27, Ken Tanzer wrote: >>- In the scheme of things, is it a lot of work or not so much? > > Probably not much. > Yeah, it doesn't seem like it would be particularly difficult, but it would probably still be a reasonable amount of

Re: How to search particular line/text code in all Postgres all database object's

why not dump them and grep inside? a command like this should help pg_dump -h localhost -U username -Fc -s -f db_dump dbName El lun., 27 de ago. de 2018 a la(s) 23:27, Raghavendra Rao J S V ( raghavendra...@gmail.com) escribió: > Hi All, > > How to search particular line/text code in all Postgre

Re: Which background task looks for pg_xlog in 10?

Hello On Tue, 2018-08-28 at 08:32 +0200, Johann Spies wrote: > I see this in /var/log/postgresql/postgresql-10-main.log: > > postgres postgres@template1 ERROR: could not open directory > "pg_xlog": No such file or directory > > postgres postgres@template1 STATEMENT: SELECT count(*) AS segments