Re: FDW Foreign Table Access: strange LOG message

[Tom Lane]

Michael Paquier  writes:
> On Thu, Mar 22, 2018 at 06:55:12PM -0400, Tom Lane wrote:
>> Anyway, it's clearly not very nice that postgres_fdw makes no attempt
>> to do a graceful shutdown of its remote connection.  I don't know that
>> this rises to the level of a bug, exactly, but if somebody wanted to
>> send in a patch it'd probably get accepted.

> I won't qualify that as a bug, this is mainly noise.  Still I agree that
> it would be cleaner to make more efforts in finishing the connections
> when the session goes down.  You would roughly just need to register an
> on_shmem_exit callback which loops through all the connections to finish
> them.

That was my basic thought.  Not sure whether on_proc_exit is better than
on_shmem_exit, but one or the other.

> Now would we want to slow down the session shutdown just for
> that?  I am less sure particularly if there is lag between the remote
> and the local servers.

Yeah, I think the sticky part might be what to do if the shutdown attempt
blocks.  I vaguely recall that PQfinish just fires off an 'X' protocol
message and doesn't wait for an answer, so maybe there's not huge risk
there, but you don't really want to risk any delay.  (The worst-case
scenario is that you've gotten SIGTERM from init because a system
shutdown is beginning, and you don't have much time before it brings
the hammer down with SIGKILL, preventing a clean database shutdown
and thereby risking data corruption if anything is wrong with the
filesystem sync semantics.  I am not sure if it's more or less dangerous
for this purpose if the "remote" session is another backend in the same
cluster, and therefore also subject to the same shutdown signal.)

regards, tom lane

Re: FDW Foreign Table Access: strange LOG message

[Michael Paquier]

On Thu, Mar 22, 2018 at 06:55:12PM -0400, Tom Lane wrote:
> Anyway, it's clearly not very nice that postgres_fdw makes no attempt
> to do a graceful shutdown of its remote connection.  I don't know that
> this rises to the level of a bug, exactly, but if somebody wanted to
> send in a patch it'd probably get accepted.

I won't qualify that as a bug, this is mainly noise.  Still I agree that
it would be cleaner to make more efforts in finishing the connections
when the session goes down.  You would roughly just need to register an
on_shmem_exit callback which loops through all the connections to finish
them.  Now would we want to slow down the session shutdown just for
that?  I am less sure particularly if there is lag between the remote
and the local servers.
--
Michael


signature.asc
Description: PGP signature

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

[Peter Geoghegan]

On Thu, Mar 22, 2018 at 2:24 PM, Jeremy Finzel  wrote:
> I am running this on a san snapshot of our production system.  I assume that
> this will give me a valid check for file-system-level corruption.  I am
> going to kick it off and see if I find anything interesting.

It might. Note that SAN snapshots might have corruption hazards,
though. Postgres expects crash consistency across all filesystems, so
you might run into trouble if you had a separate filesystem for WAL,
for example. I know that LVM snapshots only provide a consistent view
of a single logical volume, even though many LVM + Postgres setups
will involve multiple logical volumes. This makes it possible for a
small inconsistency across logical volumes to corrupt data.

I don't know anything about your SAN snapshotting, but this is at
least something to consider.

-- 
Peter Geoghegan

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

[Stuart McGraw]

On 03/21/2018 03:08 PM, Adrian Klaver wrote:

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 able to load data
dumped from a 10.3 database.  pg_restore complained about "unsupported
version (1.13) in file header".  However I just discovered I can load
a plain sql dump from that database so panic mode is over :-) and I
can wait until I can upgrade my machine to ubuntu-18.04.

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; if you do need latest version, use the Pgdg
version but only with a LTS version of Ubuntu.

If you need the latest version of both Ubuntu and Postgresql, you
may be out of luck.


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 have done that
on Fedora several times where it has been, in my limited experience,
usually simple and problem free.  But I have read that building packages
on Ubuntu is much more arcane so I wasn't looking forward to it.


That is pretty much the case when you build from source, it will live outside 
the OS packaging universe. I have built from source on Ubuntu it is not any 
more difficult then other distros, just remember to install build-essential. As 
far a dependencies basically the only things that will have a Postgres 
dependency will be other Postgres software e.g. psycopg2, etc. That means you 
will need to build them from source also, though that is helped along by 
pg_config which will find your source install and build the other software to 
match. It also means uninstalling the Ubuntu Postgres packages so you don't 
cross contaminate.


I wasn't thinking of building Postgresql from the postgresql.org
tarball -- I think that's what you are suggesting?  I agree that
in and of itself is not a problem.  The problem for me is (or
would have been), as you say, the corequisite rebuilding from
source of other packages.  Besides python's psycopg2 I have
critical tools that use libdbd-pg-perl or libpq directly like
the Bacula backup system.

So what I was thinking of was rebuilding the Pgdg Ubuntu source
package (I'm assuming one is available somewhere).  I have had
good results on Fedora backporting current versions of Postgresql
from later fedora's to my invariably outdated version of Fedora
by rebuilding the later version's Fedora postgresql source rpm.
This produces an installable binary package that will satisfy
the dependencies of all those other programs eliminating the
need to rebuild them.  I was guessing I could do something
similar in the Ubuntu world.  But, moot now fortunately :-)

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

[Alessandro Aste]

Thanks Tomas. We're currently building postgres from source. In order to
enable symbols, you want me to re-configure postres with  --enable-debug
then run perf?

Regards,

On Thu, Mar 22, 2018 at 5:00 PM, Tomas Vondra 
wrote:

>
>
> On 03/22/2018 11:33 AM, Alessandro Aste wrote:
> > Tomas, thank you. This machine  is abare metal server running only a
> > staging postgresql 10.3  instance. Nobody is using it beside me.
> >
> > I'm attaching 4 files.
> >
> > every_30_seconds_top_stats_during_query.txt- this is a caputure of
> > the top command every 30 seconds(more or less) for 10+ minutes while I'm
> > running the query. Let me know if this helps to answere your question.
> > EXPLAIN_WITH_LIMIT_AND_max_parallel_workers_per_gather_SET_TO_0.txt   -
> > query plan  with full query and  max_parallel_workers_per_gather  force
> > to 0. Full output.
> > EXPLAIN_WITH_LIMIT_AND_DEFAULT_PARALLEL_PROCESSING_SETTINGS.txt  -
> >  query plan with full query and  default parellel processing settings.
> > Full output.
> > EXPLAIN_WITHOUT_LIMIT_AND_DEFAULT_PARALLEL_PROCESSING_SETTINGS.tx  -
> > query plan of the query omitting the LIMIT clause and default parellel
> > processing settings. Full output.
> >
>
> OK. Looking at the top output, I see this:
>
>PID USER   VIRT  RES  SHR S %CPU %MEMTIME+  COMMAND
> 104880 postgres  30.8g 1.9g 1.9g R 92.0  1.5  15:15.60 postmaster
> 111732 postgres  30.8g 476m 473m R 88.2  0.4   0:00.47 postmaster
> 111730 postgres  30.8g 473m 470m R 86.4  0.4   0:00.46 postmaster
> 111731 postgres  30.8g 476m 473m R 86.4  0.4   0:00.46 postmaster
> 111733 postgres  30.8g 476m 473m R 86.4  0.4   0:00.46 postmaster
> 111734 postgres  30.8g 476m 473m R 86.4  0.4   0:00.46 postmaster
> 111728 root  15824 1912  828 R  3.8  0.0   0:00.04 top
>
> That means it certainly is not stuck, it's simply doing a lot of work on
> CPU. The question is why and what it's doing ...
>
> Can you collect some CPU profiles using perf? There's a howto here:
>
> https://wiki.postgresql.org/wiki/Profiling_with_perf
>
> But in short - install perf, install debuginfo packages for postgres,
> and then do
>
> perf record -a -g -s sleep 60
>
> while running the query. Once the profile data is collected, do
>
> perf report > report.txt
>
> and share the report.txt with us (well, if it's very large you may need
> to only cut the first 1000 lines or so).
>
> That should tell us in which functions most of the time is spent. That
> will give us some hints, hopefully.
>
> >
> > For what concerns the  self-contained test case - I'll do my best to
> > prepare it.
> >
>
> Yeah, that would be helpful.
>
> regards
>
> --
> Tomas Vondra  http://www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

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

[Tomas Vondra]

On 03/22/2018 11:29 PM, Alessandro Aste wrote:
> Thanks Tomas. We're currently building postgres from source. In order to
> enable symbols, you want me to re-configure postres with  --enable-debug
> then run perf?
> 

Yes.

regards

-- 
Tomas Vondra  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

[Jeremy Finzel]

On Thu, Mar 22, 2018 at 3:20 PM, Peter Geoghegan  wrote:

> On Thu, Mar 22, 2018 at 12:27 PM, Jeremy Finzel  wrote:
> > Thank you for the recommendation.  I ran both amcheck functions on all 4
> > indexes of those 2 tables with heapallindexed = true, but no issues were
> > found.
>
> Probably wouldn't hurt to run it against all indexes, if you can make
> time for that. If you can generalize from the example query that calls
> the bt_index_check() function, but set
> "heapallindexed=>i.indisprimary" and remove "n.nspname =
> 'pg_catalog'", as well as "LIMIT 10".  This will test tables and
> indexes from all schemas, which might be interesting.
> --
> Peter Geoghegan
>

I am running this on a san snapshot of our production system.  I assume
that this will give me a valid check for file-system-level corruption.  I
am going to kick it off and see if I find anything interesting.

Thanks,
Jeremy

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

[Alvaro Herrera]

I admit I'm pretty surprised by this whole episode.  I have no useful
advice to offer here.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

[Jeremy Finzel]

On Wed, Mar 21, 2018 at 4:29 PM, Peter Geoghegan  wrote:

> 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 amcheck to look for problems. The version on
> Github works with 9.5, and there are Redhat and Debian pgdg packages.
> See:
>
> https://github.com/petergeoghegan/amcheck
>
> The "heapallindexed" option will be of particular interest to you -
> that option verifies that the table has matching rows for a target
> index (in addition to testing the structure of a target B-Tree index
> itself). This is probably the best general test for corruption that is
> available. There is a fair chance that this will reveal new
> information.
>
> --
> Peter Geoghegan
>

Thank you for the recommendation.  I ran both amcheck functions on all 4
indexes of those 2 tables with heapallindexed = true, but no issues were
found.

Thanks,
Jeremy

Re: FDW Foreign Table Access: strange LOG message

[Adrian Klaver]

On 03/22/2018 11:08 AM, Albrecht Dreß wrote:

Hi Adrian:

Am 21.03.18 19:25 schrieb(en) Adrian Klaver:

Does it also happen when you open a psql session and do?:

psql>select * from ext_table limit 1;


No messages are printed whilst the session is open.  However, when I 
quit the session by typing “\q” /after/ entering the command above, the 
LOG message appears.  It does not appear when I query tables from my_db 
exclusively, though, i.e. when I do not access the Foreign Table.


Seems to back up Tom Lanes's observation:

https://www.postgresql.org/message-id/30297.1521574736%40sss.pgh.pa.us
"If these are about the FDW connections, maybe the answer is that
postgres_fdw ought to establish a backend-exit callback in which
it can shut down its connections gracefully.  If it's doing that
now, I sure don't see where."

Looks like it might be time to file a bug report:

https://www.postgresql.org/account/submitbug/


I am not seeing --contrib which is where I would expect postgres_fdw 
to come from.


Apparently, it is packaged with the main server deb file:

albrecht@odysseus:~$ dpkg -S $(find /usr/share/postgresql -name "*fdw*")
postgresql-10: /usr/share/postgresql/10/extension/file_fdw--1.0.sql
postgresql-10: /usr/share/postgresql/10/extension/file_fdw.control
postgresql-10: /usr/share/postgresql/10/extension/postgres_fdw--1.0.sql
postgresql-10: /usr/share/postgresql/10/extension/postgres_fdw.control

…and additionally it contains /usr/lib/postgresql/10/postgres_fdw.so, of 
course.  I double-checked the package 
http://apt.postgresql.org/pub/repos/apt/pool/main/p/postgresql-10/postgresql-10_10.3-1.pgdg90+1_amd64.deb; 
it is installed properly.


My mistake, I was looking at an install that had Postgres 9.6. In that 
case the files for 9.6 where in --contrib, presumably because 10 is now 
the default.





Is --contrib installed?


No.  But the deb file postgresql-contrib_10+190.pgdg90+1_all.deb 
contains only the changelog and copyright files.



If not where is postgres_fdw coming from?


See above…

Cheers,
Albrecht.



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

Re: FDW Foreign Table Access: strange LOG message

[Albrecht Dreß]

Hi Adrian:

Am 21.03.18 19:25 schrieb(en) Adrian Klaver:

Does it also happen when you open a psql session and do?:

psql>select * from ext_table limit 1;


No messages are printed whilst the session is open.  However, when I quit the 
session by typing “\q” /after/ entering the command above, the LOG message 
appears.  It does not appear when I query tables from my_db exclusively, 
though, i.e. when I do not access the Foreign Table.


postgresql-10    10.3-1.pgdg90+1 amd64
postgresql-client-10 10.3-1.pgdg90+1 amd64
postgresql-client-common 190.pgdg90+1    all
postgresql-common    190.pgdg90+1    all
libpq5:amd64 10.3-1.pgdg90+1 amd64


I am not seeing --contrib which is where I would expect postgres_fdw to come 
from.


Apparently, it is packaged with the main server deb file:

albrecht@odysseus:~$ dpkg -S $(find /usr/share/postgresql -name "*fdw*")
postgresql-10: /usr/share/postgresql/10/extension/file_fdw--1.0.sql
postgresql-10: /usr/share/postgresql/10/extension/file_fdw.control
postgresql-10: /usr/share/postgresql/10/extension/postgres_fdw--1.0.sql
postgresql-10: /usr/share/postgresql/10/extension/postgres_fdw.control

…and additionally it contains /usr/lib/postgresql/10/postgres_fdw.so, of 
course.  I double-checked the package 
http://apt.postgresql.org/pub/repos/apt/pool/main/p/postgresql-10/postgresql-10_10.3-1.pgdg90+1_amd64.deb;
 it is installed properly.


Is --contrib installed?


No.  But the deb file postgresql-contrib_10+190.pgdg90+1_all.deb contains only 
the changelog and copyright files.


If not where is postgres_fdw coming from?


See above…

Cheers,
Albrecht.

pgpASLcE_j7qf.pgp
Description: PGP signature

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

[Tomas Vondra]

On 03/22/2018 11:33 AM, Alessandro Aste wrote:
> Tomas, thank you. This machine  is abare metal server running only a
> staging postgresql 10.3  instance. Nobody is using it beside me. 
> 
> I'm attaching 4 files.  
> 
> every_30_seconds_top_stats_during_query.txt    - this is a caputure of
> the top command every 30 seconds(more or less) for 10+ minutes while I'm
> running the query. Let me know if this helps to answere your question.
> EXPLAIN_WITH_LIMIT_AND_max_parallel_workers_per_gather_SET_TO_0.txt   - 
> query plan  with full query and  max_parallel_workers_per_gather  force
> to 0. Full output.
> EXPLAIN_WITH_LIMIT_AND_DEFAULT_PARALLEL_PROCESSING_SETTINGS.txt  - 
>  query plan with full query and  default parellel processing settings. 
> Full output.
> EXPLAIN_WITHOUT_LIMIT_AND_DEFAULT_PARALLEL_PROCESSING_SETTINGS.tx  -
> query plan of the query omitting the LIMIT clause and default parellel
> processing settings. Full output.
> 

OK. Looking at the top output, I see this:

   PID USER   VIRT  RES  SHR S %CPU %MEMTIME+  COMMAND
104880 postgres  30.8g 1.9g 1.9g R 92.0  1.5  15:15.60 postmaster
111732 postgres  30.8g 476m 473m R 88.2  0.4   0:00.47 postmaster
111730 postgres  30.8g 473m 470m R 86.4  0.4   0:00.46 postmaster
111731 postgres  30.8g 476m 473m R 86.4  0.4   0:00.46 postmaster
111733 postgres  30.8g 476m 473m R 86.4  0.4   0:00.46 postmaster
111734 postgres  30.8g 476m 473m R 86.4  0.4   0:00.46 postmaster
111728 root  15824 1912  828 R  3.8  0.0   0:00.04 top

That means it certainly is not stuck, it's simply doing a lot of work on
CPU. The question is why and what it's doing ...

Can you collect some CPU profiles using perf? There's a howto here:

https://wiki.postgresql.org/wiki/Profiling_with_perf

But in short - install perf, install debuginfo packages for postgres,
and then do

perf record -a -g -s sleep 60

while running the query. Once the profile data is collected, do

perf report > report.txt

and share the report.txt with us (well, if it's very large you may need
to only cut the first 1000 lines or so).

That should tell us in which functions most of the time is spent. That
will give us some hints, hopefully.

> 
> For what concerns the  self-contained test case - I'll do my best to
> prepare it.
> 

Yeah, that would be helpful.

regards

-- 
Tomas Vondra  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Introducing brand-new dbForge Studio for PostgreSQL, a powerful IDE for working with PostgreSQL databases

[Melvin Davidson]

The "Free" version doesn't do much. Just lets you connect to a DB,
create/edit and execute SQL.
oh hum.

On Thu, Mar 22, 2018 at 9:55 AM, Tim Clarke 
wrote:

> On 22/03/18 11:53, Steven Hirsch wrote:
> > On Thu, 22 Mar 2018, Devart wrote:
> >
> >> /Devart announced the first release of dbForge Studio for PostgreSQL
> >> that
> >> allows to manage and develop database objects in PostgreSQL. This new
> >> IDE
> >> offers many robust features like Code Completion, Object Explorer and
> >> Data
> >> Editor which help users create, develop and execute queries as well
> >> as edit
> >> and adjust the code to their requirements in a convenient and
> >> user-friendly
> >> interface./
> >
> > (snip...)
> >
> >  Windows only.  Wake me up when you have a Mac or Linux version.
> >
> >
>
> +1
>
> Tim Clarke
>
>


-- 
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!

Re: Introducing brand-new dbForge Studio for PostgreSQL, a powerful IDE for working with PostgreSQL databases

[Tim Clarke]

On 22/03/18 11:53, Steven Hirsch wrote:
> On Thu, 22 Mar 2018, Devart wrote:
>
>> /Devart announced the first release of dbForge Studio for PostgreSQL
>> that
>> allows to manage and develop database objects in PostgreSQL. This new
>> IDE
>> offers many robust features like Code Completion, Object Explorer and
>> Data
>> Editor which help users create, develop and execute queries as well
>> as edit
>> and adjust the code to their requirements in a convenient and
>> user-friendly
>> interface./
>
> (snip...)
>
>  Windows only.  Wake me up when you have a Mac or Linux version.
>
>

+1

Tim Clarke



smime.p7s
Description: S/MIME Cryptographic Signature

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

[Alessandro Aste]

Tomas, thank you. This machine  is abare metal server running only a
staging postgresql 10.3  instance. Nobody is using it beside me.

I'm attaching 4 files.

every_30_seconds_top_stats_during_query.txt- this is a caputure of the
top command every 30 seconds(more or less) for 10+ minutes while I'm
running the query. Let me know if this helps to answere your question.
EXPLAIN_WITH_LIMIT_AND_max_parallel_workers_per_gather_SET_TO_0.txt   -  query
plan  with full query and  max_parallel_workers_per_gather  force to 0.
Full output.
EXPLAIN_WITH_LIMIT_AND_DEFAULT_PARALLEL_PROCESSING_SETTINGS.txt  -query
plan with full query and  default parellel processing settings.  Full
output.
EXPLAIN_WITHOUT_LIMIT_AND_DEFAULT_PARALLEL_PROCESSING_SETTINGS.tx  - query
plan of the query omitting the LIMIT clause and default parellel processing
settings. Full output.


For what concerns the  self-contained test case - I'll do my best to
prepare it.

Thank you very much, please let me know if this answer your questions.






Il 22 mar 2018 3:04 AM, "Tomas Vondra"  ha
scritto:

>
> 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 the question, I'm afraid. I suppose "89 of
> CPU" means that 89% idle in total, but 11% with 56 CPUs still means
> about 6 cores 100% busy. But maybe you meant something else?
>
> Is there something else running on the machine? If you look at "top" are
> the processes (the one you're connected to and the parallel workers)
> doing something on the CPU?
>
> >
> > Plain analyze as requested. :
> >
>
> I don't see anything obviously broken with the query plan, and it's
> difficult to compare with the other plans because they are quite different.
>
> But there's one part of the plan interesting:
>
>  Limit  (cost=253523.56..253523.57 rows=1 width=176)
>->  Sort  (cost=253523.56..253523.57 rows=1 width=176)
>  Sort Key: c_2.gii_circuitid, c_1.id
>  ->  Nested Loop  (cost=33190.89..253523.55 rows=1 width=176)
>Join Filter: (c_1.id = c.id)
>->  Nested Loop  (cost=31724.87..31736.29 rows=1 ...)
>  ...
>->  Gather  (cost=1466.02..221787.23 rows=3 width=75)
>  Workers Planned: 5
>  ->  Hash Join  (cost=466.02..220786.93 rows=1 ...)
>  ...
>
> That is, there's a Gather on the inner side of a Nested Loop. I wonder
> if that might cause issues in case of under-estimate (in which case we'd
> be restarting the Gather many times) ...
>
>
> BTW one of the plans you sent earlier is incomplete, because it ends
> like this:
>
> ->  Nested Loop  (cost=42469.41..42480.82 rows=1 width=85) (...)
> Join Filter: (c.status = cst.id)
> Time: 3016.688 ms (00:03.017)
>
> That is, it's missing the part below the join.
>
>
> That being said, I'm not sure what's the issue here. Can you prepare a
> self-contained test case that we might use to reproduce the issue? For
> example by dumping the relevant part of the tables?
>
>
> regards
>
> --
> Tomas Vondra  http://www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
QUERY PLAN
Sort  (cost=253514.80..253514.81 rows=1 width=176)
  Sort Key: c_2.gii_circuitid, c_1.id
  ->  Hash Join  (cost=33193.56..253514.79 rows=1 width=176)
Hash Cond: (c_1.id = c.id)
->  Gather  (cost=1466.02..221787.23 rows=3 width=75)
  Workers Planned: 5
  ->  Hash Join  (cost=466.02..220786.93 rows=1 width=75)
Hash Cond: (c_1.id = vendor_gtt_pop.gtt_pop_id)
->  Hash Join  (cost=444.07..219779.19 rows=157724 width=63)
  Hash Cond: (c_1.pop_support_vendor_id = v.id)
  ->  Merge Join  (cost=5.02..217348.87 rows=157724 
width=40)
Merge Cond: (cl.circuit_id = c_1.id)
->  Nested Loop  (cost=0.86..171314.49 
rows=157724 width=32)
  ->  Parallel Index Only Scan using 
circuit_layout_idx on circuit_layout cl  (cost=0.43..55430.93 rows=157724 
width=8)
Index Cond: (ordinal = 1)
  ->  Index Scan using uniqid on segment sg 
 (cost=0.43..0.73 rows=1 width=32)
Index Cond: (id = cl.segment_id)
->  Index Scan using circuit_id_key on circuit 
c_1  (cost=0.42..41790.58 rows=909014 width=8)
  ->  Hash  (cost=325.69..325.69 rows=9069 width=27)
->  Seq Scan on vendor v  (cost=0.00..325.69 

Re: Introducing brand-new dbForge Studio for PostgreSQL, a powerful IDE for working with PostgreSQL databases

[Raymond O'Donnell]

On 22/03/18 11:53, Steven Hirsch wrote:

On Thu, 22 Mar 2018, Devart wrote:


/Devart announced the first release of dbForge Studio for PostgreSQL that
allows to manage and develop database objects in PostgreSQL. This new IDE
offers many robust features like Code Completion, Object Explorer and 
Data
Editor which help users create, develop and execute queries as well as 
edit
and adjust the code to their requirements in a convenient and 
user-friendly

interface./


(snip...)

 Windows only.  Wake me up when you have a Mac or Linux version.


Yes, I got excited until I figured this out. It wasn't obvious on the 
website either, until I saw the line about .Net.


Ray.

--
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie

Re: Introducing brand-new dbForge Studio for PostgreSQL, a powerful IDE for working with PostgreSQL databases

[Steven Hirsch]

On Thu, 22 Mar 2018, Devart wrote:


/Devart announced the first release of dbForge Studio for PostgreSQL that
allows to manage and develop database objects in PostgreSQL. This new IDE
offers many robust features like Code Completion, Object Explorer and Data
Editor which help users create, develop and execute queries as well as edit
and adjust the code to their requirements in a convenient and user-friendly
interface./


(snip...)

 Windows only.  Wake me up when you have a Mac or Linux version.


--

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

[HORDER Phil]

Thanks Rob,

The security policy in the example was reduced to the bare minimum, to 
demonstrate the issue was with the existence of the policy, not what it did.
Obviously my real-application policy does much more, and uses other columns to 
do both read & write checks.

Adding the write check as suggested makes no difference to the demo - the 
blocking lock still occurs.


Phil Horder
Database Mechanic

-Original Message-
From: rob stone [mailto:floripa...@gmail.com] 
Sent: 22 March 2018 11:11
To: HORDER Phil; Adrian Klaver
Cc: pgsql-general
Subject: Re: Foreign Key locking / deadlock issue v2

Hello Phil,

On Wed, 2018-03-21 at 15:26 +, HORDER Phil wrote:
> 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.
> 2.updates to a child table set the foreign key value to
> the parent table, which are then blocked – because there is Row Level 
> Security on the parent table.
>  
>  
>  
> -- The setup
> 
>  
> drop table if exists ELN;
> drop table if exists PL;
>  
> Create table PL
> (pl_id integer,
> m_number text
> );
>  
>  
> alter table PL ENABLE row level security; alter table PL FORCE row 
> level security;
>  
> -- A dummy security policy, that allows everybody access to everything 
> drop policy if exists security_policy on PL ;

I don't use row level security but after reading the documentation, I'd alter 
this:-

> create policy security_policy on PL TO public using (true);   -- (1)

to

CREATE POLICY security_policy on PL FOR ALL TO PUBLIC USING (true) WITH CHECK 
(true);

and trying again.

HTH,
Rob

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

[rob stone]

Hello Phil,

On Wed, 2018-03-21 at 15:26 +, HORDER Phil wrote:
> 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.
> 2.updates to a child table set the foreign key value to
> the parent table, which are then blocked – because there is Row Level
> Security on the parent table.
>  
>  
>  
> -- The setup
> 
>  
> drop table if exists ELN;
> drop table if exists PL;
>  
> Create table PL
> (pl_id integer,
> m_number text
> );
>  
>  
> alter table PL ENABLE row level security;
> alter table PL FORCE row level security;
>  
> -- A dummy security policy, that allows everybody access to
> everything
> drop policy if exists security_policy on PL ;

I don't use row level security but after reading the documentation, I'd
alter this:-

> create policy security_policy on PL TO public using (true);   -- (1)

to

CREATE POLICY security_policy on PL FOR ALL TO PUBLIC USING (true) WITH
CHECK (true);

and trying again.

HTH,
Rob

Re: Prepared statements

[Rakesh Kumar]

> You mean Oracle 11g.

No 12c.  Some of it may have started in 11g itself, but only in 12c they really 
mastered it.  I saw it as a developer, not as a DBA. I was never an oracle DBA.

Introducing brand-new dbForge Studio for PostgreSQL, a powerful IDE for working with PostgreSQL databases

[Devart]

/Devart announced the first release of dbForge Studio for PostgreSQL that
allows to manage and develop database objects in PostgreSQL. This new IDE
offers many robust features like Code Completion, Object Explorer and Data
Editor which help users create, develop and execute queries as well as edit
and adjust the code to their requirements in a convenient and user-friendly
interface./

Devart, a Czech software provider of database connectivity solutions and
tools for database management and development, announced the first release
of dbForge Studio for PostgreSQL. Using this tool,users can view all the
required information of PostgreSQL database objects they are interested in.
In the first version, dbForge Studio for PostgreSQL offers the following key
features:

1) Code Completion enables users to save their time and enhance the code
quality while writing SQL queries with the help of numerous helpful
functionalities like:

• List Members
• Parameter Info
• Quick Info
• Complete Word

2) Object Explorer with expandable objects allows users to navigate through
the object tree, find any PostgreSQL objects of interest and perform the
following tasks:

• Retrieve data from a table to present on a single SQL document
• Truncate a table
• Select Properties to view specific information on objects

3) Data Editor simplifies the process of editing PostgreSQL table data
offering many handy capabilities:

• Editing data similar to Google Sheets and MS Excel or in the card view
mode
• Managing the settings of tables, such as adjusting column width
• Setting either paginal or auto-search mode by default
• Changing colors and formats of rows and cells using a rich set of
fonts and sizes
• Providing predefined data type formats so that users can select the
most suitable for their needs

For more information about dbForge Studio for PostgreSQL, please visit  our
site   .

About Devart
Devart is one of the leading developers of database tools and administration
software, ALM solutions, data providers for various database servers, data
integration and backup solutions. The company also implements Web and Mobile
development projects.  
Learn more about Devart at  https://www.devart.com  
.



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: JDBC connectivity issue

[Dave Cramer]

Chris,

At this point I'd write small piece of code to test if the url and failover
is working correctly. Sounds like you have a considerably sized stack
making it difficult to debug.
With a small piece of code it should become clear as to how things work or
don't as the case may be

Dave Cramer

da...@postgresintl.com
www.postgresintl.com

On 21 March 2018 at 17:13, Adrian Klaver  wrote:

> 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 a Postgres JDBC driver. If
> you know where that configuration is you should be able to find the driver
> and presumably the version.
> 2) So when you say you did a reinstall do you mean you are now pointing
> the configuration at postgresql-42.2.1.jre7.jar? FYI
> postgresql-42.2.2.jre7.jar is actually the latest:
> https://jdbc.postgresql.org/download.html
>
>
>
>>
>> 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 you could not find the version you
>>> where using how do you know installing a new driver actually changed the
>>> version you are using now?
>>>
>>>
>>>
 we downloaded the current version JDBC 4.1 Driver 42.2.1.jre7


 We are still having the same problem.

 Thanks





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

Re: Prepared statements

[Laurenz Albe]

Rakesh Kumar wrote:
> Only in Oracle 12c there are 
> options to let the planner change existing plan by peeking into the parameter
> values (supplied in ? of prepare) and checking it against the distribution.

You mean Oracle 11g.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com