Re: policies and extensions

Marc Munro writes: > I tried to define a policy within an extension but the policy does not > seem to belong to the extension. Is this the way it is supposed to be? Yeah, I would expect that. https://www.postgresql.org/docs/current/extend-extensions.html says: The kinds of SQL objects tha

Re: policies and extensions

On 2/17/20 2:46 PM, Marc Munro wrote: I tried to define a policy within an extension but the policy does not seem to belong to the extension. Is this the way it is supposed to be? This is postgres 9.5.21 Here is the relevant code from the extension: create table rls2 (   username te

policies and extensions

I tried to define a policy within an extension but the policy does not seem to belong to the extension. Is this the way it is supposed to be? This is postgres 9.5.21 Here is the relevant code from the extension: create table rls2 (   username text not null,   details  text not null

pgstattuple extension - Can pgstattuple_approx support toast tables?

Hello, The pgstattuple extension is pretty useful to monitor table bloat. I have few large tables with bytea column that represent TB of data that I would need to monitor. I ran pgstattuple() on the associated toast tables but it takes hours to run. pgstattuple_approx() would be a nice alternat

Re: Postgres error

Thanks Adrian, I will On Mon, Feb 17, 2020 at 6:53 PM Adrian Klaver wrote: > On 2/17/20 1:50 AM, Loai Abdallatif wrote: > > Hello Guys, > > > > I have case of two Postgres version 11 ( replication using repmgr) and > > and its work fine as below: > > -bash-4.2$ sudo su - postgres -c " /usr/pgsql

Re: Cases where alter table set type varchar(longer length) still needs table rewrite

On Mon, Feb 17, 2020 at 10:46 AM Adrian Klaver wrote: > > How about?: > > https://www.postgresql.org/docs/9.6/sql-altertable.html > > "Adding a column with a DEFAULT clause or changing the type of an > existing column will require the entire table and its indexes to be > rewritten. As an exceptio

Re: Cases where alter table set type varchar(longer length) still needs table rewrite

Adrian Klaver writes: > On 2/17/20 7:01 AM, Jeremy Finzel wrote: >> I'm still not sure why a rebuild of this index would be required, >> technically speaking. But perhaps in any case the docs should have >> something to the effect that expression indexes may require rebuild >> under specific c

Re: PL/pgSQL question about EXCEPTION clause & corrupt records

On 2/17/20 08:23, Jeremy Schneider wrote: > FWIW, Bertrand blogged an even faster way to do this about a month ago - > using pageinspect and processing blocks instead of rows > > https://bdrouvot.wordpress.com/2020/01/18/retrieve-postgresql-variable-length-storage-information-thanks-to-pageinspect

Re: parsing xml with PG 9.2.4

Hi Mario, First off, as you will be aware, 9.2 is quite an old Postgres version and is currently unsupported - for security reasons alone, you should upgrade ASAP. Regardless, this query should work for you: SELECT xpath('/ProgramInformation/BasicDescription/CreditsList', program_information.des

Re: Postgres error

On 2/17/20 1:50 AM, Loai Abdallatif wrote: Hello Guys, I have case of two Postgres version 11 ( replication using repmgr) and and its work fine as below: -bash-4.2$ sudo su - postgres -c " /usr/pgsql-11/bin/repmgr -f /var/lib/pgsql/repmgr/repmgr.conf cluster show"  ID | Name  | Role    | Stat

Re: Cases where alter table set type varchar(longer length) still needs table rewrite

On 2/17/20 7:01 AM, Jeremy Finzel wrote: On Mon, Feb 17, 2020 at 8:21 AM Tom Lane > wrote: Jeremy Finzel mailto:finz...@gmail.com>> writes: > I have a table foo with 100 million rows, and a column: >    - id character varying(20) > The following comm

Re: Cannot connect to postgresql-11 from another machine after boot

On 2/17/20 7:17 AM, Jason Swails wrote: On Sun, Feb 16, 2020 at 8:51 AM Peter J. Holzer > wrote: On 2020-02-13 21:03:48 -0800, Adrian Klaver wrote: > On 2/13/20 9:02 PM, Adrian Klaver wrote: > > On 2/13/20 7:54 PM, Jason Swails wrote: > > > The prob

parsing xml with PG 9.2.4

Hello Developers, I hope you can help me. I'm having troubles parsing some data from my psql table, which I need for further manipulation. So my query: select program_information.description FROM program_information WHERE id = 8768787; GIves me: Zla smrt Pet prijateljev, starih neka

Re: PL/pgSQL question about EXCEPTION clause & corrupt records

FWIW, Bertrand blogged an even faster way to do this about a month ago - using pageinspect and processing blocks instead of rows https://bdrouvot.wordpress.com/2020/01/18/retrieve-postgresql-variable-length-storage-information-thanks-to-pageinspect/ -J Sent from my TI-83 > On Feb 17, 2020, at

Re: DBI && INSERT

On Mon, 17 Feb 2020 16:49:27 +0100 Matthias Apitz wrote: > I spend today some hours to nail down and insert problem into our > database with DBI like: > >my $rc = $my_dbh->do($my_sqlstatement); > > which returns 1 in $rc (which the following flow in our script took > as an error). The DBI d

DBI && INSERT

Hello, I spend today some hours to nail down and insert problem into our database with DBI like: my $rc = $my_dbh->do($my_sqlstatement); which returns 1 in $rc (which the following flow in our script took as an error). If one fired up the same string in $my_sqlstatement with pgsql the resul

Re: Cannot connect to postgresql-11 from another machine after boot

On Sun, Feb 16, 2020 at 8:51 AM Peter J. Holzer wrote: > On 2020-02-13 21:03:48 -0800, Adrian Klaver wrote: > > On 2/13/20 9:02 PM, Adrian Klaver wrote: > > > On 2/13/20 7:54 PM, Jason Swails wrote: > > > > The problem is that after my machine boots, I'm unable to connect to > > > > the server fr

Re: Cases where alter table set type varchar(longer length) still needs table rewrite

On 2/17/20 9:01 AM, Jeremy Finzel wrote: On Mon, Feb 17, 2020 at 8:21 AM Tom Lane > wrote: Jeremy Finzel mailto:finz...@gmail.com>> writes: > I have a table foo with 100 million rows, and a column: >    - id character varying(20) > The following command

Re: Cases where alter table set type varchar(longer length) still needs table rewrite

On Mon, Feb 17, 2020 at 8:21 AM Tom Lane wrote: > Jeremy Finzel writes: > > I have a table foo with 100 million rows, and a column: > >- id character varying(20) > > The following command is the one that we expect to execute very quickly > (we > > are not seeing any locking), but it is inste

Re: Cases where alter table set type varchar(longer length) still needs table rewrite

Jeremy Finzel writes: > I have a table foo with 100 million rows, and a column: >- id character varying(20) > The following command is the one that we expect to execute very quickly (we > are not seeing any locking), but it is instead taking a very long time: >- ALTER TABLE foo ALTER COLUM

Cases where alter table set type varchar(longer length) still needs table rewrite

Good morning! We are a little bit puzzled because running the following command on a 9.6 cluster is apparently requiring a table rewrite, or at least a very long operation of some kind, even though the docs say that as of 9.2: - Increasing the length limit for a varchar or varbit column,

Re: PL/pgSQL question about EXCEPTION clause & corrupt records

Hi Jeremy, This happend on PostgreSQL v9.6 which crashed 2 weeks ago. Since then we have upgraded and restored our server, but my example is from the older, corrupt database. Nick On 15 Feb 2020, at 5:30, Jeremy Schneider wrote: On Feb 14, 2020, at 04:39, Nick Renders wrote: I get the fo

Re: PL/pgSQL question about EXCEPTION clause & corrupt records

The problem is that I don't know which column is corrupt. But I found a solution: by simply copying the record into another variable, the values are parsed and the TOAST errors are thrown. In case anyone's interested, here's my code, based on an example from http://www.databasesoup.com/2013/10

Compiling via LLVM and active LTO

Hello, I want to do some performance measures and have problems compiling postgres with LTO activated. I tried it with postgresql 12.0 and 12.2, yielding both the same result. The errors seem to relate to languages and encoding-functions. The linker simply seems unable to find them. Also it does n

Postgres error

Hello Guys, I have case of two Postgres version 11 ( replication using repmgr) and and its work fine as below: -bash-4.2$ sudo su - postgres -c " /usr/pgsql-11/bin/repmgr -f /var/lib/pgsql/repmgr/repmgr.conf cluster show" ID | Name | Role| Status| Upstream | Location | Priority | Connect