Re: Sorting composite types

Miles Elam wrote: > Is there any way to define a natural sorting order for composite types? For > example, let's say you have a type like: > > CREATE TYPE contrived AS ( > i1 integer, > i2 integer > ); > > The semantics of this contrived type are that the natural order is ascending > NU

Re: Guidance needed on an alternative take on common prefix SQL

On 8/6/19 6:25 PM, Laura Smith wrote: Hi, I've seen various Postgres examples here and elsewhere that deal with the old common-prefix problem (i.e. "given 1234 show me the longest match"). I'm in need of a bit of guidance on how best to implement an alternative take. Frankly I don't quite kn

Guidance needed on an alternative take on common prefix SQL

Hi, I've seen various Postgres examples here and elsewhere that deal with the old common-prefix problem (i.e. "given 1234 show me the longest match"). I'm in need of a bit of guidance on how best to implement an alternative take. Frankly I don't quite know where to start but I'm guessing it wi

Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?

Thanks Adrian. My aim with p1() was to show that its behavior, in each AUTOCOMMIT mode, aligns with my intuition. I’ve noticed the system-generated “begin” that you mention when I add this to my “/usr/local/var/postgres/postgresql.conf”: log_statement = 'all' log_directory = 'log' logging_colle

Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?

On 8/6/19 1:17 PM, Bryn Llewellyn wrote: I read this blog post *PostgreSQL 11 – Server-side Procedures — Part 1  and Part 2 * I

Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?

I read this blog postPostgreSQL 11 – Server-side Procedures — Part 1 and Part 2It starts with “Thanks to the work done by 2ndQuadrant contributors, we now have the ability to write Stored Procedures in PostgreSQL… once PostgreSQL 11 comes out”. It focuses on doing txn control from a stored proc.In

Sorting composite types

Is there any way to define a natural sorting order for composite types? For example, let's say you have a type like: CREATE TYPE contrived AS ( i1 integer, i2 integer ); The semantics of this contrived type are that the natural order is ascending NULLS first for i1 and descending NULLS la

Re: Does pgadmin4 work with postgresql 8.4?

On 8/6/19 9:11 AM, Luca Ferrari wrote: On Tue, Aug 6, 2019 at 2:46 PM Benedict Holland wrote: To me, there is a huge difference between unsupported and wont work. ote: Thta' why the only truly working software you has is psql 8.4. As other has stated, it is too far in the past to say what is

Re: Does pgadmin4 work with postgresql 8.4?

On Tue, Aug 6, 2019 at 2:46 PM Benedict Holland wrote: > > To me, there is a huge difference between unsupported and wont work. ote: Thta' why the only truly working software you has is psql 8.4. As other has stated, it is too far in the past to say what is working and what not, you have to walk

Re: vacuum & free space map

On Tue, Aug 6, 2019 at 3:50 PM Tom Lane wrote: > VAC FULL builds a new physical table, which has no FSM to start with. Thanks, that was I was suspecting. Luca

Re: Does pgadmin4 work with postgresql 8.4?

On 8/5/19 10:02 PM, Murtuza Zabuawala wrote: On Tue, Aug 6, 2019 at 7:57 AM Adrian Klaver > wrote: On 8/5/19 7:04 PM, Murtuza Zabuawala wrote: > No, pgAdmin4 only supports PostgreSQL 9.2 and later versions. Where is that mentioned in the documentat

Re: vacuum & free space map

Luca Ferrari writes: > I'm not understanding why a vacuum full clears a FSM information while > a normal vacuum does not. On a table with fillfactor = 50 I've got: VAC FULL builds a new physical table, which has no FSM to start with. Possibly, when fillfactor < 100, that's something that should b

Re: Preventing in-session 'set role' commands

VO Ipfix writes: > Hello everyone! I am working on a multi-tenant (sigh) DB design using > schemas. I anticipate a bunch of junior developers coming in before we > fully mature our testing process, so SQLi is a concern. Basically, I want > to have a role for each tenant, and have a user/role that

Re: Does pgadmin4 work with postgresql 8.4?

8.4's final release was 5 years ago, so unlikely anyone would know the answer to your question. The only way to really know is to try it. I just had an issue with the current version (4.11) against PG11.4, where the version of psycopg2 was out of date by a release (2.7x instead of 2.8). Most stuf

Re: Does pgadmin4 work with postgresql 8.4?

We cant upgrade because of business reasons but that is very high on our agenda to fix. To me, there is a huge difference between unsupported and wont work. I dont expect support for this but are there significant changes between 8.4 and 9.2 that would prevent pgadmin4 from working? Thanks, ~Ben

RE: Preventing in-session 'set role' commands

Maybe check out the set_user extension: https://github.com/pgaudit/set_user Steve.

Re: Preventing in-session 'set role' commands

On Tue, Aug 6, 2019 at 10:26 AM VO Ipfix wrote: > > Is this something that can be accomplished with PostgreSQL? Any suggestions > thoughts are welcome, however tangential Perhaps SET SESSION AUTHORIZATION? Luca

Preventing in-session 'set role' commands

Hello everyone! I am working on a multi-tenant (sigh) DB design using schemas. I anticipate a bunch of junior developers coming in before we fully mature our testing process, so SQLi is a concern. Basically, I want to have a role for each tenant, and have a user/role that will est. a DB session fro

Re: Compression In Postgresql 9.6

On Mon, 5 Aug 2019, 18:57 Kenneth Marshall, wrote: > > >Hi, > > > > > >On RHEL/Centos you can use VDO filesystem compression to make an archive > > >tablespace to use for older data. That will compress everything. > > > > Doesn't this imply that either his table is partitioned or he > > regularly

vacuum & free space map

Hi, I'm not understanding why a vacuum full clears a FSM information while a normal vacuum does not. On a table with fillfactor = 50 I've got: testdb=> vacuum full respi.pull_status; VACUUM testdb=> SELECT * FROM pg_freespace( 'respi.pull_status', 0 ); pg_freespace -- 0 (1

Re: Does pgadmin4 work with postgresql 8.4?

On Tue, Aug 6, 2019 at 7:02 AM Murtuza Zabuawala wrote: > It is mentioned on the front page of https://www.pgadmin.org/ > (Check introduction texts on the elephant wallpaper) "pgAdmin may be used on Linux, Unix, Mac OS X and Windows to manage PostgreSQL 9.2 and above." However, I would suspect i