Re: Rationale for aversion to the central database?

re bypassed). For a system that's only accessible to internal staff, it's a great way to defend against their workstations being infected by malware that goes looking for databases to attack. cheers, raf

Re: Control PhoneNumber Via SQL

and the phone number and returns what you want to insert and then always use its return value when assigning a value to the phone_number field. i suspect that subclassing PhoneNumberField is probably the best approach. cheers, raf

Re: CSVQL? CSV SQL? tab-separated table I/O? RENAME COLUMN

Ron wrote: > On 05/02/2018 04:49 PM, David G. Johnston wrote: > [snip] > > > > - the microsoft patented CSV would be required for implementation. it > > handles special data with commas and double-quotes in them > > > > > > If true this seems like a show-stopper to anything PostgreSQL

Re: Recreating functions after starting the database server.

ing like this but I only have small databases so that doesn't mean there won't be any. Good luck! cheers, raf

Re: How to search particular line/text code in all Postgres all database objects

where u.usename = current_user and p.proowner = u.usesysid and p.proname like 'aps%' -- 'aps' is the prefix for our functions order by p.proname Or just: select proname, prosrc from pg_proc where proname like ... cheers, raf

Re: Inconsistent compilation error

Adrian Klaver wrote: > On 04/18/2018 06:02 PM, r...@raf.org wrote: > > Hi, > > > > postgresql-9.5.12 on debian-9 > > > > I have a stored function with code that looks like: > > > > create or replace function tla_audit_delete_thing() > > returns boolean stable language plpgsql as $$ >

Re: Inconsistent compilation error

David G. Johnston wrote: > On Wed, Apr 18, 2018 at 7:04 PM, Melvin Davidson > wrote: > > > > > Normally, literals are inside the first quotes. IE: raise notice ' > > blah_history.original_id' %', r; > > > > ​But a compiler doesn't care about human concepts like "normally"

Re: Inconsistent compilation error

Tom Lane wrote: > r...@raf.org writes: > > I have a stored function with code that looks like: > > > raise notice '% %', 'blah_history.original_id', r; > > > But I'm getting this compilation error when it tries to load this > > function: > > > ERROR: too many parameters

Re: Inconsistent compilation error

iour with pygresql-5+ that i had to find ways to disable/revert so it's not surprising that there might be other oddities to encounter. i'm surprised it's only become a problem now. i think you're definitely right. when i change my function loading program to duplicate all percent signs in all the source code before passing it to pgdb, they all load successfully and a subsequent audit of the code inside the database and on disk still shows that they match, so pgdb must be de-duplicating all the duplicated percent signs. thanks so much for spotting this. cheers, raf

Inconsistent compilation error

red functions were loaded fine. It's just this one that went wrong. Thanks in advance for any insights you can share. cheers, raf

Re: Is there a continuous backup for pg ?

Gary M wrote: > Vick, > > I would love to use ZFS, this project requires RHEL/SeLinux MLS. Without > MLS and RH support, ZFS is a no-go. btrfs could be an option. it does mostly the same things as zfs. > On Fri, Mar 2, 2018 at 2:34 PM, Vick Khera wrote: > > > On Fri, Mar 2,

Re: Is pg_restore in 10.6 working?

ame on the command line after the options will be read as the source of the restore. Only if it is absent would stdin be used. Apologies if the usage message for 10.6 doesn't say the same thing. cheers, raf

Re: Is pg_restore in 10.6 working?

raf wrote: > Tom Lane wrote: > > > David writes: > > > I have some experience with different versions of Postgres, but I'm just > > > getting around to using pg_restore, and it's not working for me at all. > > > ... > > > But a matching pg_r

Re: Invoking user of the function with SECURITY DEFINER

LECT tellme(); > tellme > - > laurenz > (1 row) > > Yours, > Laurenz Albe session_user has always worked for me. cheers, raf

Re: why not using a mountpoint as PGDATA?

ctory of the mountpoint in case I ever want to start using pg_upgrade. It would also mean I only need a single ecryptfs mountpoint. At the moment, I create a new one for each major upgrade which seems silly. cheers, raf

Re: Inserts restricted to a trigger

Adrian Klaver wrote: > On 6/17/19 4:54 PM, Miles Elam wrote: > > Is there are way to restrict direct access to a table for inserts but > > allow a trigger on another table to perform an insert for that user? > > > > I'm trying to implement an audit table without allowing user tampering > > with

Re:

Laurenz Albe wrote: > On Tue, 2019-07-02 at 13:09 +0530, Prakash Ramakrishnan wrote: > > We are getting below error while during import the csv file please do the > > needful. > > I'd say the needful thing here is for you to read the documentation... > > > -bash-4.2$ more ckr_sto.csv > >

Re: plpgsql: How to modify a field in an array of records

the record that you want to modify, make the modification in the single record variable and then assign that single record variable back into the array element that it came from. i.e. something like: declare a rectype[]; r rectype; i integer; begin ... r := a[i]; r.field := newvalue; a[i] := r; ... I didn't even realise that you could have an array of records. I've only ever used arrays of scalar values. cheers, raf

question about client/server version mismatches

to servers where they mean something? Or am I just misunderstaing what's happening? I would have thought a new client would be able to work with an old but supported server. It's not a big deal. I'll get around to completing the upgrade and it'll be fine again but I'm curious. cheers, raf

Re: Work hours?

d no doubt every country is different. All of that is by far the biggest component of such a calculation. The postgres-specific bit is easy and yes, what Adrian suggests will be fine if you can use the dates returned by generate_series to look up the working conditions of the person involved. I've done it in plpgsql with a loop over the dates rather using generate_series in plain sql. Both are fine but plain sql is probablby faster. Do whatever is most readable. cheers, raf

update returning order by syntax error question

lain why the error is what it is? It's no big deal. I'm just curious. cheers, raf

Re: Users, Roles and Connection Pooling

hen the functions are called. This means that there will never be any successful SQL injection, even if the application code is buggy, so it's more important for web applications, but I apply this method to internal systems as well. This approach might help with scaling because fewer users might be needed but I'm not sure. cheers, raf

Re: Redis 16 times faster than Postgres?

http://www.twitter.com/stevelitt i don't know but voltdb, another in-memory database, replicates to other instances which can be in different geographical locations and so not prone to a single power failure. perhaps all in-memory databases are aware of the need for this. cheers, raf

Re: PostgreSQL vs PostgresQL

Олег Самойлов wrote: > My vote to change official name form PostgreSQL to PostgresQL. The > reason is: many young people look at PostgreSQL and think that the > short name is Postgre. Sounded awfully, especially in Russian, tied to > fix neighbours. With PostgresQL they will think that the short

Re: jsonb_set() strictness considered harmful to data

return null when given a null argument might be important to know for other functions as well. cheers, raf

Re: jsonb_set() strictness considered harmful to data

Steven Pousty wrote: > On Sun, Oct 20, 2019 at 4:31 PM raf wrote: > > > Steven Pousty wrote: > > > > > I would think though that raising an exception is better than a > > > default behavior which deletes data. > > > > I can't help but feel

Re: Users, Roles and Connection Pooling

Rob Sargent wrote: > On 10/2/19 5:27 PM, raf wrote: > > > > > I can't help with questions about scale but I like to give roles/users > > almost no permissions at all. i.e. They can't select, insert, update > > or delete anything. All they have permission to do is t

Re: Restrict connection from pgadmin.

in a transaction that will rollback or on a test server before executing it in production. I don't think a GUI is suitable for this. cheers, raf

Re: timestamp and timestamptz

les is a great idea. They hardly ever include timezone information so keeping everything in the same timezone is important. Mind you, they hardly ever even include the year. What's with that? (yes, I'm looking at you /var/log). > -- > Tim Cross cheers, raf

Re: timestamp and timestamptz

Adrian Klaver wrote: > On 4/16/20 1:23 AM, raf wrote: > > Steve Baldwin wrote: > > > > > I'm pretty sure you are mistaken. Postgres doesn't store the 'creating' > > > time zone in a timestamptz column. > > > > > > Try doing this before re-r

Re: timestamp and timestamptz

David G. Johnston wrote: > On Wed, Apr 15, 2020 at 4:53 PM raf wrote: > > > I don't see much difference in storing a timestamptz in UTC or a > > timestamptz > > in CET. As long as the intended offset from UTC is recorded (which it is > > in a timestamptz) it shou

Re: timestamp and timestamptz

tamp without time zone is best avoided I think. cheers, raf

Re: Backing out of privilege grants rabbit hole

It's probably more sensible to grant permissions to roles that represent groups, and have roles for individual users that inherit the permissions of the group roles. Then you don't need to revoke the permissions just because an individiual has left. cheers, raf AC Gomez wrote: > Tha

Re: Which is the setup with lowest resources you know Postgres is used in?

website (probably no more than 40 users at a time), and the database became corrupted so often that I had had to automate rebuilding it from the latest backup and my own sql logs. I was very silly. Switching to postgres was the real solution. cheers, raf

Re: Which is the setup with lowest resources you know Postgres is used in?

On Thu, Oct 08, 2020 at 01:14:02AM +0300, Dmitry Igrishin wrote: > чт, 8 окт. 2020 г. в 00:14, raf : > > > > On Wed, Oct 07, 2020 at 01:53:44PM +0300, Dmitry Igrishin > > wrote: > > > > > In many cases concurrency is not a problem and in fact SQLite may >

Re: PostgreSQL on Windows' state

o be considered a second class > choice. It sounds awkward to me that such a great db has flaws like this (I'm > NOT stating it *has* flaws) > > sandro Surely, it's a flaw in the C# ecosystem, not a flaw in the database? (assuming it's true, and assuming there's anything wrong with ODBC). :-) cheers, raf

I'm surprised that this worked

than usual. :-) cheers, raf

Re: PostgreSQL processes use large amount of private memory on Windows

regards, tom lane For what it's worth, I have 171305 lines of plpgsql/sql functions and it hasn't caused any problem on a server with 4GB RAM. With a small number of long-lived connections. cheers, raf

Re: Can I get some PostgreSQL developer feedback on these five general issues I have with PostgreSQL and its ecosystem?

hould be 1/2 to 3/4 of RAM It's the "Should..." comments that matter. And it might be out of date... Actually, it is out of date. The comment for checkpoint_completion_target refers to checkpoint_segments which no longer exists (since 9.5) so disregard that. cheers, raf

Re: GPG signing

Marc Munro wrote: > On Tue, 2020-05-26 at 12:04 -0700, Adrian Klaver wrote: > > On 5/26/20 12:01 PM, Marc Munro wrote: > > > I need to be able to cryptographically sign objects in my database > > > using a public key scheme. > > > [ . . . ] > > > Any other options?  Am I missing something? > > >

pg_restore: error: schema "public" already exists

script to load the unencrypted dump, and that worked fine. Does anyone have any idea why this might have happened? Under what circumstances (immediately after createdb) might "CREATE SCHEMA public" result in "ERROR: schema "public" already exists"? And why might it differ depending on whether the unencrypted dump was read from stdin or from a file? cheers, raf

Re: Implement a new data type

encies probably don't change as often as timezones but there would probably still be some ongoing need for updates. cheers, raf

Re: Implement a new data type

On Wed, Aug 12, 2020 at 12:44:21PM -0400, Philip Semanchuk wrote: > > On Aug 11, 2020, at 8:01 PM, raf wrote: > > > > On Tue, Aug 11, 2020 at 06:38:39AM -0700, Miles Elam > > wrote: > > > >> Also of note: PostgreSQL already has a money type ( >

Re: survey: psql syntax errors abort my transactions

ion. It might not be exactly what you need. > As someone working at a large company with an aweful lot of PostgreSQL, > thinking of the internal production systems I'm aware of, I'd personally > vote pretty strongly in favor of changing the default. > > -Jeremy > > Jeremy Schneider > Database Engineer > Amazon Web Services cheers, raf

Re: PostgreSQL 12 - ERROR: could not rename file "pg_logical/snapshots/1A-7C00D890.snap.13594.tmp" to "pg_logical/snapshots/1A-7C00D890.snap": No space left on device

out of space, you might have run out of inodes. The -i option of df should show you. 100,000 empty files could cause that. I wouldn't think that renaming would require a new inode, but I also wouldn't think that renaming would require any more space on a file system. cheers, raf

Re: PostgreSQL 12 - ERROR: could not rename file "pg_logical/snapshots/1A-7C00D890.snap.13594.tmp" to "pg_logical/snapshots/1A-7C00D890.snap": No space left on device

FOUTE K. Jaurès wrote: > Le mer. 1 juil. 2020 à 00:11, raf a écrit : > > > FOUTE K. Jaurčs wrote: > > > > > Hi everyone, > > > > > > I am use a native logical replication in PostgreSQL 12 on Ubuntu 16.04 > > and > > > all is working fi

Re: Hiding a GUC from SQL

Laurenz Albe wrote: > On Mon, 2020-06-22 at 09:44 +1000, raf wrote: > > A superuser can access files and start programs on the server machine. > > > A dedicated superuser may for example attach to PostgreSQL with a debugger > > > and rea

Re: Hiding a GUC from SQL

or a superuser. You might not stop a legitimate sitewide superuser whose family is being held hostage, but you can stop, or at least make things much more difficult, for a superuser process on a single host that is the result of a software vulnerability that wasn't nobbled by apparmor or selinux or grsecurity. cheers, raf

Re: Changing from security definer to security invoker without dropping ?

break with such a change. There might be a reason that the functions needed to be created as security definers. I'd recommend checking each function's need to be a security definer before changing it (or just test it thoroughly somewhere). cheers, raf

Re: Oracle vs. PostgreSQL - a comment

ould be needed later. Then the process would have to be followed twice, once for the first port, and once again for all the other ports that might become necessary. cheers, raf

Re: TDE implementation in postgres which is in docker container

ecryptfs works really well with postgres (i.e. no performance penalty that I could measure) and should probably be usable with docker (but I haven't done that so I don't know the details). cheers, raf

Re: How to keep format of views source code as entered?

You'll also want to make sure that they all have write access to the same git repository where the views are. cheers, raf > From: Markhof, Ingolf [mailto:ingolf.mark...@de.verizon.com] > Sent: 07 January 2021 17:19 > To: pgsql-general@lists.postgresql.org > Subject: [E] How

Re: Is the Halloween problem an issue in Postgres

results in: id | a | b +---+--- 1 | 2 | 2 2 | 3 | 3 3 | 4 | 4 4 | 4 | 5 5 | 5 | 6 6 | 6 | 7 It's the same with or without the index on a(a). cheers, raf

Re: Is the Halloween problem an issue in Postgres

; Why exactly is that a problem in SQL Server? > And what are the consequences if you do it nevertheless. According to wikipedia, this problem was discovered on Halloween day, 1976. I find it hard to believe that any database would still exhibit that behaviour 44 years later. cheers, raf

Re: How to set up a schema default date to '2020-01-01'?

abase backup (i.e. anything whose timestamp is later than the timestamp of the backup), which seems like it might be related to what you are after, but I don't understand the idea of a "default" date. The "date" for my schema is always the present so as to match the corresponding software in its current state. Perhaps you can explain in more detail what you are after. cheers, raf

Re: Christopher Browne

On Wed, Nov 04, 2020 at 06:29:18PM -0500, Steve Singer wrote: > > It is with much sadness that I am letting the community know that Chris > Browne passed away recently. > > Chris had been a long time community member and was active on various > Postgresql mailing lists. He was a member of

Re: How to keep format of views source code as entered?

e code on a file system, so that a local git repository could see it, and push it to github. You don't even have to use git if you really don't want to. cheers, raf

Re: How to keep format of views source code as entered?

lse in the system catalogs? Without them, this query would only find the names and query code of views: select v.viewname, v.definition from pg_views v where v.viewname like 'myview_%'; -- Your naming convention Is there a query that can be used to obtain all of the information needed to reconstruct the create view statement that corresponds to a view in pg_views? cheers, raf

Re: How to keep format of views source code as entered?

On Sat, Jan 09, 2021 at 05:26:04PM -0700, "David G. Johnston" wrote: > On Saturday, January 9, 2021, raf wrote: > > > Actually, I just had a look at the pg_views system > > catalog where the source code for views is stored, and > > it doesn't

Re: Do we need a way to moderate mailing lists?

fcha/imapfilter) might be more appropriate if your email is in an imap account. cheers, raf

Re: psql behavior change on upgrade from version 12.x to 13.1

hat will enable it to insert each paste into the editing buffer as a single string of characters, instead of treating each character as if it had been read from the keyboard. This can prevent pasted characters from being interpreted as editing commands. The default is `On'. So try putting this in your ~/.inputrc file: set enable-bracketed-paste off cheers, raf

Re: Need help with clarification on stored procedure support in PostGreSQL database

important different between a stored procedure and a stored function that returns void? I've been using functions for everything but calling them procedures in my head (even though most of them do return a value or result set). cheers, raf

Re: Need help with clarification on stored procedure support in PostGreSQL database

On Mon, Mar 08, 2021 at 06:31:56PM -0500, Tom Lane wrote: > raf writes: > > Is there any important different between a stored procedure and > > a stored function that returns void? I've been using functions > > for everything but calling them procedures in my head (even th

Re: Are stored procedures/triggers common in your industry

fast. But yes, being able to do complex system testing with transaction rollback is great. cheers, raf

Re: Are stored procedures/triggers common in your industry

s for everything on websites for security reasons. Everyone's mileage varies. We're all in different places. cheers, raf

Re: Stored procedure code no longer stored in v14 and v15, changed behaviour

or are your applications completely > view-free? In my case, yes, all access is via procedures. No views or triggers. So it was OK. cheers, raf

Re: Stored procedure code no longer stored in v14 and v15, changed behaviour

its own copy of the source code in the database somewhere, and just hope that nobody loads stored procedures using any other tool. Even so, it will slow down loading old database backups and bringing their schema and code up to date. But that's probably OK compared with the time to load the backup itself. cheers, raf

Re: Stored procedure code no longer stored in v14 and v15, changed behaviour

On Fri, Dec 02, 2022 at 03:00:09PM +0200, Pasi Oja-Nisula wrote: > On Fri, 2 Dec 2022 at 02:24, raf wrote: > > Same here. Accessing the loaded stored procedure source > > is how I audit the state of stored procedures in the > > database against the code in the code rep

Re: Maintaining blank lines in psql output?

On Tue, Jan 17, 2023 at 04:10:50PM -0700, "David G. Johnston" wrote: > On Tue, Jan 17, 2023 at 4:07 PM raf wrote: > > > On Tue, Jan 17, 2023 at 02:22:22PM -0700, "David G. Johnston" < > > david.g.johns...@gmail.com> wrote: > >

Re: Maintaining blank lines in psql output?

like adding: select ''; or raise notice ''; It won't result in just a blank line, but it will separate things. cheers, raf

Re: SOC II Type 2 report.

be described as a vendor. By definition, a vendor is a person or organisation that sells something. OED: vendor(n): One who disposes of a thing by sale; a seller cheers, raf