Re: Postgresql 10.3 , query never completes if LIMIT clause is specified and paralle processing is on

On 03/21/2018 08:44 PM, Alessandro Aste wrote: > Thanks for your reply Tomas.  The query just got stuck for forever.  I > observed no CPU spikes, it is currently running and I see 89 of the CPU > idle. I'm using a relative powerfull machine (120 GB ram, 56 CPU).   > That doesn't really answer th

Re: postgresql-10.3 on unbuntu-17.10 - how??

Or you compile it? That was going to be my next step.  But I don't think a simple compile from source would do because Ubuntu's package manager wouldn't be aware that Postgresql was now available to satisfy other packages' dependencies. So I would need to rebuild the Ubuntu source package.  I

Re: Prepared statements

> On Mar 21, 2018, at 2:09 PM, Tim Cross wrote: > > > a simple question I wasn't able to get a clear answer on > > It is general best practice to use prepared statements and parameters > rather than concatenated strings to build sql statements as mitigation > against SQL injection. However

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On Wed, Mar 21, 2018 at 1:38 PM, Jeremy Finzel wrote: > A server restart and upgrade to 9.5.12 (at the same time), as expected, made > the issue go away. Still doesn't give us any answers as to what happened or > if it would happen again! Thanks for the feeback. You may still want to use amchec

Re: Prepared statements

> For example, the planner may be able to > more easily recognise a statement and reuse an existing plan rather than > re-planning the query. This is a double edged sword. Reuse an existing plan can be bad in those cases where the data distribution is not suitable for the current plan. This has b

Re: JDBC connectivity issue

On 03/21/2018 01:56 PM, chris wrote: I did the re install not to change versions but to now know what version I am running My previous question was not as clear as should have been. So: 1) At some place in your software stack there is some sort of configuration that links your app via JDBC to

Prepared statements

a simple question I wasn't able to get a clear answer on It is general best practice to use prepared statements and parameters rather than concatenated strings to build sql statements as mitigation against SQL injection. However, in some databases I've used, there is also a performance advant

Re: postgresql-10.3 on unbuntu-17.10 - how??

On 03/21/2018 01:59 PM, Stuart McGraw wrote: On 03/21/2018 02:37 PM, Rob Sargent wrote: Thanks, I forgot that the older repos also received the pg-10 update. Unfortunately but no luck with Xenial either, slightly different but similar conflicts. My main motivation for updating to 10.3 was to be

Re: postgresql-10.3 on unbuntu-17.10 - how??

On 03/21/2018 02:38 PM, Adrian Klaver wrote: On 03/21/2018 01:31 PM, Stuart McGraw wrote: On 03/21/2018 12:14 PM, Adrian Klaver wrote: [...] It still seems to me that the best advice for using Postgresql on Ubuntu is to use the Ubuntu version of Postgresql if you don't need the latest version;

Re: postgresql-10.3 on unbuntu-17.10 - how??

On 03/21/2018 02:37 PM, Rob Sargent wrote: Thanks, I forgot that the older repos also received the pg-10 update. Unfortunately but no luck with Xenial either, slightly different but similar conflicts. My main motivation for updating to 10.3 was to be able to load data dumped from a 10.3 database

Re: JDBC connectivity issue

I did the re install not to change versions but to now know what version I am running On 03/21/2018 02:44 PM, Adrian Klaver wrote: On 03/21/2018 01:16 PM, chris wrote: I wasnt able to find what version we had installed so we went ahead and reinstalled it Maybe I am missing something, but if

Re: JDBC connectivity issue

On 03/21/2018 01:16 PM, chris wrote: I wasnt able to find what version we had installed so we went ahead and reinstalled it Maybe I am missing something, but if you could not find the version you where using how do you know installing a new driver actually changed the version you are using no

Re: postgresql-10.3 on unbuntu-17.10 - how??

On 03/21/2018 01:31 PM, Stuart McGraw wrote: On 03/21/2018 12:14 PM, Adrian Klaver wrote: On 03/21/2018 10:59 AM, Stuart McGraw wrote: On 03/21/2018 07:02 AM, Adrian Klaver wrote: On 03/20/2018 10:52 PM, Stuart McGraw wrote: [...] If it where me I would simplify the above for the moment to :

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On Tue, Mar 20, 2018 at 11:19 AM, Jeremy Finzel wrote: > > > On Mon, Mar 19, 2018 at 3:55 PM, Jeremy Finzel wrote: > >> >> >> On Mon, Mar 19, 2018 at 3:53 PM, Peter Geoghegan wrote: >> >>> On Mon, Mar 19, 2018 at 1:01 PM, Jeremy Finzel >>> wrote: >>> > SELECT heap_page_items(get_raw_page('pg_a

Re: postgresql-10.3 on unbuntu-17.10 - how??

Thanks, I forgot that the older repos also received the pg-10 update. Unfortunately but no luck with Xenial either, slightly different but similar conflicts. My main motivation for updating to 10.3 was to be able to load data dumped from a 10.3 database.  pg_restore complained about "unsupported

Re: postgresql-10.3 on unbuntu-17.10 - how??

On 03/21/2018 12:14 PM, Adrian Klaver wrote: On 03/21/2018 10:59 AM, Stuart McGraw wrote: On 03/21/2018 07:02 AM, Adrian Klaver wrote: On 03/20/2018 10:52 PM, Stuart McGraw wrote: [...] If it where me I would simplify the above for the moment to : apt-get install postgresql-10 Tried on a fr

Re: postgresql-10.3 on unbuntu-17.10 - how??

On 03/21/2018 11:12 AM, Vincenzo Romano wrote: On 03/20/2018 10:52 PM, Stuart McGraw wrote: [...] Is there any reason now not to conclude that the 10.3 bionic version is simply incompatible with Ubuntu-17.10 (at least without a lot more package wrangling chops than I have)? One can install pos

Re: Postgresql 10.3 , query never completes if LIMIT clause is specified and paralle processing is on

Thanks for your reply Tomas. The query just got stuck for forever. I observed no CPU spikes, it is currently running and I see 89 of the CPU idle. I'm using a relative powerfull machine (120 GB ram, 56 CPU). Plain analyze as requested. : QUERY PLAN --

Re: JDBC connectivity issue

I wasnt able to find what version we had installed so we went ahead and reinstalled it. we downloaded the current version JDBC 4.1 Driver 42.2.1.jre7 We are still having the same problem. Thanks On 03/14/2018 03:27 PM, Adrian Klaver wrote: On 03/14/2018 01:47 PM, chris wrote: Oh sorry f

Re: Postgresql 10.3 , query never completes if LIMIT clause is specified and paralle processing is on

On 03/21/2018 05:09 PM, Alessandro Aste wrote: > Hi there, we are using postgresql 10.3 and we're facing an issue with a > query. The query (full query below)  completes only  when:  > > 1 - LIMIT 10 is removed > or > 2 -  show max_parallel_workers_per_gather  is set to 0, so parallel > processi

Re: Foreign Key Validation after Reference Table Ownership Change

> On Mar 21, 2018, at 2:36 PM, David G. Johnston wrote: > > And altering an owner of a table to one lacking usage and create permissions > on the schema is possible but unadvisible. > > David J. Exactly. The cause of my mistake was changing the REFERENCED table ownership to a role without g

Postgresql 10.3 , query never completes if LIMIT clause is specified and paralle processing is on

Hi there, we are using postgresql 10.3 and we're facing an issue with a query. The query (full query below) completes only when: 1 - LIMIT 10 is removed or 2 - show max_parallel_workers_per_gather is set to 0, so parallel processing is disabled. With max_parallel_workers_per_gather set to

Foreign Key Validation after Reference Table Ownership Change

On Wednesday, March 21, 2018, Tom Lane wrote: > Louis Battuello writes: > >> The point is you can't resolve a name like "schema_1.something" unless > >> you have USAGE on schema_1. So the RI-checking query, which is run as > >> the owner of the table, fails at parse time. > > > That certainly m

Re: FDW Foreign Table Access: strange LOG message

On 03/21/2018 11:15 AM, Albrecht Dreß wrote: Hi Adrian & Tom: Thanks a lot for your input! Am 20.03.18 20:38 schrieb(en) Tom Lane: See comments inline below. Unfortunately, in my original post, I confused the primary and secondary (accessed via the FDW) data bases in my example when I anon

Re: FDW Foreign Table Access: strange LOG message

Hi Adrian & Tom: Thanks a lot for your input! Am 20.03.18 20:38 schrieb(en) Tom Lane: My suspicion is it has to do with this: postgres_fdw establishes a connection to a foreign server during the first query that uses a foreign table associated with the foreign server. This connection is kept

Re: postgresql-10.3 on unbuntu-17.10 - how??

On 03/21/2018 10:59 AM, Stuart McGraw wrote: On 03/21/2018 07:02 AM, Adrian Klaver wrote: On 03/20/2018 10:52 PM, Stuart McGraw wrote: Looks like these posts are coming through a news group to me. I am Ccing list to get response back there. Is this something I am doing wrong?  I am posting thr

Re: postgresql-10.3 on unbuntu-17.10 - how??

On 03/21/2018 07:02 AM, Adrian Klaver wrote: On 03/20/2018 10:52 PM, Stuart McGraw wrote: Looks like these posts are coming through a news group to me. I am Ccing list to get response back there. Is this something I am doing wrong? I am posting through the gmane newsgroup which in turn is bi-d

Re: Foreign Key Validation after Reference Table Ownership Change

Louis Battuello writes: >> The point is you can't resolve a name like "schema_1.something" unless >> you have USAGE on schema_1. So the RI-checking query, which is run as >> the owner of the table, fails at parse time. > That certainly makes sense for user_2 that owns the reference table and is

Re: Foreign Key Validation after Reference Table Ownership Change

On 03/21/2018 10:48 AM, Louis Battuello wrote: The point is you can't resolve a name like "schema_1.something" unless you have USAGE on schema_1. So the RI-checking query, which is run as the owner of the table, fails at parse time. That certainly makes sense for user_2 that owns the refere

Re: Foreign Key Validation after Reference Table Ownership Change

> > The point is you can't resolve a name like "schema_1.something" unless > you have USAGE on schema_1. So the RI-checking query, which is run as > the owner of the table, fails at parse time. That certainly makes sense for user_2 that owns the reference table and is blocked by not having usa

Re: Foreign Key Validation after Reference Table Ownership Change

"David G. Johnston" writes: > On Wed, Mar 21, 2018 at 8:30 AM, Battuello, Louis < > louis.battue...@etasseo.com> wrote: >> So, user_2 needs usage on the schema containing its newly owned reference >> table even though user_1 is performing the insert on a table in the other >> schema? Interesting.

Re: Foreign Key Validation after Reference Table Ownership Change

Agreed. It would certainly make sense that user_2 have usage on the schema in order to operate against the table owned by user_2. I just found it confusing that the discrepancy would cause an issue for user_1, which had all necessary privileges on the schema and references on the reference table

Re: postgresql-10.3 on unbuntu-17.10 - how??

2018-03-21 14:02 GMT+01:00 Adrian Klaver : > On 03/20/2018 10:52 PM, Stuart McGraw wrote: > Looks like these posts are coming through a news group to me. > I am Ccing list to get response back there. > > Is Pgdg 10.3 even available for ubuntu 17.10? How the heck does one upgrade to

Re: Re: Foreign Key Validation after Reference Table Ownership Change

On Wed, Mar 21, 2018 at 8:30 AM, Battuello, Louis < louis.battue...@etasseo.com> wrote: > So, user_2 needs usage on the schema containing its newly owned reference > table even though user_1 is performing the insert on a table in the other > schema? Interesting. I though the validation was only de

RE: Re: Foreign Key Validation after Reference Table Ownership Change

So, user_2 needs usage on the schema containing its newly owned reference table even though user_1 is performing the insert on a table in the other schema? Interesting. I though the validation was only dependent on user_1's ACL. - Original Message - Subject: Re: Foreign Key Vali

RE: Foreign Key locking / deadlock issue.... v2

OK, Let's try again, with a full script, and including the bit that makes the difference… Hi, I’m trying to understand why I’m getting a deadlock issue, and how to work around it. At base, I think the problem is: 1.Updates to a parent table are creating row level write locks.

Re: Foreign Key Validation after Reference Table Ownership Change

On Wednesday, March 21, 2018, Battuello, Louis wrote: > What permission is being violated at the schema level? > USAGE https://www.postgresql.org/docs/10/static/sql-grant.html David J.

Foreign Key Validation after Reference Table Ownership Change

This should be simple, but I must be missing something obvious. Running a change of table ownership on PostgreSQL 9.4.16. I changed the owner of a reference table in another, yet, after granting references to the referencing table owner, the key validation encounters an error. create role user

Re: Restore - disable triggers - when they fired?

On 03/21/2018 02:41 AM, Durumdara wrote: Dear Adrian! 2018-03-20 16:33 GMT+01:00 Adrian Klaver >: On 03/20/2018 07:56 AM, Durumdara wrote: pg_dump --disable-triggers -d test -U aklaver -t disable_trigger_test -a -f disable_trigger_test_data.sql

Re: postgresql-10.3 on unbuntu-17.10 - how??

On 03/20/2018 10:52 PM, Stuart McGraw wrote: Looks like these posts are coming through a news group to me. I am Ccing list to get response back there. Is Pgdg 10.3 even available for ubuntu 17.10?  How the heck does one upgrade to it? 18.04 LTS (Bionic Beaver)?: http://apt.postgresql.org/pub

Re: Is there a way to get the name of the calling function in pgplsql?

2018-03-21 13:30 GMT+01:00 Thiemo Kellner, NHC Barhufpflege < thiemo.kell...@gelassene-pferde.biz>: > Thanks for the hint and please excuse my not thouroughly enough reading of > the documentation. I did not suspect such Feature amongst controll > structures. > no problem:) Pavel > > Zitat von

RE: Foreign Key locking / deadlock issue.

Apologies, I committed the heinous crime of not creating a full working demo. It seems that Row Level Security is involved - I'll post an update with full code ASAP Phil Horder Database Mechanic

Re: Is there a way to get the name of the calling function in pgplsql?

Thanks for the hint and please excuse my not thouroughly enough reading of the documentation. I did not suspect such Feature amongst controll structures. Zitat von Pavel Stehule : https://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-CALL-STACK -- Öffentli

Re: Foreign Key locking / deadlock issue.

Hello Phil, On Tue, 2018-03-20 at 11:46 +, HORDER Phil wrote: > Hi, > I’m trying to understand why I’m getting a deadlock issue, and how to > work around it. > > At base, I think the problem is: > 1. Updates to a parent table are creating row level write > locks, > 2. updates to

Re: Restore - disable triggers - when they fired?

Dear Adrian! 2018-03-20 16:33 GMT+01:00 Adrian Klaver : > On 03/20/2018 07:56 AM, Durumdara wrote: > >> >> pg_dump --disable-triggers -d test -U aklaver -t disable_trigger_test -a > -f disable_trigger_test_data.sql > > > CREATE OR REPLACE FUNCTION public.trigger_test() > ... > truncate disable_tr

Re: Is there a way to get the name of the calling function in pgplsql?

Hi 2018-03-21 8:24 GMT+01:00 Thiemo Kellner : > Hi all > > In a function I would like to log the caller. Is there a way to get its > name in pgplsql? > you can read it from stack https://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-CALL-STACK Regards Pavel

Is there a way to get the name of the calling function in pgplsql?

Hi all In a function I would like to log the caller. Is there a way to get its name in pgplsql? Kind regards Thiemo -- Öffentlicher PGP-Schlüssel: http://pgp.mit.edu/pks/lookup?op=get&search=0x8F70EFD2D972CBEF This message