ProjectSet

[Oliver Kohll]

Hi,

I've just been running explain analyze on a slow query and came across
something new to me - a node type of ProjectSet in the plan. What is that?

The plan is here: https://explain.depesz.com/s/4rqA

The query does a cross join of two tables to get every row in one combined
with every row in the other, then runs the similarity function to compare
text in them. The full query is below, apologies for the different
obfuscation to the plan, if that's an issue let me know.

Is there an explanation of ProjectSet anywhere? I can't see one with a
quick google or search of these archives.

Cheers
Oliver

---

SELECT table1.id,
similarity(table2.field1::text, regexp_matches(table1.field3::text,
'product.ame:s*([^-]*)'::text, 'g'::text)::character
varying(10)::text)::double precision AS similarityscore,
table1.ourid_g3a11eruac8ct55b,
regexp_matches(table1.field3::text, 'product.ame:s*([^-]*)'::text,
'g'::text)::character varying(10) AS products,
table2.field1,
table2.field2,
table2.abaccount
   FROM table1,
table2
  WHERE lower(table2.statusofingredient::text) < '6'::text AND
lower(table2.statusofproduct::text) < '5'::text AND table1.fsacreated >=
(date_trunc('day'::text, now()) - '30 days'::interval) AND
lower(table2.bought::text) = 'bought'::text
  ORDER BY table1.id DESC NULLS LAST;

Re: Adding terminal title support for psqlrc

[ik]

Thank you, it works :)

https://github.com/ik5/dotfiles/blob/master/psqlrc

On Wed, Aug 1, 2018 at 9:15 PM, Adrian Klaver 
wrote:

> On 08/01/2018 10:55 AM, ik wrote:
>
>> hi,
>>
>> Thank you, it does not effect the terminal title.
>>
>> As Adrian answered me, it is not supported :/
>>
>
> I was wrong:
>
> https://petereisentraut.blogspot.com/2009/09/how-to-set-
> xterm-titles-in-psql.html
>
>
> To get this to work in Konsole I had to edit the profile to have Tab title
> format use %w (Window title set by shell).
>
>
>
>>
>>
>> On Wed, Aug 1, 2018 at 4:47 PM, Tom Lane > t...@sss.pgh.pa.us>> wrote:
>>
>> ik mailto:ido...@gmail.com>> writes:
>> > Is there a way to add terminal title information for psqlrc that cn
>> display
>> > what schema I am, operation that is execute and stuff like that?
>>
>> Most terminal programs recognize escape sequences to set the window
>> title.  So you could set values for PROMPTn that cause updates in
>> the title.  There's an example in
>>
>> https://www.postgresql.org/docs/devel/static/app-psql.html#
>> APP-PSQL-PROMPTING
>> > APP-PSQL-PROMPTING>
>>
>> although it's just talking about a color change.
>>
>>  regards, tom lane
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: ProjectSet

[David Rowley]

On 2 August 2018 at 21:17, Oliver Kohll  wrote:
> Is there an explanation of ProjectSet anywhere?

Plan node types and what they each do are not very well documented
outside of the source code.

ProjectSet appears when the SELECT or ORDER BY clause of the query.
They basically just execute the set-returning function(s) for each
tuple until none of the functions return any more records.

Simple Example:

EXPLAIN SELECT generate_series(1,2);
   QUERY PLAN
-
 ProjectSet  (cost=0.00..5.02 rows=1000 width=4)
   ->  Result  (cost=0.00..0.01 rows=1 width=0)
(2 rows)

-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

ERROR: found xmin 54230249 from before relfrozenxid 61349053

[rihad]

Hi all. We see such errors on one of our own (non-system) tables on 
Postgres 10.3. They stop both automatic vacuum & automatic analyze not 
only on that table, but on all tables & databases (manual vacuuming 
works though). Luckily it's a small (but important) table - less than 
1000 rows. Any quick way of fixing it with no downtime? Thanks.

Re: Who and How is responsible for released installations packages and 3rd party packs? (e.g. on https://yum.postgresql.org/9.6/redhat/rhel-7.3-x86_64/)

[Adrian Klaver]

On 08/01/2018 10:53 PM, Alexandru Lazarev wrote:

Hi PG Community,

In my company I found that PG Installation on deployed OS Images are 
takne from here: https://yum.postgresql.org/9.6/redhat/rhel-7.3-x86_64/


We are using PG 9.6.5 or 9.6.7 + pgpool + plv8 + others

Some or RPMs for CentOS are taken from that URL (PG Installation, plv8).

My question is:
Who is building RPMs and uploading to that URL? What are the criteria to 
build one RPM or other and their versions?


https://www.postgresql.org/download/linux/redhat/

https://yum.postgresql.org/


Why I am asking:
I saw on URL there are PG 9.6.8 and 9.6.9 - Are there maintained only 
latest 2 build releases?


plv8 - there are versions 2.0.0-1 and 2.1.0, since latest plv8 are 
already 2.3.7 and latest for 2.1.X is 2.1.3 contating major fixes



Thanks,
AlexL



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: ProjectSet

[Oliver Kohll]

Ah thanks David, at least I know what it is now. I don't think I have any
set returning functions though will double check in case there's a joined
view that has one. Perhaps it could be the something to do with cross
product which similarly creates multiple rows on the right for each row on
the left side.

In any case, after deleting some obsolete rows, the plan seems to have
changed as the query now executes in a fraction of a second as opposed to
25 seconds.

Oliver

On 2 August 2018 at 13:21:32, David Rowley (david.row...@2ndquadrant.com)
wrote:

On 2 August 2018 at 21:17, Oliver Kohll  wrote:
> Is there an explanation of ProjectSet anywhere?

Plan node types and what they each do are not very well documented
outside of the source code.

ProjectSet appears when the SELECT or ORDER BY clause of the query.
They basically just execute the set-returning function(s) for each
tuple until none of the functions return any more records.

Simple Example:

EXPLAIN SELECT generate_series(1,2);
QUERY PLAN
-
ProjectSet (cost=0.00..5.02 rows=1000 width=4)
-> Result (cost=0.00..0.01 rows=1 width=0)
(2 rows)

-- 
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Re: Who and How is responsible for released installations packages and 3rd party packs? (e.g. on https://yum.postgresql.org/9.6/redhat/rhel-7.3-x86_64/)

[Alexandru Lazarev]

What about 3rd party libraries like plv8 - Who and How (based on which
criteria, which versions) build RPM and upload them there?

+ adding more relevant mail list

On Thu, Aug 2, 2018 at 3:47 PM, Adrian Klaver 
wrote:

> On 08/01/2018 10:53 PM, Alexandru Lazarev wrote:
>
>> Hi PG Community,
>>
>> In my company I found that PG Installation on deployed OS Images are
>> takne from here: https://yum.postgresql.org/9.6/redhat/rhel-7.3-x86_64/
>>
>> We are using PG 9.6.5 or 9.6.7 + pgpool + plv8 + others
>>
>> Some or RPMs for CentOS are taken from that URL (PG Installation, plv8).
>>
>> My question is:
>> Who is building RPMs and uploading to that URL? What are the criteria to
>> build one RPM or other and their versions?
>>
>
> https://www.postgresql.org/download/linux/redhat/
>
> https://yum.postgresql.org/
>
>
> Why I am asking:
>> I saw on URL there are PG 9.6.8 and 9.6.9 - Are there maintained only
>> latest 2 build releases?
>>
>> plv8 - there are versions 2.0.0-1 and 2.1.0, since latest plv8 are
>> already 2.3.7 and latest for 2.1.X is 2.1.3 contating major fixes
>>
>>
>> Thanks,
>> AlexL
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: ProjectSet

[David Rowley]

On 3 August 2018 at 01:16, Oliver Kohll  wrote:
> Ah thanks David, at least I know what it is now. I don't think I have any
> set returning functions though will double check in case there's a joined
> view that has one. Perhaps it could be the something to do with cross
> product which similarly creates multiple rows on the right for each row on
> the left side.

postgres=# select proretset from pg_proc where proname = 'regexp_matches';
 proretset
---
 t
 t
(2 rows)

-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Error couldn't not serialize data due to concurrent read write update

[Om Prakash Jaiswal]

Sent from Yahoo Mail on Android 
 
   - Forwarded Message - From: "Om Prakash Jaiswal" 
 To: "bucardo-gene...@bucardo.org" 
 Cc:  Sent: Thu, Aug 2, 2018 at 18:56 Subject: How 
stop acknowledgement of transmitted data  Hi I am running bucardo 4.4 version 
on postgresql 9.4.8  RHEL 7.3 64bit OS. It is geographically distributed nodes 
using as master master replication.Data are coming from sensors at 
200tracks/second. One track size 100bytes network bandwidth 50mbps. I am 
getting error could not serialize concurrent read/write update.On bucardo_track 
table concurrent update.I configured isolation level repeatable read and later 
serialize level in postgresql configuration file. Restarted postgresql also. 
Still I am getting similar error.I want to stop getting acknowledgement from 
bucardo.My aim is to Only transmit data.
Still bucardo is synchronising data, only failure transaction is not 
synchronising.Please help me.
Thanks in advance
RegardsOm Prakash,DBABangaloreMob 9035635787



Sent from Yahoo Mail on Android  

Re: ProjectSet

[Oliver Kohll]

Of course! Doh.

On 2 August 2018 at 14:27:02, David Rowley (david.row...@2ndquadrant.com)
wrote:

postgres=# select proretset from pg_proc where proname = 'regexp_matches';
proretset
---
t
t
(2 rows)

Re: ERROR: found xmin 54230249 from before relfrozenxid 61349053

[rihad]

On 08/02/2018 04:38 PM, rihad wrote:
Hi all. We see such errors on one of our own (non-system) tables on 
Postgres 10.3. They stop both automatic vacuum & automatic analyze not 
only on that table, but on all tables & databases (manual vacuuming 
works though). Luckily it's a small (but important) table - less than 
1000 rows. Any quick way of fixing it with no downtime? Thanks.




BTW, it's a materialized view, not a table. "refresh materialized view 
concurrently" is being run on it regularly, but apparently doesn't fix 
the problem.

Re: ERROR: found xmin 54230249 from before relfrozenxid 61349053

[rihad]

On 08/02/2018 05:34 PM, rihad wrote:

On 08/02/2018 04:38 PM, rihad wrote:
Hi all. We see such errors on one of our own (non-system) tables on 
Postgres 10.3. They stop both automatic vacuum & automatic analyze 
not only on that table, but on all tables & databases (manual 
vacuuming works though). Luckily it's a small (but important) table - 
less than 1000 rows. Any quick way of fixing it with no downtime? 
Thanks.




BTW, it's a materialized view, not a table. "refresh materialized view 
concurrently" is being run on it regularly, but apparently doesn't fix 
the problem.




Answering to myself: this probably relates to this issue:

https://www.postgresql.org/docs/9.4/static/release-9-4-17.html

 *

   Repair pg_upgrade's failure to preserve relfrozenxid for
   materialized views (Tom Lane, Andres Freund)

   This oversight could lead to data corruption in materialized views
   after an upgrade, manifesting as "could not access status of
   transaction" or "found xmin from before relfrozenxid" errors. The
   problem would be more likely to occur in seldom-refreshed
   materialized views, or ones that were maintained only with REFRESH
   MATERIALIZED VIEW CONCURRENTLY.

   If such corruption is observed, it can be repaired by refreshing the
   materialized view (without CONCURRENTLY).


Fixed on 2018-03-01, so the bug existed not only for 9.4.17, but for all 
major versions too.

List user who have access to schema

[Suresh Raja]

Hi All:

I'm looking for query which can list all users who have access to a
particular schema.
The user may be granted role, which is turn may have access to the schema.
If the schema name is sch1,

grant select on table sch1.tab1 to role_ro;
grant ALL on table sch1.tab1 to role_rw;

grant  role_ro to user1;
grant  role_rw to user2;

I'm looking for report like


Schema  | Role |Username

sch1role_ro  user1, user3
   role_rw  user2, user4


Thanks in advance,
-SR

Re: List user who have access to schema

[Tom Lane]

Suresh Raja  writes:
> I'm looking for query which can list all users who have access to a
> particular schema.

Something involving

SELECT ... FROM pg_user
  WHERE has_schema_privilege(usename, 'schema-of-interest', 'usage');

would probably be what you want.

regards, tom lane

Re: List user who have access to schema

[Suresh Raja]

yes ... how can i pass variable * to the function   has_schema_privilege(*,
'schema-of-interest', 'usage');

Thanks!

On Thu, Aug 2, 2018 at 12:58 PM, Tom Lane  wrote:

> Suresh Raja  writes:
> > I'm looking for query which can list all users who have access to a
> > particular schema.
>
> Something involving
>
> SELECT ... FROM pg_user
>   WHERE has_schema_privilege(usename, 'schema-of-interest', 'usage');
>
> would probably be what you want.
>
> regards, tom lane
>

Re: List user who have access to schema

[David G. Johnston]

On Thursday, August 2, 2018, Suresh Raja  wrote:

> yes ... how can i pass variable * to the function   has_schema_privilege(*,
> 'schema-of-interest', 'usage');
>

You cannot...you must execute the function once for every user, hence the
original query's from clause.

David J.

Re: List user who have access to schema

[Adrian Klaver]

On 08/02/2018 11:23 AM, Suresh Raja wrote:
yes ... how can i pass variable * to the function 
has_schema_privilege(*, 'schema-of-interest', 'usage');


PREPARE schema_user(varchar, varchar) AS SELECT * FROM pg_user
  WHERE has_schema_privilege($1, $2, 'create');

EXECUTE schema_user ('aklaver', 'public');

DEALLOCATE schema_user ;



Thanks!

On Thu, Aug 2, 2018 at 12:58 PM, Tom Lane > wrote:


Suresh Raja mailto:suresh.raja...@gmail.com>> writes:
 > I'm looking for query which can list all users who have access to a
 > particular schema.

Something involving

SELECT ... FROM pg_user
   WHERE has_schema_privilege(usename, 'schema-of-interest', 'usage');

would probably be what you want.

                         regards, tom lane





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: could not read block 0 in file : read only 0 of 8192 bytes when doing nasty on immutable index function

[Peter Geoghegan]

On Tue, Jul 31, 2018 at 9:00 PM, Andres Freund  wrote:
> I don't think that's particularly relevant. We should always register an
> invalidation before the relevant CommandCounterIncrement(), because that
> is what makes catalog changes visible, and therefore requires
> registering invalidations for coherency.

Fair enough. How about the attached revision?

-- 
Peter Geoghegan


v2-0001-Add-table-relcache-invalidation-to-index-builds.patch
Description: Binary data

Re: could not read block 0 in file : read only 0 of 8192 bytes when doing nasty on immutable index function

[Andres Freund]

Hi,

On 2018-08-02 13:00:16 -0700, Peter Geoghegan wrote:
> On Tue, Jul 31, 2018 at 9:00 PM, Andres Freund  wrote:
> > I don't think that's particularly relevant. We should always register an
> > invalidation before the relevant CommandCounterIncrement(), because that
> > is what makes catalog changes visible, and therefore requires
> > registering invalidations for coherency.
> 
> Fair enough. How about the attached revision?

Looks good to me, minus some optional nitpicks in the commit message
below.


> From 156be401c38f7884ed65641aa202b72b698f3e91 Mon Sep 17 00:00:00 2001
> From: Peter Geoghegan 
> Date: Tue, 31 Jul 2018 18:33:30 -0700
> Subject: [PATCH v2] Add table relcache invalidation to index builds.
> 
> It's necessary to make sure that owning tables have a relcache
> invalidation prior to advancing the command counter to make
> newly-entered catalog tuples for the index visible.  inval.c must be
> able to roll back the local caches in the event of transaction abort.
> There is only a problem when CREATE INDEX transactions abort, since
> there is a generic invalidation once we reach index_update_stats().

s/roll back/maintain consistency of/.  I'd also perhaps weaken the next
sentence, by adding a 'practically' or such, as a memory allocation
failure or such before the index build starts could cause this as well.

Greetings,

Andres Freund

FW: Pg_rewind cannot load history wal

[Richard Schmidt]

> Now once your master A can’t become slave of B.

Isn’t that the exact situation that pg_rewind should take care of?


This email and any attachments may contain confidential information. If you are 
not the intended recipient, your use or communication of the information is 
strictly prohibited. If you have received this message in error please notify 
MetService immediately.

Re: Who and How is responsible for released installations packages and 3rd party packs? (e.g. on https://yum.postgresql.org/9.6/redhat/rhel-7.3-x86_64/)

[Devrim Gündüz]

Hi,

On Thu, 2018-08-02 at 16:26 +0300, Alexandru Lazarev wrote:
> What about 3rd party libraries like plv8 - Who and How (based on which
> criteria, which versions) build RPM and upload them there?

Latest versions of PL/v8 does not build on RHEL/Fedora anymore, at least from
the package build point of view. RPMs are not supposed to download extra
dependencies from elsewhere.

Regards,
-- 
Devrim Gündüz
EnterpriseDB: https://www.enterprisedb.com
PostgreSQL Consultant, Red Hat Certified Engineer
Twitter: @DevrimGunduz , @DevrimGunduzTR


signature.asc
Description: This is a digitally signed message part