Re: [GENERAL] Vacuum and state_change

> On Jun 9, 2017, at 4:20 PM, Adrian Klaver wrote: > > On 06/09/2017 02:01 PM, armand pirvu wrote: >>> On Jun 9, 2017, at 3:52 PM, Adrian Klaver wrote: >>> >>> On 06/09/2017 01:31 PM, armand pirvu wrote: >>> > > > >> By temporary

Re: [GENERAL] pg_catalog tables don't have constraints?

On 9 Jun 2017 14:59, wrote: Neil Anderson wrote: > I've been exploring the pg_catalog tables and pointed a couple of tools at > it to extract an ER diagram for a blog post. At first I thought it was a bug > in the drawing tool but it appears that

Re: [GENERAL] Vacuum and state_change

On 06/09/2017 02:01 PM, armand pirvu wrote: On Jun 9, 2017, at 3:52 PM, Adrian Klaver wrote: On 06/09/2017 01:31 PM, armand pirvu wrote: By temporary tables I mean just regular table not tables created by "create temporary table" . I should have been more

Re: [GENERAL] Performance issue with Pointcloud extension

Jeff Janes writes: > On Fri, Jun 9, 2017 at 9:13 AM, Eric Lemoine > wrote: >> I now think that the performance bug is not related to the fn_extra >> thing. I had hope but not anymore :) I don't see where the Pointcloud >> and PostGIS extensions

Re: [GENERAL] Vacuum and state_change

> On Jun 9, 2017, at 5:42 PM, Adrian Klaver wrote: > > On 06/09/2017 02:26 PM, armand pirvu wrote: >>> On Jun 9, 2017, at 4:20 PM, Adrian Klaver wrote: >>> >>> On 06/09/2017 02:01 PM, armand pirvu wrote: > On Jun 9, 2017, at 3:52 PM,

Re: [GENERAL] Vacuum and state_change

> On Jun 9, 2017, at 11:23 AM, Adrian Klaver wrote: > > On 06/09/2017 09:13 AM, armand pirvu wrote: >>> On Jun 9, 2017, at 11:01 AM, Adrian Klaver >> > wrote: >>> >>> On 06/09/2017 08:45 AM, armand pirvu

Re: [GENERAL] Vacuum and state_change

On 06/09/2017 01:31 PM, armand pirvu wrote: Are these large tables? I would say yes select count(*) from csischema.tf_purchased_badge; 9380749 select count(*) from csischema.tf_purchases_person; 19902172 select count(*) from csischema.tf_demographic_response_person; 80868561

Re: [GENERAL] Limiting DB access by role after initial connection?

> > As to your very last point (suggestions about other approaches), is it > impossible or impractical to migrate to a scheme in which each user > actually has a data base role and their own password? Postgresql has really > great facility for managing database authorization and access by means of

Re: [GENERAL] Vacuum and state_change

On 06/09/2017 02:26 PM, armand pirvu wrote: On Jun 9, 2017, at 4:20 PM, Adrian Klaver wrote: On 06/09/2017 02:01 PM, armand pirvu wrote: On Jun 9, 2017, at 3:52 PM, Adrian Klaver wrote: On 06/09/2017 01:31 PM, armand pirvu wrote:

Re: [GENERAL] Vacuum and state_change

> On Jun 9, 2017, at 3:52 PM, Adrian Klaver wrote: > > On 06/09/2017 01:31 PM, armand pirvu wrote: > >>> >>> Are these large tables? > > >> I would say yes >> select count(*) from csischema.tf_purchased_badge; >> 9380749 >> select count(*) from

Re: [GENERAL] Limiting DB access by role after initial connection?

On Fri, Jun 9, 2017 at 11:22 AM, Joe Conway wrote: > On 06/09/2017 08:56 AM, Ken Tanzer wrote: > > The extra logging would be undesirable. Is there any way to skip that > > entirely? I see with block_log_statement I could dial down the logging > > after switching users, but

Re: [GENERAL] Limiting DB access by role after initial connection?

On 06/09/2017 02:16 PM, Ken Tanzer wrote: > FWIW, it would be clearer at least to me if you took the two statements > in the description: > > * log_statement setting is set to "all", meaning every SQL statement > executed while in this state will also get logged. > * If

Re: [GENERAL] Limiting DB access by role after initial connection?

On Thu, Jun 08, 2017 at 22:37:34 -0700, Ken Tanzer wrote: My approach was to have the initial connection made by the owner, and then after successfully authenticating the user, to switch to the role of the site they belong to. After investigation, this still seems

[GENERAL] pg_upgrade --link on Windows

Hi The pg_upgrade documentation for PostgreSQL 9.6 states that --link will use junction points on Windows. Shouldn't it rather user hard-links ? If I'm not mistaken, with junction points (i.e. soft-links to directories), the old data dir cannot be removed. With hard-links to file, we can get

Re: [GENERAL] Limiting DB access by role after initial connection?

As a follow up to this, a couple more questions from my wishful thinking list: 1) Any way, whether kosher or hacky, to set an arbitrary yet immutable (for the lifetime of the session) variable within a session? Something akin to DEFINE, which wouldn't allow redefinition? A temp table that

Re: [GENERAL] pg_upgrade --link on Windows

On 06/09/2017 07:07 AM, Bruce Momjian wrote: On Fri, Jun 9, 2017 at 12:00:56PM +0200, Arnaud L. wrote: Hi The pg_upgrade documentation for PostgreSQL 9.6 states that --link will use junction points on Windows. Shouldn't it rather user hard-links ? If I'm not mistaken, with junction points

Re: [GENERAL] pg_upgrade --link on Windows

On 06/09/2017 07:39 AM, Arnaud L. wrote: Le 9/06/2017 à 16:07, Bruce Momjian a écrit : I was told junction points on Windows were hard links and no one has ever complained about not being able to remove them. Sorry, I think my explanation was not very clear. You can remove the link, but the

[GENERAL] Vacuum and state_change

Hi Had a couple of processes blocking the vacuum so I terminated them using select pg_terminate_backend(pid); Running the following select distinct pid, backend_start, query_start, state_change, state, query from pg_catalog.pg_stat_activity order by 1; pid | backend_start |

Re: [GENERAL] Limiting DB access by role after initial connection?

On Fri, Jun 9, 2017 at 6:42 AM, Joe Conway wrote: > On 06/08/2017 10:37 PM, Ken Tanzer wrote: > > My approach was to have the initial connection made by the owner, and > > then after successfully authenticating the user, to switch to the role > > of the site they belong to.

Re: [GENERAL] Vacuum and state_change

On 06/09/2017 08:45 AM, armand pirvu wrote: Hi Had a couple of processes blocking the vacuum so I terminated them using select pg_terminate_backend(pid); Running the following select distinct pid, backend_start, query_start, state_change, state, query from pg_catalog.pg_stat_activity order by

Re: [GENERAL] ERROR: unexpected chunk number 0 (expected 1) for toast value 76753264 in pg_toast_10920100

Hi, Maybe you could give some info on : > - your ext3 mkfs and mount options (journal, barriers, etc) > /etc/fstab details below: LABEL=/var/lib/pgsql/var/lib/pgsql ext3defaults 1 2 LABEL=/tablespace1 /tablespace1ext3defaults 1 2

Re: [GENERAL] Limiting DB access by role after initial connection?

- Original Message - > From: "Ken Tanzer" > To: "PG-General Mailing List" > Sent: Friday, June 9, 2017 1:37:34 AM > Subject: [GENERAL] Limiting DB access by role after initial connection? > > ...I'm working with an organization with

Re: [GENERAL] pg_upgrade --link on Windows

On Fri, Jun 9, 2017 at 12:00:56PM +0200, Arnaud L. wrote: > Hi > > The pg_upgrade documentation for PostgreSQL 9.6 states that --link will use > junction points on Windows. > Shouldn't it rather user hard-links ? > If I'm not mistaken, with junction points (i.e. soft-links to directories), > the

Re: [GENERAL] pg_upgrade --link on Windows

Le 9/06/2017 à 16:07, Bruce Momjian a écrit : I was told junction points on Windows were hard links and no one has ever complained about not being able to remove them. Sorry, I think my explanation was not very clear. You can remove the link, but the point is to remove the target (i.e. the

Re: [GENERAL] Limiting DB access by role after initial connection?

On 06/08/2017 10:37 PM, Ken Tanzer wrote: > My approach was to have the initial connection made by the owner, and > then after successfully authenticating the user, to switch to the role > of the site they belong to. After investigation, this still seems > feasible but imperfect. Specifically, I

[GENERAL] Enc: Extending SQL in C using VARIABLE length type in user defined type

I am trying around a little bit with creating my own types using shared objects, written in C. The usage of static types with fixed length was actually no problem for me, so I proceeded to variable length types. I created an n-dimensional point structure called "Geo_Polygon" that contains a

Re: [GENERAL] pg_upgrade --link on Windows

Le 9/06/2017 à 16:55, Adrian Klaver a écrit : On 06/09/2017 07:39 AM, Arnaud L. wrote: So maybe pg_upgrade uses hard-links (i.e. to files), and only the documentation is wrong by calling them junctions (i.e. soft links to files) ? Looks that way. In file.c in ~/src/bin/pg_upgrade I see:

Re: [GENERAL] pg_upgrade --link on Windows

Le 9/06/2017 à 17:02, Arnaud L. a écrit : Le 9/06/2017 à 16:55, Adrian Klaver a écrit : On 06/09/2017 07:39 AM, Arnaud L. wrote: So maybe pg_upgrade uses hard-links (i.e. to files), and only the documentation is wrong by calling them junctions (i.e. soft links to files) ? Looks that way. In

Re: [GENERAL] ERROR: unexpected chunk number 0 (expected 1) for toast value 76753264 in pg_toast_10920100

Robert Lakes writes: > I am new to postgress and I am trying to write my first function to insert, > update or delete and trap errors as a result of the table not existing , > the columns not exist or if any other error simply pass back the sqlstate Please do not hijack an

Re: [GENERAL] Limiting DB access by role after initial connection?

On Fri, Jun 9, 2017 at 5:38 PM, Bruno Wolff III wrote: > On Thu, Jun 08, 2017 at 22:37:34 -0700, > Ken Tanzer wrote: > >> >> My approach was to have the initial connection made by the owner, and then >> after successfully authenticating the user, to switch

[GENERAL] Inconsistent performance with LIKE and bind variable on long-lived connection

PostgreSQL 9.6.3 on OS X Sierra, JDBC driver version 42.1.1. I noticed that one of my queries was slowing down after a few invocations. Narrowed it down to an issue with bind variables and LIKE conditions. Very consistently, on a given connection, the first 9 times a SELECT containing a LIKE

Re: [GENERAL] ERROR: unexpected chunk number 0 (expected 1) for toast value 76753264 in pg_toast_10920100

Hi Tom, Thanks for attempting to replicate the issue. Anyway, the bad news is I couldn't reproduce the problem then and I can't > now. I don't know if it's a timing issue or if there's something critical > about configuration that I'm not duplicating. Can you explain what sort > of platform

Re: [GENERAL] ERROR: unexpected chunk number 0 (expected 1) for toast value 76753264 in pg_toast_10920100

On 09/06/2017 14:44, Harry Ambrose wrote: Hi Tom, Thanks for attempting to replicate the issue. Anyway, the bad news is I couldn't reproduce the problem then and I can't now. I don't know if it's a timing issue or if there's something critical about configuration that I'm not

Re: [GENERAL] pg_upgrade --link on Windows

On Fri, 9 Jun 2017 10:07:24 -0400, Bruce Momjian wrote: >On Fri, Jun 9, 2017 at 12:00:56PM +0200, Arnaud L. wrote: >> Hi >> >> The pg_upgrade documentation for PostgreSQL 9.6 states that --link will use >> junction points on Windows. >> Shouldn't it rather user hard-links ?

Re: [GENERAL] Vacuum and state_change

> On Jun 9, 2017, at 11:01 AM, Adrian Klaver wrote: > > On 06/09/2017 08:45 AM, armand pirvu wrote: >> Hi >> Had a couple of processes blocking the vacuum so I terminated them using >> select pg_terminate_backend(pid); >> Running the following >> select distinct pid,

Re: [GENERAL] Performance issue with Pointcloud extension

On 06/08/2017 10:41 PM, Éric wrote: > > > >> Have you experimented with other queries that don't involve PostGIS? >> I'm wondering if your hook-installation code fails to work properly >> unless PostGIS was loaded first. This would be easier to credit if >> there are hooks both extensions try

Re: [GENERAL] Vacuum and state_change

On 06/09/2017 09:13 AM, armand pirvu wrote: On Jun 9, 2017, at 11:01 AM, Adrian Klaver > wrote: On 06/09/2017 08:45 AM, armand pirvu wrote: Hi Had a couple of processes blocking the vacuum so I terminated them using select

Re: [GENERAL] ERROR: unexpected chunk number 0 (expected 1) for toast value 76753264 in pg_toast_10920100

Ha guys, I am new to postgress and I am trying to write my first function to insert, update or delete and trap errors as a result of the table not existing , the columns not exist or if any other error simply pass back the sqlstate here's my code can you help CREATE OR REPLACE FUNCTION

Re: [GENERAL] pg_upgrade --link on Windows

On Fri, 9 Jun 2017 07:24:03 -0700, Adrian Klaver wrote: >https://msdn.microsoft.com/en-us/library/windows/desktop/aa365006(v=vs.85).aspx > >Seems to me the difference is hard links point to file, junctions to >directories. You can make either hard links or symlinks

Re: [GENERAL] Performance issue with Pointcloud extension

On Fri, Jun 9, 2017 at 9:13 AM, Eric Lemoine wrote: > On 06/08/2017 10:41 PM, Éric wrote: > > > > > > > >> Have you experimented with other queries that don't involve PostGIS? > >> I'm wondering if your hook-installation code fails to work properly > >> unless PostGIS

Re: [GENERAL] pg_catalog tables don't have constraints?

Neil Anderson wrote: > I've been exploring the pg_catalog tables and pointed a couple of tools at > it to extract an ER diagram for a blog post. At first I thought it was a bug > in the drawing tool but it appears that the relationships between the > pg_catalog tables

Re: [GENERAL] pg_catalog tables don't have constraints?

kbran...@pwhome.com schrieb am 09.06.2017 um 20:57: Neil Anderson wrote: I've been exploring the pg_catalog tables and pointed a couple of tools at it to extract an ER diagram for a blog post. At first I thought it was a bug in the drawing tool but it appears that

Re: [GENERAL] Performance issue with Pointcloud extension

On 06/09/2017 09:13 AM, Eric Lemoine wrote: On 06/08/2017 10:41 PM, Éric wrote: Have you experimented with other queries that don't involve PostGIS? I'm wondering if your hook-installation code fails to work properly unless PostGIS was loaded first. This would be easier to credit if there

Re: [GENERAL] Limiting DB access by role after initial connection?

On 06/09/2017 08:56 AM, Ken Tanzer wrote: > On Fri, Jun 9, 2017 at 6:42 AM, Joe Conway wrote: > See set_user for a possible solution: https://github.com/pgaudit/ > > Thanks! Looking at the README, it seems like the intended use case is > the opposite (escalating privileges), but if I