Re: selecting timestamp

On 02/27/2018 12:16 PM, chris wrote: > > What is the best way of selecting current timestamp in UTC?  > > SELECT > CURRENT_TIMESTAMP   as ct1 > ,timezone('UTC',CURRENT_TIMESTAMP) as ct2 > ,timezone('utc',now()) as ct3 > ,CURRENT_TIMESTAMP at time zone 'UTC' as ct4 > ,NOW() at time zone 'utc' as c

Re: Reindex doesn't eliminate bloat

On 03/12/2018 09:16 PM, Ron Johnson wrote: > On 03/12/2018 10:48 PM, Nikolay Samokhvalov wrote: >> Those queries from wiki for table and index bloat estimation are for >> estimation only. In many cases they show very wrong results. Better >> (yet not ideal) approach is using pgstattuple extension (

Re: Space Stalker in SQL Output

On 06/27/2018 12:45 PM, Susan Hurst wrote: > Wow!  The -A option worked perfectly! > > Thanks for the syntax lesson Steve and Jerry! If you are going to be doing lots of scripting with Postgres, you might want to take a look here: https://github.com/jconway/shebang HTH, Joe -- Crunchy Data -

Re: Will there ever be support for Row Level Security on Materialized Views?

On 08/27/2018 07:38 PM, Ken Tanzer wrote: > On Mon, Aug 27, 2018 at 4:23 PM David Rowley > mailto:david.row...@2ndquadrant.com>> wrote: > >> If I had to guess what's going on here then I'd say that nobody has >> been sufficiently motivated to work on this yet. If that's the case, >> everyone who r

Re: PostgreSQL FIPS 140-2 on Window

On 10/01/2018 11:44 AM, Bradley May wrote: > I understand the PostgreSQL 9.x installed on a RHEL distribution can > be configured and supports FIPS 140-2 when using and properly > configuring OpenSSL FIPS. My question is can the same be accomplished > with a Windows installation, as easily or simil

Re: Error: rows returned by function are not all of the same row type

On 7/5/19 5:39 AM, Andrey Sychev wrote: > Thank you very much for answering my question, Tom. > Yes, I have always assumed that returning from function without > calling SPI_freetuptable is not good idea, but I do not know another > way to achieve same result. Please do not top post on t

Re: I think that my data is saved correctly, but when printing again, other data appears

On 10/27/19 8:01 PM, Yessica Brinkmann wrote: > Thank you so much for the answers. > By telling me this: "MemoryContextStrdup enables you to create a copy of > a string in an explicitly specified memory context." I better understood > the function of MemoryContextStrdup. > And thank you very much t

Re: Help with configuring pgAudit

On 11/20/19 8:09 AM, Dave Hughes wrote: > Hey, > Thanks for reaching out.  It looks like I have that parameter set as > well.  Here is a list of settings I have turned on in postgresql.conf > since I installed pgAudit: > > shared_preload_libraries = 'pgaudit' > log_destination = 'csvlog' > logging

Re: Help with configuring pgAudit

On 11/20/19 5:54 PM, Dave Hughes wrote: > Thanks for the tips Joe!  After fighting with this all day, I realized > while I was testing this, I was logging into the database as the > "postgres" user.  For some reason those actions were not being logged.  > But once I logged in as another superuser a

Re: Help with configuring pgAudit

On 11/21/19 1:27 PM, Dave Hughes wrote: > Thank you so much for all your help!  I found out my issue on accident > actually.  I backed up all my user accounts into a SQL scripts and after > reviewing it, I noticed there were some lines that said: > ALTER ROLE postgres SET "pgauid.log" to 'Role'; >

Re: I think that my data is saved correctly, but when printing again, other data appears

On 11/25/19 4:38 PM, Yessica Brinkmann wrote: > Well, as I was told that I should save the > CurrentMemoryContext before starting the SPI, Let's say it would be > before doing SPI_connect (). Is this correct? And then I must use > MemoryContextStrdup. As you told me the MemoryContextStrdup It is >

Re: Range contains element filter not using index of the element column

On 11/27/19 6:33 AM, Lauri Kajan wrote: > On Wed, Nov 27, 2019 at 1:05 PM Игорь Выскорко > wrote: > > Hi! > Do you use GIST index? > According to > https://www.postgresql.org/docs/12/rangetypes.html#RANGETYPES-INDEXING > <@ operator is supported

Re: Interpolation problem - pg 12.4 - full correct version!

On 8/19/20 3:08 PM, David G. Johnston wrote: > On Wed, Aug 19, 2020 at 11:51 AM Pól Ua Laoínecháin > wrote: > > > I think my *MAJOR* problem is that I've developed what is, > essentially, a totally brute force approach - and this simply won't > work at the sce

Re: Christopher Browne

On 11/4/20 7:45 PM, Jan Wieck wrote: > Christopher Browne was my colleague and friend at Afilias and past for more > than > a decade. I have countless memories that connect us. He will be missed. > > > Jan He will definitely be missed. Rest in Peace Chris. Joe > On Wed, Nov 4, 2020, 18:29 S

Re: postgres-10 with FIPS

On 12/9/20 4:51 AM, Aravindhan Krishnan wrote: > The paid version I had mentioned about was the paid OS (ubuntu) for FIPS > compliancy. I understand that postgres as is completely available for > open-source. > > Since we can't get the paid version of the OS to support FIPS compliancy the > idea

Re: CROSSTAB( .. only one column has values... )

On 1/5/21 12:11 PM, Tim Clarke wrote: > imho the crosstab() function isn't a good implementation. The biggest failure > it > has is that you must know exactly how many output columns you will have in the > result /_prior to running it *That* is a function of how Postgres set returning functions

Re: CROSSTAB( .. only one column has values... )

On 1/5/21 10:46 AM, Adam Tauno Williams wrote: > I'm using the crosstab feature and do not understand why I am only > getting values in the first column. > So I put this in as a crosstab: > > SELECT * FROM crosstab( > $$ > $$) AS final_result (invoice_date VARCHAR, "parts" BIGINT, "rent

Re: permanent setting of config variables

On 2/18/21 4:09 AM, Joao Miguel Ferreira wrote: > I have a few PL/pgSQL functions that use queires like "SHOW company.product > INTO > _product_name" and "SHOW company.cluster INTO _cluster_number". > > But these are failing because I don't know how to set those values on a > permanent basis, or

Re: Permission inconsistency with views that call functions

On 2/22/21 10:32 PM, David Wheeler wrote: > I’m seeing some inconsistency with how permissions are enforced within views. > In > particular, if the view accesses a table directly, then the table is > accessible, > however if the view uses a function to access the table then permission is > denie

Re: PostgreSQL 14.4 ERROR: out of memory issues

 ELSE external_complete_id = _external_complete_id             END; $function$ -- Joe Conway RDS Open Source Databases Amazon Web Services: https://aws.amazon.com

Re: how to check specific user_name have “SELECT ON ALL TABLES IN SCHEMA ” privilege or not ?

info you are interested in: https://github.com/CrunchyData/crunchy_check_access/blob/master/sql/check_access.sql HTH, -- Joe Conway PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com

Re: Index corruption revealed after upgrade to 11.17, could date back to at least 11.12

alid false for these indexes. HTH, -- Joe Conway PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com

Re: IMMUTABLE function to cast enum to/from text?

d) RETURNS text AS $$ select $1 $$ STRICT IMMUTABLE LANGUAGE sql; SELECT name, mood2text(current_mood) FROM person; name | mood2text --+--- Moe | happy (1 row) HTH, -- Joe Conway PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com

Re: How do the Linux distributions create the Linux user/group "postgres"?

/bash \ -c "PostgreSQL Server" -u 26 postgres >/dev/null 2>&1 || : 8<--- HTH, -- Joe Conway PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com

Re: How do the Linux distributions create the Linux user/group "postgres"?

On 1/9/23 09:11, Ron wrote: On 1/9/23 07:15, Joe Conway wrote: On 1/9/23 07:41, Matthias Apitz wrote: Please note: I'm talking about the user and group "postgres" in the Linux OS and not in the PostgreSQL server. We're compiling PostgreSQL from source (actually 14.1) an

Re: glibc initdb options vs icu compatibility questions (PG15)

fic/known issues, keep in mind that there is still a possibility of changes across major glibc versions for things affected by LC_CTYPE, LC_TIME, LC_MONETARY, and LC_NUMERIC - lower()/upper() for example. -- Joe Conway PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com

Re: Properly handle OOM death?

ess likely. All that said, even if the individual backend gets killed, the postmaster will still go into crash recovery. So while technically postgres does not restart, the effect is much the same. So see #1 above as your best protection. HTH, Joe -- Joe Conway PostgreSQL Contributors Team RDS O

Re: Properly handle OOM death?

/ cgroup2fs 8<--cgroupv1 enabled- # stat -fc %T /sys/fs/cgroup/ tmpfs 8<--- 2) All the entries contain the line "oom_score_adj:0”, which would seem to imply that the postmaster, with its -900 score is not being directly targeted by the OOM killer. Sound

Re: Properly handle OOM death?

he corresponding/relevant systemd unit file parameters are: 8<--- MemoryAccounting=yes MemoryHigh=3G MemoryMax=4G 8<--- There are other ways that memory.max may get set, but it seems most likely that the systemd unit file is doing it (if it is in fact set). -- Joe Conway PostgreSQL

Re: Properly handle OOM death?

, but without actually testing it I suspect that since memory.max = high (that is, the limit is whatever the host has available) the OOM kill is technically a cgroup OOM kill even though it is effectively a host level memory pressure event. Did you try setting "vm.overcommit_memory=2

Re: Properly handle OOM death?

On 3/13/23 16:18, Israel Brewster wrote: On Mar 13, 2023, at 11:42 AM, Joe Conway wrote: I am not entirely sure, but without actually testing it I suspect that since memory.max = high (that is, the limit is whatever the host has available) the OOM kill is technically a cgroup OOM kill even

Re: Properly handle OOM death?

On 3/18/23 18:02, Tomas Pospisek wrote: On 13.03.23 21:25, Joe Conway wrote: Hmm, well big +1 for having swap turned on, but I recommend setting "vm.overcommit_memory=2" even so. I've snipped out the context here, since my advice is very unspecific: do use swap only as a s

Re: Very slow queries followed by checkpointer process killed with signal 9

running in the TimescaleDB Docker image. Possibly the docker image is running with a cgroup memory.limit set? The OOM killer will trigger on any cgroup limit even if the host has plenty of free memory. -- Joe Conway PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com

Re: SV: SV: Implementing pgaudit extension on Microsoft Windows

On 2/13/19 3:32 AM, Niels Jespersen wrote: > Dive into the source for pgaudit and try to understand how it works, > perhaps getting a small example extension working along the way. > Perhaps contacting the authors of pgaudit to have them confirm that > there exists working implementations of pgau

Re: SV: SV: Implementing pgaudit extension on Microsoft Windows

On 2/13/19 9:17 AM, Joe Conway wrote: > On 2/13/19 3:32 AM, Niels Jespersen wrote: >> Dive into the source for pgaudit and try to understand how it works, >> perhaps getting a small example extension working along the way. >> Perhaps contacting the authors of pgaudit to h

Re: Implementing pgaudit extension on Microsoft Windows

On 2/20/19 5:58 AM, Niels Jespersen wrote: > I did not submit an issue. Instead, I read your input on another > existing issue. In that, you recommended to place pgaudit source in > contrib in a PostgreSQL source tree and build the whole thing. I was hoping you would see that -- it looked pretty m

Re: why not using a mountpoint as PGDATA?

On 2/27/19 11:49 AM, Peter J. Holzer wrote: > On 2019-02-27 10:42:12 -0500, Tom Lane wrote: >> Luca Ferrari writes: >> > On Wed, Feb 27, 2019 at 12:33 PM Julien Rouhaud wrote: >> >> You can see most obvious reasons at >> >> https://bugzilla.redhat.com/show_bug.cgi?id=1247477 > [...] >> The case t

Re: Query much slower on 9.6.5 than on 9.3.5

On 4/5/19 6:23 AM, Rob Northcott wrote: > Ah, fair enough – I’ll get into the habit of doing that then.  Thanks again. > > Rob > *From:*Ron > *Sent:* 05 April 2019 11:07 > *To:* pgsql-general@lists.postgresql.org > *Subject:* Re: Query much slower on 9.6.5 than on 9.3.5 > > pg_dump/restore gets

Re: how to add more than 1600 columns in a table?

On 4/24/19 4:17 PM, pabloa98 wrote: > Sadly today we hit the 1600 columns limit of Postgresql 11. > > How could we add more columns? > > Note: Tables are OK. We truly have 2400 columns now. Each column > represents a value in a matrix. As everyone else has mentioned, your use case sounds like ar

Re: Is _ a supported way to create a column of array type?

On 4/26/19 6:50 AM, Piotr Findeisen wrote: > Presto type system however distinguishes array(integer), > array(array(integer))... > (using Presto therms). And execution is (expectedly) not as flexible. Fine point here -- Postgres does not actually have arrays-of-arrays, it has multi-dimension array

Re: Row data is reflected in DETAIL message when constraints fail on insert/update

On 6/22/19 6:00 AM, William Denton wrote: >>I guess the original question basically boils down to "Given a >>rogue/dumb app, and a DBA who neglected his job, is it PG's >>business (or even within its possibilities) to mop up ?" > > It feels like you aren't setting people up to land in the pit of s

Re: How To: A large [2D] matrix, 100,000+ rows/columns

t really need to be stored as such or could it be stored as vectors that are converted to a matrix on the fly when needed? Seems like using python or R makes more sense. Perhaps it might make sense to store the data in Postgres and use plpython or plr. But it is hard to say with more details.

Re: "paths" between two ROLEs

/CrunchyData/crunchy_check_access It might do for you as-is, or at least you can use it as an example. HTH, -- Joe Conway PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com

Re: PostgreSQL Commercial Support

On 6/26/23 05:56, Oduba, Ernest wrote: We want to start using PostgreSQL and we intent to have a commercial support. Kindly advise who can assist with this request for further engagement. See: https://www.postgresql.org/support/professional_support/ -- Joe Conway PostgreSQL Contributors

Re: postgreSQL UPPER Method is converting the character "µ" into "M"

, and the client encoding. You have to be particularly careful when cutting and pasting that the client encoding and your terminal encoding match or you can get surprising results. -- Joe Conway PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com

Re: libpq crashing on macOS during connection startup

related to the discussion here? https://www.postgresql.org/message-id/flat/CAN55FZ1eDDYsYaL7mv%2BoSLUij2h_u6hvD4Qmv-7PK7jkji0uyQ%40mail.gmail.com -- Joe Conway PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com

Re: Generic File Access Function to read program output

nction special, in that these restrictions and the meaning of role pg_read_server_files just do not apply for it. Do you know if there is already such a function, maybe provided by an extension I do not yet know? Maybe write your own in plpython or plperlu? -- Joe Conway PostgreSQL Contributors

Re: Pgxs - How to reference another extension

er := $(shell ls -1 $(postgis_libdir) | grep "^postgis") SHLIB_LINK += -L$(postgis_libdir) -l:$(postgis_libver) -llwgeom HTH, -- Joe Conway PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com

Re: Feature Request: Option for TLS no SSLRequest with psql

=d39a49c1e459804831302807c724fa6512e90cf0 -- Joe Conway PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com

Re: Feature Request: Option for TLS no SSLRequest with psql

On 5/11/24 11:59, Joe Conway wrote: On 5/11/24 11:19, AJ ONeal wrote: Could we get a `postgrestls://` or `sslmode=tls` or --tls option that instructs psql​ to sends straight TLS, skipping the 000804d2162f / 000804d21630 + N / Y / S handshake? You should probably be following the

Re: PG16.1 security breach?

e privilege selectively. To avoid having a window where the new function is accessible to all, create it and set the privileges within a single transaction. For example: 8<-------- HTH, -- Joe Conway PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com

Re: PG16.1 security breach?

ttps://www.crunchydata.com/blog/postgresql-defaults-and-impact-on-security-part-2 -- Joe Conway PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com

Re: Question about when PostgreSQL 11.0 was released

On 3/8/21 1:22 PM, Bysani, Ram wrote: > I am not finding the release cadence for the PostgreSQL databases. Please let > me > know how I can find the base and dot version release dates for: > > PostgreSQL 11 > PostgreSQL 12 > PostgreSQL 13 I'm not sure a consolidated list of the minor release da

Re: Question about when PostgreSQL 11.0 was released

On 3/9/21 8:20 AM, Greg Sabino Mullane wrote: > Ram Bysani asked: >> I am not finding the release cadence for the PostgreSQL databases. >> Please let me know how I can find the base and dot version release dates for: > > You can see all the release dates on one page here: > > https://bucardo.org/

Re: Compare with default value?

On 3/13/21 1:05 PM, Ulrich Goebel wrote: I would like to get the rows, where a column has the default value, similar to: select id fromt tbl where col = default Is there a chance? It isn't pretty, and not all that practical, but for example: 8< create table def(id int, f1

Re: How to deny access to Postgres when connected from host/non-local

On 4/2/21 7:06 PM, A. Reichstadt wrote: Hello, I try to deny access to all databases on my server if the user “postgres" tries to connect from a non-local host. Here is what I did in pg_hba.conf: # TYPE  DATABASE        USER            ADDRESS                 METHOD # "local" is for Unix do

Re: EXCLUDE USING and tstzrange

On 6/4/21 10:58 AM, Laura Smith wrote: One other question, what's the syntax for manipulating only the upper bound of a range. Say I have a Postgres function that does a "SELECT INTO" for an existing tsrange. Is there an easy way to change the variable's upper bound whilst leaving the "old" low

Re: Struggling with EXCLUDE USING gist

On 6/4/21 1:32 PM, Laura Smith wrote: What is the solution then ? I need to keep historical versions but at the same time I need a "current" version. If I am not able to use "infinity" as bounds for "current" version then clearly I'm wasting my time trying to use EXCLUDE AS for version tracking

Re: Need to omit time during weekends from age calculations

On 6/7/21 3:12 PM, David Gauthier wrote: Hi: I suspect I'm not the first to ask about this but couldn't find anything after googling for a bit.  So here goes I'd like to get the "age" difference between two times which span either all or part of a weekend but exclude any time that transp

Re: removing "serial" from table definitions.

On 6/24/21 8:33 AM, Marc Mamin wrote: Hi, Is there  a way to change a data type from serial to int? I tried with :   ALTER TABLE "admin".db_jobs ALTER COLUMN id TYPE int USING id::int; But this seems not to change anything, as if Posgres woud consider the statement as a no-op. serial is n

Re: pgcrypto - real life examples to encrypt / decrypt

On 8/3/21 8:43 AM, Luca Ferrari wrote: On Tue, Aug 3, 2021 at 1:03 PM Vikas Sharma wrote: My question is, can I use the gpg public/secret key instead of the 'Secret password' in above PGP_Sym_encrypt/decrypt? I can create a wrapper function to read the public/secret keys to hide it from appe

Re: Pg stuck at 100% cpu, for multiple days

On 8/30/21 10:36 AM, hubert depesz lubaczewski Anyway - it's 12.6 on aarm64. Couple of days there was replication slot started, and now it seems to be stuck. #0 hash_seq_search (status=status@entry=0xdd90f380) at ./build/../src/backend/utils/hash/dynahash.c:1448 #1 0xc3042060 in

Re: Account privileges

On 11/16/21 9:07 PM, Prathima Mulpuri wrote: Hi all, I need some help regarding Postgres and I have checked and tried many queries. I am working on RDS Postgres 13 and in the process of preparing the script for auditing all the user privileges . As a part of our auditing, I need a script to list

Re: Feature idea: Dynamic Data Making

On 12/05/2017 01:03 PM, Alvaro Herrera wrote: > Riccardo Bassani wrote: >> As SQL Server 2016 ( >> https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking >> ) , or Oracle does: it could be a neat feature having Dynamic Data >> Masking, even if it has some limitation

Re: Help in dealing with OOM

EMORY-OVERCOMMIT -- Joe Conway PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com

Re: glibc updarte 2.31 to 2.38

On 9/22/24 12:53, Adrian Klaver wrote: On 9/22/24 09:48, Paul Förster wrote: Hi Joe On 22. Sep, 2024, at 15:47, Joe Conway wrote: Note that moving to ICU might improve things, but there are similar potential issues with ICU as well. The trick there would be to get your OS distro provider

Re: glibc updarte 2.31 to 2.38

version across major versions of the distro, which is not the case currently. Nor does the PGDG repo do that. -- Joe Conway PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com

Re: glibc updarte 2.31 to 2.38

, you are good to go. If not, rebuild the index before *any* DML is done to the table. -- Joe Conway PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com

Re: glibc updarte 2.31 to 2.38

On 9/19/24 13:07, Joe Conway wrote: On 9/19/24 11:14, Tom Lane wrote: Paul Foerster writes: we have SLES 15.5 which has glibc 2.31. Our admin told us that he's about to install the SLES 15.6 update which contains glibc 2.38. 2) Does the glibc update have any impact? Maybe. We

Re: glibc updarte 2.31 to 2.38

f work involved (the port from the AL2 rpm to the RHEL7 rpm took me the better part of a couple of days), but once done your collation is frozen to the specific version you had on 15.5. -- Joe Conway PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com

Re: Automatic upgrade of passwords from md5 to scram-sha256

have libpq somehow know/detect that an algorithm change is needed and execute that (or some equivalent) from the client side? And presumably we could ask pgjdbc to implement something similar. Joe [1] https://www.postgresql.org/docs/17/libpq-misc.html#LIBPQ-PQCHANGEPASSWORD -- Joe Conway Post

Re: Moving from Linux to Linux?

*before* allowing any DML you risk further complications such as inability to REINDEX due to duplicate primary key values. HTH, -- Joe Conway PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com

Re: Moving from Linux to Linux?

2025-PGConf.IN-glibc.pdf This is a really good one. Thanks very much for this. You should probably watch this presentation in its entirety: https://www.youtube.com/watch?v=KTA6oau7tl8 Jeremy does a really good job of dispelling misconceptions and if I remember correctly Jeff Davis talks a

Re: Moving from Linux to Linux?

get from the AL2 (glibc 2.26) branch (which was done first) to the RHEL 7 (glibc 2.17) branch. HTH, [1] https://www.joeconway.com/presentations/2025-PGConf.IN-glibc.pdf -- Joe Conway PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com

Re: Need help understanding has_function_privilege

my_user; =# create schema my_schema; =# create function my_schema.my_func(p1 text) returns integer as $ $select 1::integer;$$ language sql; See: https://www.postgresql.org/docs/current/sql-createfunction.html#:~:text=execute%20privilege%20is%20granted%20to%20PUBLIC -- Joe Conway PostgreSQL Contribu